What Actually Happens Inside a Database Transaction

The transaction that seemed simple

You have a funds transfer: debit account A, credit account B. You wrap it in a transaction. It's atomic — either both happen or neither does. This is the textbook use case, and it's clear.

But then you have a transaction that: looks up an inventory item, checks a price, applies a discount, inserts an order, updates inventory, and sends a notification via a stored procedure. It holds locks for 800ms. Concurrency drops. Support tickets start arriving. You're not sure what the transaction is actually doing during those 800ms.

Understanding the mechanics of transactions — not just the ACID guarantees — is what separates developers who can debug concurrency problems from those who can only restart the database and hope.

ACID: what each letter actually means

Atomicity: all operations in a transaction commit together or none do. Implemented via the write-ahead log (WAL) — operations are written to the log first, and the log is what determines whether a transaction committed. If the server crashes mid-transaction, the WAL is replayed on recovery and uncommitted transactions are rolled back.

Consistency: the database moves from one valid state to another. Enforced through constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY) evaluated at commit time. If any constraint fails, the transaction rolls back.

Isolation: concurrent transactions see a consistent view of the data, as if they ran serially. Implemented via locks (in MySQL's InnoDB by default) or MVCC (in PostgreSQL and InnoDB's snapshot reads). The degree of isolation is configurable — see isolation levels.

Durability: a committed transaction survives a server crash. Guaranteed by the write-ahead log being flushed to durable storage before the commit acknowledgment is sent to the client. fsync = on in PostgreSQL, innodb_flush_log_at_trx_commit = 1 in MySQL.

MVCC: how reads and writes coexist

PostgreSQL uses Multi-Version Concurrency Control (MVCC). Rather than blocking readers when a writer updates a row, MVCC creates a new version of the row:

• The old version remains visible to transactions that started before the update
• The new version is visible to transactions that start after the commit
• Both versions coexist in the heap until the old version is cleaned up by VACUUM

This means readers never block writers and writers never block readers in PostgreSQL. The tradeoff: table bloat from accumulated row versions, requiring regular VACUUM to reclaim space.

Timeline:
T=0: Transaction A starts (sees snapshot at T=0)
T=1: Transaction B updates row X (creates new version X')
T=2: Transaction A reads row X — still sees old version (its snapshot is at T=0)
T=3: Transaction B commits
T=4: Transaction C starts (sees snapshot at T=4, sees X')
T=5: Transaction A reads row X — still sees old version (snapshot hasn't changed)
T=6: Transaction A commits

This is why a long-running transaction in PostgreSQL can prevent VACUUM from cleaning up old row versions — VACUUM can't remove a row version that might still be visible to an active transaction.

The write-ahead log

Every change — INSERT, UPDATE, DELETE — is first written to the WAL (Write-Ahead Log) before it's applied to the actual data pages. The WAL is sequential and append-only, making it fast to write.

On commit, the WAL is flushed to disk. Only then does the client receive the commit acknowledgment. This is what makes durability work: even if the server crashes immediately after acknowledging the commit, the WAL contains the complete record of what needs to be applied.

WAL is also the mechanism for replication. Standby replicas receive and replay the WAL stream, staying in sync with the primary.

Lock acquisition order

When a transaction acquires locks on multiple rows or tables, the order matters. If transaction A locks row 1 then row 2, and transaction B locks row 2 then row 1, and they run concurrently, they can deadlock — each waiting for the other's lock.

-- Transaction A:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1
-- ... some delay ...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- waits for row 2

-- Transaction B (concurrent):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- locks row 2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- waits for row 1
-- DEADLOCK

The fix: always acquire locks in a consistent order. In the funds transfer example, always lock the lower account ID first:

-- Application enforces consistent lock order
UPDATE accounts SET balance = balance - 100 WHERE id = LEAST(:from_id, :to_id);
UPDATE accounts SET balance = balance + 100 WHERE id = GREATEST(:from_id, :to_id);

What happens during those 800ms

When a transaction holds locks, other transactions trying to modify the same rows wait. In PostgreSQL, the default lock wait timeout is indefinite — transactions queue. In high-concurrency scenarios, lock queuing cascades: one slow transaction blocks many others, and their lock waits accumulate.

You can see lock waits in PostgreSQL:

SELECT
  pid,
  wait_event_type,
  wait_event,
  state,
  query_start,
  query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Any row here is a transaction currently blocked waiting for a lock. If you see many rows, you have a lock contention problem. Finding the blocking transaction:

SELECT pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

The practical insight

A transaction is not just a correctness boundary — it's a lock-holding interval. The longer your transaction runs, the longer it holds locks, the more concurrency you sacrifice. This is why "keep transactions short" is not generic advice — it's a direct consequence of how locking and MVCC work. Design your transactions to do the minimum work necessary to maintain consistency, and nothing more.