Understanding Database Transactions: ACID, Isolation Levels, and MVCC Explained

What Is a Transaction?

A database transaction is a logical unit of work that groups a finite sequence of read, write, or schema‑changing operations (e.g., INSERT, UPDATE, DELETE, CREATE INDEX, DROP TABLE) so that they either all succeed or all fail together.

Example: transferring 100 units from account Bob to account Smith involves three steps—check balance, debit Bob, credit Smith. The whole sequence is a single transaction.

Create an index on a column

Read a row

Insert a row and update it

Delete an entire table

…

Atomicity

Atomicity guarantees that a transaction is all‑or‑nothing. The DB engine records the undo log —the previous state of every modified row—so that if a failure occurs after some operations have been applied, the engine can restore the original state.

Rollback must be performed in reverse order of execution (FILO) to avoid inconsistencies. Concurrency control is required to ensure that interleaved rollbacks from different sessions do not corrupt data.

Implementation steps (MySQL example) :

-- Step A: check balance (read only, no undo needed)
SELECT money FROM accounts WHERE id=1;

-- Step B: debit Bob (writes undo record)
UPDATE accounts SET money = money - 100 WHERE id=1;

-- Step C: credit Smith (writes undo record)
UPDATE accounts SET money = money + 100 WHERE id=2;

If Step C fails, the engine uses the undo log to revert Step B, restoring Bob’s original balance.

Consistency

Consistency requires that a transaction moves the database from one valid state to another. Using the transfer example, the database passes through three versions:

Version 1 : Bob=100, Smith=0 (initial state)

Version 2 : Bob=0, Smith=0 (after debit)

Version 3 : Bob=0, Smith=100 (after credit)

Applications must see only Version 1 or Version 3; Version 2 must never be exposed. Consistency is enforced primarily by locking—transactions cannot read intermediate states.

Isolation

Isolation prevents concurrent transactions from interfering with each other. Four standard isolation levels are defined:

Serializable : transactions execute one after another using exclusive locks; guarantees no anomalies but severely limits parallelism.

Repeatable Read : a transaction sees a consistent snapshot for the duration of the transaction; writes acquire exclusive locks, which can cause phantom reads .

Read Committed : each SELECT sees only data committed before the statement starts; read locks can be upgraded to write locks, allowing higher concurrency but permitting non‑repeatable reads .

Read Uncommitted : the lowest level; allows dirty reads of uncommitted data.

Lock splitting and read/write lock separation improve concurrency:

Lock splitting assigns separate locks to non‑conflicting rows or tables, allowing independent transactions to proceed in parallel.

Read/write locks let multiple readers share a lock (S‑lock) while writers obtain an exclusive lock (X‑lock). This enables read‑read parallelism and, when appropriate, read‑write parallelism.

Example of lock splitting (three independent transactions run concurrently instead of serially):

Durability

Durability ensures that once a transaction commits, its effects survive crashes and power failures. Most DBMSs write changes to a write‑ahead log (WAL) before acknowledging success, then flush the log to stable storage asynchronously. If a crash occurs after the commit, the log can be replayed to reconstruct the committed state.

Multi‑Version Concurrency Control (MVCC)

MVCC solves the "write blocks read" problem by keeping multiple versions of each row. When a transaction updates a row, it creates a new version and stores the previous version in the undo log (snapshot).

Read‑only transactions can access the appropriate snapshot without acquiring locks, while write transactions work on the latest version.

Two kinds of reads:

Snapshot read (e.g., plain SELECT): reads the version visible at the transaction’s start time; no lock is taken.

Current read (e.g., SELECT … FOR UPDATE, INSERT, UPDATE, DELETE): reads the latest version and acquires an X‑lock to prevent concurrent modifications.

MySQL InnoDB implements MVCC with a per‑transaction ID (trx_id). Under READ COMMITTED , each SELECT sees the latest committed version. Under REPEATABLE READ , each SELECT sees the version that was committed when the transaction began.

Example of version visibility:

-- Transaction A (REPEATABLE READ) starts
SELECT amount FROM accounts WHERE id=1;   -- returns 1

-- Transaction B (READ COMMITTED) updates the row
UPDATE accounts SET amount=3 WHERE id=1;
COMMIT;

-- Transaction A issues the same SELECT again
SELECT amount FROM accounts WHERE id=1;   -- still returns 1 (snapshot)

-- Transaction C (READ COMMITTED) issues SELECT
SELECT amount FROM accounts WHERE id=1;   -- returns 3 (latest committed)

Internally, the engine maintains a logical timestamp (e.g., Oracle SCN, InnoDB trx_id) to decide which version a read should see.

Reflection

The core of transaction management is balancing lock granularity and concurrency:

Row‑level locks (InnoDB) reduce contention compared to table‑level locks (MyISAM).

Lock splitting and read/write lock separation further increase parallelism.

Choosing between pessimistic (locks) and optimistic (version checks) strategies depends on the expected contention.

MVCC provides "no‑lock reads" for snapshots, achieving write‑does‑not‑block‑read semantics.

In practice, a well‑designed transaction system combines appropriate isolation levels, fine‑grained locking, and MVCC to achieve both correctness (ACID) and high performance.