The Internals of Postgres – Concurrency Control

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,
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

Original URL:

Original article

Comments are closed.

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑

%d bloggers like this: