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 is a set of operations that form a single logical unit of work in a database. In a bank transfer, the debit and the credit must succeed together or fail together. If only one of them lands, the balances no longer agree, so the database needs a mechanism to handle such groups 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.

Confirming every operation is a commit, and returning to the pre-transaction state is a rollback. The database keeps the pre-update values in the undo log, which is what makes recovery on rollback possible.

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.

All three arise because another transaction's change cuts in during your transaction. A dirty read is the problem of seeing an uncommitted value; the other two are the problem of data changing by the time of the second read. Deciding how much of this to allow is what an isolation level does.

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.

Deadlock is typically handled in three ways. A timeout rolls one side back after a fixed interval, or a cycle is detected in the wait-for graph and one side is killed. As prevention, unifying lock acquisition order (for example, always accessing in ascending primary key order) stops the cycle from forming at all.

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 distinguishes versions by attaching to each row the transaction number that created it (xmin) and the transaction number that invalidated it (xmax). A transaction reads only the versions visible in its snapshot.

In this example, transaction 150 sees name='Kim' if its snapshot includes the commit of transaction 100 and records transaction 200 as still in progress. Visibility is not a simple numeric comparison of transaction IDs. PostgreSQL also checks the snapshot bounds, its 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 buys a large gain in concurrency at the cost of accumulating old versions. PostgreSQL has to clear away dead versions that are no longer visible through VACUUM to keep storage and performance in shape. In other words, the read waiting it removes comes back as an operational cleanup burden.

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.