丁久

Posted on May 16 • Originally published at dingjiu1989-hue.github.io

Database Concurrency Control: MVCC, Locking, and Deadlocks

#database #backend #data

This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Database Concurrency Control: MVCC, Locking, and Deadlocks

Modern databases must handle thousands of concurrent transactions reading and writing the same rows. Concurrency control mechanisms ensure correctness while maximizing throughput. PostgreSQL's approach is built on Multi-Version Concurrency Control (MVCC).

Multi-Version Concurrency Control (MVCC)

MVCC is the foundation of concurrency in PostgreSQL and Oracle. Instead of locking rows for readers, MVCC preserves multiple versions of each row. Each transaction sees a snapshot of data as it existed at that transaction's start time.

Every row in PostgreSQL carries two hidden system columns: xmin (the transaction ID that created this version) and xmax (the transaction ID that deleted or updated this version). When a transaction reads a row, it checks visibility rules:

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- xmin must be committed and <= current transaction ID

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- xmax must be uncommitted or > current transaction ID

This design means readers never block writers, and writers never block readers. It is the single most important property for OLTP workloads.

Transaction Snapshots

A transaction's snapshot captures which transactions were in-progress at the moment the snapshot was taken. The REPEATABLE READ isolation level uses snapshot semantics:

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM accounts WHERE id = 1; -- sees snapshot at this moment

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Another transaction updates and commits account 1

SELECT * FROM accounts WHERE id = 1; -- still sees the original snapshot

COMMIT;

Optimistic vs Pessimistic Locking

Optimistic Locking

Optimistic locking assumes conflicts are rare. The application reads a row, performs work, and checks that the row has not changed before writing. It is typically implemented with a version column:

CREATE TABLE inventory (

id INTEGER PRIMARY KEY,

quantity INTEGER,

version INTEGER DEFAULT 1

);

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Application reads: SELECT quantity, version FROM inventory WHERE id = 42;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Application computes new quantity

UPDATE inventory

SET quantity = 5, version = version + 1

WHERE id = 42 AND version = 3; -- version from the read

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- If 0 rows updated, another transaction changed the row → retry

Optimistic locking works well when contention is low and transactions are short. It avoids holding database locks between application operations.

Pessimistic Locking

Pessimistic locking assumes conflicts are likely and acquires locks proactively:

BEGIN;

SELECT * FROM inventory WHERE id = 42 FOR UPDATE;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.

DEV Community

Database Concurrency Control: MVCC, Locking, and Deadlocks

Database Concurrency Control: MVCC, Locking, and Deadlocks