Concurrency Control is a mechanism that maintains consistency and isolation, which are two properties of the ACID, when several transactions run concurrently in the database.
There are three broad concurrency control techniques, i.e. Multi-version Concurrency Control (MVCC), Strict Two-Phase Locking (S2PL), and Optimistic Concurrency Control (OCC), and 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, e.g.,
Oracle, use rollback segments. When writing a new data item, the old version of the item is written to the rollback segment, and subsequently the new item is overwritten to the 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, i.e. 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. SSI can detect the serialization anomalies and can resolve the conflicts caused by such anomalies. Thus, PostgreSQL version 9.1 and later provides a true SERIALIZABLE isolation level. (In addition, SQL Server also uses SSI, Oracle still uses only SI.)
PostgreSQL uses SSI for DML (Data Manipulation Language, e.g, SELECT, UPDATE, INSERT, DELETE), and 2PL for DDL (Data Definition Language, e.g., CREATE TABLE, etc).
Transaction Isolation Level in PostgreSQL
| Isolation Level | Dirty Reads | Non-repeatable Read | Phantom Read | Serialization Anomaly |
|---|---|---|---|---|
| READ COMMITTED | Not possible | Possible | Possible | Possible |
| REPEATABLE READ*1 | Not possible | Not possible | Not possible in PG; (Possible in ANSI SQL) | Possible |
| SERIALIZABLE | Not possible | Not possible | Not possible | Not possible |