Concurrency Challenges in Database Systems: A Focus on Postgresql

Abstract

Concurrent execution is a pivotal factor in enhancing database system performance. However, as concurrency introduces certain anomalies, accurately tuning concurrent execution while considering the balance between throughput and data integrity becomes a crucial challenge. Isolation levels, specifically, act as database-side settings designed to manage anomalies that pose threats to integrity. In this article, we provide an overview of concurrency control techniques employed to achieve complete accuracy, namely, serializability. Additionally, we discuss weaker isolation levels to enhance performance at the expense of accuracy. We emphasize the importance of Snapshot Isolation (SI), a feature implemented by default in many databases, despite not yet being standardized within SQL. Furthermore, we analyze Serializable Snapshot Isolation (SSI) within PostgreSQL’s Multiversion Concurrency Control framework (MVCC). SSI is anticipated to become a standard in both centralized and distributed systems in the coming periods. To validate the promises of SI and SSI, we conduct experimental studies with realistic scenarios and synthetic datasets on the PostgreSQL database. We anticipate that this paper, combining theoretical insights with practical applications, will serve as a key reference for database administrators.

Keywords

• concurrency
• Isolation Levels
• System Performance

Veritabanı Sistemlerinde Eşzamanlılık Problemine Çözüm Önerileri Ve Postgresql Odaklı Ölçme Ve Değerlendirmeler

Abstract

Veritabanı sistem performansında önemli bir faktör, hareketlerin eşzamanlı çalışmasıdır. Ancak, eşzamanlılık bazı anomalilere yol açtığı için, verim ve veri bütünlüğü arasında dengeyi sağlayarak doğru şekilde ayarlanması oldukça önemlidir. Yalıtım seviyeleri, özellikle bütünlüğü tehdit eden anomalileri yönetmek için tasarlanmış veritabanı tarafı ayarlarıdır. Bu makalede, tam doğruluğu sağlamak için kullanılan, yani serilenebilirliği sağlayan eşzamanlılık kontrol tekniklerine genel bir bakış sunuyoruz. Ayrıca, doğruluktan ödün vererek performansı artırmak için kullanılabilen daha zayıf yalıtım seviyelerinden bahsediyoruz. Birçok veritabanında varsayılan olarak uygulanmasına rağmen henüz SQL standardı olmayan Snapshot Isolation (SI)'nın önemini vurguluyoruz. Ayrıca, PostgreSQL'in Çok Sürüm Eşzamanlı Kontrol (MVCC) çerçevesi içindeki Serializable Snapshot Isolation (SSI)'yi analiz ediyoruz. SSI'nın önümüzdeki dönemlerde hem merkezi hem de dağıtık sistemlerde standart hale gelmesi bekleniyor. SI ve SSI'nin vaatlerini doğrulamak için, PostgreSQL veritabanında gerçekçi senaryolar ve sentetik veri kümeleriyle deneysel çalışmalar da paylaşıyoruz. Teori ve pratiği birleştiren makalemizin sektördeki veri tabanı yöneticileri için de önemli bir kaynak olabileceğini düşünüyoruz.

Keywords

• Eşzamanlılık
• Yalıtım Seviyeleri
• Sistem Performansı

References

1. Adya, A., Liskov, B.H., & O'Neil, P.E. (2000). Generalized isolation level definitions. Proceedings of 16th International Conference on Data Engineering (Cat. No.00CB37073), 67-78.
2. Almeida, D., Lopes, M., Saraiva, L., Abbasi, M., Martins, P., Silva, J.C., & Váz, P. (2023). Performance Comparison of Redis, Memcached, MySQL, and PostgreSQL: A Study on Key-Value and Relational Databases. 2023 Second International Conference On Smart Technologies For Smart Nation (SmartTechCon), 902-907.
3. Alomari, M.A., Fekete, A.D., & Röhm, U. (2014). Performance of program modification techniques that ensure serializable executions with snapshot isolation DBMS. Inf. Syst., 40, 84-101.
4. ANSI. Database language – SQL. American National Standard for Information Systems X3.135-1992, (1992).
5. Beillahi, S.M., Bouajjani, A., & Enea, C. (2021). Checking Robustness Between Weak Transactional Consistency Models. Programming Languages and Systems, 12648, 87 - 117.
6. Berenson, H. & Bernstein, P. & Gray, J. & Melton, J. & O'Neil, E. & O'Neil, P. (1995). A Critique Of ANSI SQL İsolation Levels. pp. 1–10, arXiv:cs/0701157,doi:10.1145/223784.223785, ISBN 978-0897917315. ACM SIGMOD international Conference on Management of Data
7. Bernstein, P. A. & Goodman, N. (1981). Concurrency Control İn Distributed Database Systems, ACM Computing Surveys
8. Binnig, C., Hildenbrand, S., Färber, F., Kossmann, D., Lee, J., & May, N. (2014). Distributed snapshot isolation: global transactions pay globally, local transactions pay locally. The VLDB Journal, 23, 987 - 1011.

9. Bog, A., Kruger, J., & Schaffner, J. (2008). A Composite Benchmark for Online Transaction Processing and Operational Reporting. 2008 IEEE Symposium on Advanced Management of Information for Globalized Enterprises (AMIGE), 1-5.
10. Cahill, M.J. & Röhm, U. & Fekete, A. (2008). Serializable isolation for snapshot databases. ACM(SIGMOD),doi:10.1145/1376616.1376690
11. Cao, W., Li, F., Huang, G., Lou, J., Zhao, J., He, D., Sun, M., Zhang, Y., Wang, S., Wu, X., Liao, H., Chen, Z., Fang, X., Chen, M., Liang, C., Luo, Y., Wang, H., Wang, S., Ma, Z., Yang, X., Peng, X., Ruan, Y., Wang, Y., Zhou, J., Wang, J., Hu, Q., & Kang, J. (2022). PolarDB-X: An Elastic Distributed Relational Database for Cloud-Native Applications. 2022 IEEE 38th International Conference on Data Engineering (ICDE), 2859-2872.
12. Fekete, A.D., O'Neil, E.J., & O'Neil, P.E. (2004). A read-only transaction anomaly under snapshot isolation. SIGMOD Rec., 33, 12-14.
13. Filip, P., & Cegan, L. (2020). Comparison of MySQL and MongoDB with focus on performance. 2020 International Conference on Informatics, Multimedia, Cyber and Information System (ICIMCIS), 184-187.
14. Kingsbury, K., & Alvaro, P. (2020). Elle: Inferring Isolation Anomalies from Experimental Observations. ArXiv, abs/2003.10554.
15. Kleppmann, M. (2017). Designing Data-Intensive Application, Publisher(s): O'Reilly Media, Inc. ISBN: 9781491903100
16. Li, K., Weng, S., Ni, L., Yang, C., Zhang, R., Zhou, X., & Zhou, A. (2024). DBStorm: Generating Various Effective Workloads for Testing Isolation Levels. International Symposium on Software Testing and Analysis.
17. Liarokapis, D., O'Neil, E.J., & O'Neil, P.E. (2020). Testing Concurrency in Databases still Matters. 2020 International Conference on Information Technologies (InfoTech), 1-6.
18. Ports, D. R. K., Grittner, K., (2012). Serializable snapshot isolation in PostgreSQL. VLDB, https://doi.org/10.48550/arXiv.1208.4179. Proceedings of the VLDB Endowment (PVLDB), Vol. 5, No. 12, pp. 1850-1861
19. Salunke, S.V., & Ouda, A. (2024). A Performance Benchmark for the PostgreSQL and MySQL Databases. Future Internet.
20. Silberschatz, A. & Korth, H. F. & Sudarshan, S. (2019) Database System Concepts, Seventh Edition, McGraw-Hill ISBN 9780078022159
21. URL-1:http://www.bailis.org/blog/when-is-acid-acid-rarely/#acidtable [Accessed June, 2024]
22. URL-2:http://www.interdb.jp/pg/pgsql05.html [Accessed June, 2024]
23. URL-3:https://postgrespro.com/blog/Rogov [Accessed June, 2024]
24. URL-4:Yahoo!CloudServingBenchmark(YCSB) https://github.com/brianfrankcooper/YCSB/ [Accessed June, 2024]
25. URL-5:HammerDB, https://www.hammerdb.com/download.html [Accessed June, 2024]
26. URL-6:pgbench, https://www.postgresql.org/docs/ current/pgbench.html [Accessed June, 2024]
27. URL-7:TPC-BHomepage, https://www.tpc.org/tpcb/ [Accessed June, 2024]
28. URL-8:TuningYourPostgreSQLServer, https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server [Accessed June, 2024]
29. URL-9:PostgreSQL Benchmarking Toolset, https://github.com/gregs1104/pgbench-tools [Accessed Sept. 28, 2023]
30. Wang, T., Johnson, R., Fekete, A.D., & Pandis, I. (2016). Efficiently making (almost) any concurrency control mechanism serializable. The VLDB Journal, 26, 537 - 562.
31. Wiechork, K., & Charão, A.S. (2020). Investigating the Performance of Moodle Database Queries in Cloud Environments. International Conference on Enterprise Information Systems.
32. Tongkaw, S., & Tongkaw, A. (2016). A comparison of database performance of MariaDB and MySQL with OLTP workload. 2016 IEEE Conference on Open Systems (ICOS), 117-119.
33. Zendaoui, F., & Hidouci, W. (2015). Performance evaluation of serializable snapshot isolation in PostgreSQL. 2015 12th International Symposium on Programming and Systems (ISPS), 1-11.