Database Transactions and MVCC

A transaction groups several operations into one unit, and MVCC raises concurrent throughput by never locking reads.

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.

Among the ACID properties, atomicity, consistency, and durability are largely guaranteed automatically. Isolation is the difficult one. Enforcing it perfectly requires running transactions nearly serially, which collapses concurrent throughput.

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 holds a snapshot taken at the start of the transaction (per the official MySQL documentation).
• Serializable behaves as if execution were serial but is expensive, so it is reserved for the core logic that truly needs it.

One point deserves attention: the standard and the implementations do not 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.

The idea rests on two points. Reads take no locks, so reads and writes never conflict. Each transaction sees only the snapshot taken when it started, so changes made by other transactions in the meantime do not affect it.

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.

When transaction 150 reads this row, xmin=100 refers to an already committed version, while xmax=200 is not yet committed from transaction 150's point of view. Its snapshot therefore shows the name='Kim' version.

MySQL InnoDB does not attach versions to the row itself; it manages them through the undo log. If the current row was modified after your transaction began, InnoDB walks back through the undo log and reconstructs the version as of your point in time. Either way, the outcome is the same: reads need no locks.

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 in order 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 a partial failure never leaves data inconsistent. Of the four properties, only isolation requires a tradeoff against concurrency and performance. The SQL standard therefore splits isolation into four levels that let you choose how many anomalies to allow.

Locks enforce isolation by making conflicting access wait, but they produce deadlocks and read stalls. MVCC raises concurrency by splitting versions and never locking reads, leaving behind cleanup work such as PostgreSQL VACUUM as an operational cost. Isolation levels and concurrency control mechanisms have to be understood together, at the level of each database's implementation. That understanding produces better defaults between consistency and performance.