Database Transactions and MVCC

A transaction groups several operations into one unit, while MVCC separates row versions to reduce conflicts between reads and writes.

A transaction groups database operations into one logical unit of work. In a bank transfer, the debit and credit must succeed together or fail together. Applying only one operation makes the balances inconsistent. The database therefore needs a mechanism to process both operations safely.

Transactions and ACID

The four properties a transaction must guarantee are known as ACID. Each is implemented by a different internal mechanism.

An account transfer is the clearest illustration of atomicity. Grouping two updates into one transaction makes it possible to undo the first when the second fails.

BEGIN;
  UPDATE accounts SET balance = balance - 100000 WHERE id = 'A';  -- debit
  UPDATE accounts SET balance = balance + 100000 WHERE id = 'B';  -- credit
COMMIT;
-- If the second UPDATE fails, ROLLBACK also cancels the first UPDATE.

A commit confirms all operations. A rollback restores the state before the transaction. The database records previous values in the undo log and uses them during rollback.

The database provides mechanisms for atomicity and durability, while consistency also requires correct constraints and application logic. Isolation directly affects concurrency. Serializable isolation gives a strong guarantee but can increase retries when transactions conflict.

The read anomalies concurrency creates

When several transactions touch the same data at once, read results can disagree. Three anomalies are the classic examples.

These anomalies occur when another transaction changes data during the current transaction. A dirty read sees an uncommitted value. The other two occur when data changes before the second read. The isolation level determines which anomalies are permitted.

The four isolation levels

The SQL standard (ISO/IEC 9075) defines four isolation levels and specifies which anomalies each permits. Higher in the table means more concurrency; lower means stronger consistency.

The table gives the minimum the standard guarantees; actual behavior varies by database. Knowing the defaults and the implementation differences matters more in practice.

• Read Uncommitted guarantees almost no consistency and is rarely used in production.
• Read Committed is the PostgreSQL default and sees only data committed as of each statement's execution (per the official PostgreSQL documentation).
• Repeatable Read is the MySQL InnoDB default and retains the snapshot created by the first consistent read (MySQL 8.4 documentation).
• Serializable behaves as if execution were serial but is expensive, so it is reserved for the core logic that truly needs it.

The standard and the implementations do not always line up. MySQL InnoDB adds gap locks at Repeatable Read and thereby blocks much of the phantom read that the standard permits.

Lock-based control

The classic way to enforce isolation is the lock. A lock grants a transaction access rights to data and makes conflicting access wait.

Several transactions reading the same row at once is safe, so shared locks coexist. A write, on the other hand, takes an exclusive lock and blocks all other access. The granularity of the lock also affects concurrency directly.

• Row-level locks lock only a specific row, give high concurrency, and are the default in InnoDB and PostgreSQL.
• Table-level locks lock the whole table, give low concurrency, and appear in legacy engines such as MyISAM.
• Gap locks lock the interval between index records to block inserts during a range scan.

Locks introduce deadlock, where transactions stall waiting on each other's locks. When two transactions each request the lock the other holds, neither can proceed.

flowchart LR
    TX1["Transaction 1<br/>holds the lock on row A"] -->|"requests row B, waits"| TX2["Transaction 2<br/>holds the lock on row B"]
    TX2 -->|"requests row A, waits"| TX1

There are three common ways to handle deadlock. A timeout can roll back one transaction after a fixed interval. Cycle detection in a wait-for graph can identify a transaction to terminate. Consistent lock acquisition order, such as ascending primary key order, prevents the cycle from forming.

MVCC

Locking every read makes reads and writes wait on each other. MVCC (multi-version concurrency control) removes that waiting by keeping several versions of the data at once.

An ordinary MVCC read avoids locks that conflict with writers and selects a row version visible to its snapshot. The snapshot lifetime depends on the isolation level. PostgreSQL Read Committed takes a new snapshot for each statement.

Repeatable Read keeps the snapshot from the first non-transaction-control statement (PostgreSQL documentation).

PostgreSQL records the transaction that created a row as xmin and the transaction that invalidated it as xmax. These values distinguish row versions. A transaction reads only the versions visible in its snapshot.

In this example, transaction 150 sees name='Kim' under two snapshot conditions. The snapshot includes transaction 100's commit and records transaction 200 as still in progress. Visibility does not depend only on comparing transaction IDs. PostgreSQL also checks snapshot bounds, the in-progress transaction set, and commit status.

MySQL InnoDB does not attach versions to the row itself; it manages them through the undo log. If the current row changed after the snapshot, InnoDB walks back through the undo log and reconstructs a visible version. In both PostgreSQL and InnoDB, ordinary consistent reads do not block writes, while locking reads such as SELECT FOR UPDATE are exceptions.

MVCC increases concurrency but accumulates old versions. PostgreSQL uses VACUUM to remove dead versions that are no longer visible and maintain storage efficiency and performance. Reducing read waits therefore creates version cleanup work.

Summary

A transaction uses ACID to bind several operations into one unit so that partial failure does not leave half of the work applied. The isolation level decides which anomalies concurrent execution may expose and how often conflicts require retries. Locks make conflicting access wait and can create deadlocks.

MVCC selects row versions visible to a snapshot and reduces conflict between ordinary reads and writes. PostgreSQL takes that snapshot per statement at Read Committed and retains it from the first non-control statement at Repeatable Read. Keeping old versions creates cleanup work such as VACUUM and undo-log purging.