Concurrency control is a mechanism that ensures atomicity and isolation, two key properties of the ACID, when multiple transactions run simultaneously in a database. In this article, we will focus on the concurrency control mechanisms used in PostgreSQL, a popular open-source relational database management system.

Concurrency Control Techniques

There are three primary concurrency control techniques used in PostgreSQL:

Multi-version Concurrency Control (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. PostgreSQL uses a variation of MVCC called Snapshot Isolation (SI).

Strict Two-Phase Locking (S2PL): In S2PL, a writer acquires an exclusive lock for an item, blocking readers until the writer has finished.

Optimistic Concurrency Control (OCC): OCC assumes that conflicts between transactions are rare and allows transactions to proceed without locks. OCC checks for conflicts only at the end of each transaction and rolls back if conflicts are detected.

PostgreSQL uses MVCC with Snapshot Isolation as its primary concurrency control technique.

Transaction Isolation Levels
PostgreSQL supports the following transaction isolation levels:

READ COMMITTED: This level allows non-repeatable and phantom reads but prevents dirty reads.

REPEATABLE READ: This level allows phantom reads but prevents non-repeatable and dirty reads.

SERIALIZABLE: This level prevents all three anomalies: dirty reads, non-repeatable reads, and phantom reads.

It's important to note that in PostgreSQL version 9.0 and earlier, the REPEATABLE READ level was used as SERIALIZABLE because it prevented all three anomalies defined in the ANSI SQL-92 standard. However, with the implementation of Serializable Snapshot Isolation (SSI) in version 9.1, a true SERIALIZABLE level was introduced, and the REPEATABLE READ level was changed to its current definition.

Transaction ID

In PostgreSQL, each transaction is assigned a unique identifier called a transaction ID (txid). When a transaction starts, the transaction manager assigns a txid, which is a 32-bit unsigned integer. PostgreSQL reserves three special txids: 0 (Invalid txid), 1 (Bootstrap txid, used only in the initialization of the database cluster), and 2 (Frozen txid).

Concurrency Control in PostgreSQL

PostgreSQL uses Snapshot Isolation (SI) for DML (Data Manipulation Language, e.g., SELECT, UPDATE, INSERT, DELETE), and Strict Two-Phase Locking (S2PL) for DDL (Data Definition Language, e.g., CREATE TABLE, etc). Here are the main components of PostgreSQL's concurrency control mechanism:

Commit log (clog): The clog holds all transaction states.

Transaction snapshots: Transaction snapshots represent a consistent view of the database for a particular transaction.

Visibility check rules: Visibility check rules are used to determine which transaction snapshots are visible to other transactions.

Preventing Anomalies

PostgreSQL's Snapshot Isolation (SI) technique prevents the three anomalies defined in the ANSI SQL-92 standard: Dirty Reads, Non-Repeatable Reads, and Phantom Reads. However, SI does not achieve true serializability and allows serialization anomalies such as Write Skew and Read-only Transaction Skew.

To address this issue, Serializable Snapshot Isolation (SSI) was introduced in PostgreSQL version 9.1. SSI detects serialization anomalies and resolves conflicts caused by such anomalies, providing a true SERIALIZABLE isolation level.

Conclusion: Concurrency Control in PostgreSQL

In conclusion, concurrency control is a crucial mechanism in any database management system that allows multiple transactions to execute simultaneously while maintaining the integrity of the database. PostgreSQL implements three main concurrency control techniques: MVCC, S2PL, and OCC. Among these, MVCC is the most popular, and PostgreSQL uses a variation of it called Snapshot Isolation (SI).

While SI prevents the three anomalies defined in the ANSI SQL-92 standard, it cannot achieve true serializability because it allows serialization anomalies. To address this issue, Serializable Snapshot Isolation (SSI) was introduced in PostgreSQL version 9.1 and later, which provides a true SERIALIZABLE isolation level.

This chapter has discussed the key features required for implementing the concurrency control mechanism, including transaction ids, tuple structure, commit log (clog), transaction snapshots, and visibility check rules. We have also looked at specific examples of concurrency control in PostgreSQL and discussed the transaction isolation levels in PostgreSQL.

Overall, PostgreSQL's concurrency control mechanism is a powerful tool for managing multiple transactions in a database environment. Its use of MVCC and SI ensures that transactions can read and write data simultaneously without blocking each other, while SSI provides true serializability. By understanding these concepts and techniques, database administrators and developers can ensure that their applications are efficient, scalable, and reliable.