Concurrency Challenges in Database Systems: A Focus on Postgresql

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.