5. Concurrency Control

Concurrency Control is a mechanism that maintains atomicity and isolation (two key properties of ACID) when multiple transactions execute concurrently in a database.

There are three major concurrency control techniques: Multi-version Concurrency Control (MVCC), Strict Two-Phase Locking (S2PL), and Optimistic Concurrency Control (OCC). Each technique has many variations.

In MVCC, each write operation creates a new version of a data item while retaining the old version. When a transaction reads a data item, the system selects one of the versions to ensure isolation of the individual transaction. The main advantage of MVCC is that “readers don’t block writers, and writers don’t block readers”. In contrast, for example, an S2PL-based system must block readers when a writer writes an item because the writer acquires an exclusive lock for the item. PostgreSQL and some RDBMSs use a variation of MVCC called Snapshot Isolation (SI).

To implement SI, some RDBMSs, such as Oracle, use rollback segments. When a data item is updated, the old version is moved to a rollback segment, and the new version overwrites the original data area. PostgreSQL uses a simpler method. A new data item is inserted directly into the relevant table page. When reading items, PostgreSQL selects the appropriate version of an item in response to an individual transaction by applying visibility check rules.

SI does not allow the three anomalies defined in the ANSI SQL-92 standard: Dirty Reads, Non-Repeatable Reads, and Phantom Reads. However, SI cannot achieve true serializability because it allows serialization anomalies, such as Write Skew and Read-only Transaction Skew. Note that the ANSI SQL-92 standard based on the classical serializability definition is not equivalent to the definition in modern theory.

To deal with this issue, Serializable Snapshot Isolation (SSI) has been added as of version 9.1 (2011). SSI detects the serialization anomalies and resolves the resulting conflicts. Thus, PostgreSQL versions 9.1 or later provide a true SERIALIZABLE isolation level. (In addition, SQL Server also uses SSI; Oracle still uses only SI.)

This chapter comprises the following four parts:

1. Foundations (Sections 5.1 — 5.3): Covers transaction IDs, tuple structure, and basic tuple operations (insert, delete, update).
2. Key Components (Sections 5.4 — 5.6): Explains the commit log (clog), transaction snapshots, and visibility check rules.
3. Concurrency Control Mechanics (Sections 5.7 — 5.9): Demonstrates visibility checks, ANSI SQL anomaly prevention, Lost Update handling, and SSI.
4. Maintenance (Section 5.10): Introduces background maintenance tasks, which are executed via VACUUM processing (covered in Chapter 6).

This chapter focuses on the topics that are unique to PostgreSQL, although there are many concurrency control-related topics. Note that descriptions of deadlock prevention and lock modes are omitted. (For more information, refer to the official documentation.)