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.
Types
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)
MVCC
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.
S2PL
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.
OCC
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
How lets discuss about transactions. Whenever a transaction begins, a new unique transacion id (txid) called transaction manager is assigned. Which is 32-bit unsigned interger.
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.
Thats all for this time. If you want to explore more, don't forget to check references.
Top comments (0)