Concurrency Control in PostgresSQL

Concurrency control is the process of maintaining atomicity and isolation when two transactions run concurrently in the database.

There are three types of concurrency control techniques:

• Multi-version Concurrency Control (MVCC) is the technique that allows multiple transactions in database without blocking each other. PostgresSQL and some other RDMS uses the version of MVCC called as Snapshot Isolation (SI).

• Strict Two-Phase Locking (S2PL) is the technique that uses locks while accessing the shared resources in database. It means that if one transactions is happening, the lock will prevent other transactions to access the shared resource.

• Optimistic Concurrency Control works by reducing the need of locks during transactions in database. It supposes that the conflicts are not often. As described by its name, OCC works optimistically and allows transactions to happen but if the conflict occurs, OCC revert the transaction that causes the conflict.

Transaction ID:

The unique identifier (txid) assigned by the transaction manager to a transaction is known as transaction ID.

PostgresSQL has three types of txids:
0 means invalid txid.
1 means Bootstrap that is used in the initialization of database cluster.
2 means Frozen txid that is used in the for MVCC.

Commit Log:

Commit log holds the statuses of transactions. This log is allocated to the shared memory and is used throughout the transaction processing.
There are four transactions statuses in the commit log:

• IN_PROGRESS when transaction is in progress.

• COMMITTED when transaction completed successfully.

• ABORTED when transaction encounters some errors.

• SUB_COMMITTED when transaction goes under some custom implementations.

Transaction Snapshot:

Transaction snapshot refers to the dataset that stores all the information about the transaction like whether it is active, at a certain time for an individual transaction.

Serial Snapshot Isolation:

This concurrency method is used to make sure the high level of isolation. One transaction never gets the effect of other transactions in database and it can never read the data that is added by another transactions in the database but not committed successfully i.e. serialization.

References:

https://www.interdb.jp/pg/pgsql05.html