What Is Concurrency Control in PostgreSQL With Example

Concurrency Control in PostgreSQL

To handle concurrent access to data, PostgreSQL has a complex concurrency control system that is mostly based on a multi-version concurrency control (MVCC) architecture. By keeping distinct versions of each row according to its status at different times, this method guarantees that neither readers nor writers will ever block one another. A consistent snapshot of the data is shown to transactions, reducing lock contention and enabling effective performance in multi-user settings.

Four levels of transaction isolation are defined by the SQL standard: serialisable, repeatable read, read committed, and read uncommitted. While PostgreSQL internally implements three different levels, it supports all four. The default isolation level, Read Committed, is acted upon by a request for Read Uncommitted.

While UPDATE, DELETE, and SELECT FOR UPDATE/SHARE commands also locate target rows committed at the command’s start time, waiting to check if a concurrent transaction has already updated them, SELECT queries in Read Committed mode view data committed prior to the query’s start. Repeatable Read frequently stops phantom reads in PostgreSQL and guarantees that a transaction sees a snapshot of the database as of its beginning, meaning that subsequent queries within the same transaction will view identical data.

Multi-Version Concurrency Control (MVCC)

PostgreSQL’s concurrency control relies on snapshot isolation protocol MVCC. MVCC’s key principle is to keep numerous copies of each row, each representing its state at different moments. Every transaction can see a consistent snapshot of the data, regardless of other concurrent transactions’ changes.

One major benefit of MVCC is that neither writers nor readers ever block one another. Because write activities make a separate copy of the row to be altered, while read operations access a version of the row that is included in the transaction’s snapshot, this is the case. Traditional locking-based database systems frequently experience lock contention, which is reduced by this method.

Transaction Isolation Levels

Read Committed, Repeatable Read, Serialisable, and Read Uncommitted are the four transaction isolation levels specified by the SQL standard. PostgreSQL allows you to set any of these, but it only implements three different levels internally. A read-uncommitted request is handled as read-committed.

Read Committed: PostgreSQL defaults to this isolation level. A SELECT query in Read Committed mode displays data committed before the query started. Uncommitted data or modifications made by concurrent transactions while the query is running won’t be visible to it. However, if there are modifications made by other transactions between the two SELECT statements, subsequent SELECT queries within the same transaction may view different data.

Target rows committed at the start time of the UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE procedures are also located. The current transaction will pause until the other concurrent transaction commits or rolls back if a target row has been modified or locked. The current transaction reassesses its WHERE clause on the modified row for any updates committed by the other transaction.

Repeatable Read: At this level, a snapshot taken at the beginning of the transaction is used by all queries within it. Therefore, even if other transactions commit changes while a transaction is being executed, subsequent SELECT statements within that transaction will always view the same data. The Repeatable Read implementation in PostgreSQL is more stringent than what the SQL standard calls for at this level in order to prevent phantom reads.

If a UPDATE, DELETE, or SELECT FOR UPDATE/SHARE command finds a row updated and committed by another transaction since the repeated read transaction began, the current transaction will be rolled back with a serialisation failure error. Prior to PostgreSQL 9.1, Serialisable isolation level affected this behaviour.

Serializable: This is the most stringent level of isolation, offering complete serializability by simulating the execution of serial transactions, as though they were sequential. It functions similarly to Repeatable Read, but it also keeps an eye out for circumstances that can cause abnormal serialisation. Should such a condition be found, one of the transactions that are in disagreement is reversed.

In order to ensure serializability, PostgreSQL uses predicate locking. Instead of blocking, these locks find dependencies between concurrent serialisable transactions that can cause irregularities. For predicate locks that are visible in pg_locks, SIReadLock is the mode in use.

Explicit Locking

While MVCC addresses most concurrency needs, PostgreSQL provides explicit locking when needed or application-controlled locking is needed.

Table-level Locks: To prevent incompatible modifications while PostgreSQL commands are running, they automatically lock tables. To prevent concurrent processes on the table, TRUNCATE secures an ACCESS EXCLUSIVE lock. The LOCK TABLE command specifically acquires table-level locks. SELECT statements without FOR UPDATE/SHARE are only prevented in ACCESS EXCLUSIVE mode. To counter DDL commands, DML commands, on the other hand, obtain less restrictive locks.

Row-level Locks: SELECT FOR UPDATE or SHARE clauses lock rows. UPDATE locks the fetched rows as if for an update to prevent subsequent transactions from locking, altering, or deleting them until the current transaction is finished. By acquiring a shared lock, FOR SHARE permits additional transactions to get shared locks on the same row, but it stops exclusive locks, updates, and deletions.

Advisory Locks: Advisory locks are handled by the application rather than the system to ensure data consistency. They are application-defined locks that are not connected to particular data objects. They avoid table bloat and are faster than flag-based locking. Transaction-level advisory locks are automatically released at the conclusion of a transaction, but session-level locks are retained until they are expressly released or the session terminates.

Deadlocks

Two or more transactions holding locks that another transaction is waiting to acquire are said to be in a stalemate. By stopping one of the affected transactions and letting the rest continue, PostgreSQL automatically discovers and fixes deadlocks. Applications should acquire locks on numerous objects in a consistent order to prevent deadlocks.

Code Example:

With an accounts table and two concurrent transactions trying to edit the same account, let’s look at a straightforward example.

-- Create a sample table
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    balance DECIMAL(10, 2) NOT NULL
);
-- Insert initial data
INSERT INTO accounts (id, balance) VALUES (1, 100.00);

Output:

CREATE TABLE
INSERT 0 1

Conclusion

To guarantee data integrity and optimal performance in multi-user settings, PostgreSQL’s concurrency control system integrates Multi-Version Concurrency Control (MVCC), adjustable transaction isolation levels, explicit locking mechanisms, and automatic deadlock detection. MVCC greatly reduces lock contention by allowing readers and writers to function without obstructing one another by maintaining several row versions. With PostgreSQL implementation, problems like phantom reads and serialisation anomalies are avoided.

Transaction isolation levels, ranging from Read Committed to Serialisable, provide fine-grained control over consistency. Table-level, row-level, and advisory locks offer extra protection when more stringent control is needed, and integrated deadlock detection automatically settles disputes to maintain seamless transactions. All of these characteristics work together to make PostgreSQL a stable, effective, and dependable solution for managing concurrent data access.