DEV Community

Aadil Bashir
Aadil Bashir

Posted on

Concurrency Control in PostgreSQL - An in-depth guide

Welcome to the fifth article of my blog series on PostgreSQL. In this blog, I will be giving an overview and summary of Concurrency Control, without further ado let's begin.

Concurrency Control

Concurrency Control is a mechanism that maintains atomicity and isolation the two main properties of ACID. Further we can define it as when several transactions run at the same time in the database.


There are three main concurrency control techniques, having many variations each.

  • Multi-version Concurrency Control(MVCC)
  • Strict Two-Phase locking (S2PL)
  • Optimistic Concurrency Control (OCC)


In this techniques, each write operation creates a new version of a data item while retaining the old data version.
And while reading data, it is designed in a way that the system selects one of the version of the data versions just to ensure isolation of the individual transaction. In this technique the main thing is that it does not block the system, means reader don't block writer and vice versa during a transaction.


In contract to MVCC, this system block readers when a write is writing something and vice versa. Its because the reader or writer get the exclusive locks during its processing and using that it blocks the activity of the other one to perform actions.


It ensure concurrency without the usage of a lock mechanism. OCC assumes that conflicts are not too frequent to occure, and it uses a validation mechanism to ensure the consistency of data. Also in this, each transaction is allowed to be performed without checking for any conflict until it reaches its commit points. At commit point it decides either to proceed or abort depending on conflits status.
Main advantage is that it reduces the main overhead of getting and releasing lock. which in turn increases the system performance.

Snapshot Isolation (SI)

PostgreSQL along with some other RDBMSs use some variations of MVCC. These variations are called snapshot isolations.
These RDBMSs like Oracle use rollback segments. And this rollback segment is used to write new data item by over Writing to the old data area. And the new data item is inserted at the relevant data page.
Main point about this SI is, it does not allow anomalies defined in ANSI SQL-92 standards. These anomalies are Dirty Reads, Non-Repeatable Reads and Phantom Reads. Serializabl Snapshot Isolation (SSI) has been added to some latest versions just to deal with these issues.

Transaction ID

Whenever a transaction begins, a new unique transacion id (txid) called transaction manager is assigned. Which is 32-bit unsigned interger.

Image description
PostgreSQL treats the txid space as a circle just to avoid the space issues.

Tuple Structure

  • Heap tuple(usual data tuple)
  • Toast tuple

In this we are going to discuss only the usual data tuple.

It has three parts, HeapTupleHeaderData structure, NULL bitmap, and user data as shown in figure below.

Image description


If you want to explore it further please read from the given link below.
Chapter-5 The Internals of PostgreSQL

Top comments (0)