Q15 — How does a database transaction work under the hood?

What does BEGIN actually do?

When you execute BEGIN (or START TRANSACTION), the database doesn't acquire any locks, touch any disk, or allocate any data structures in the visible data yet. It primarily does one thing: assigns your connection a transaction ID (XID) — a unique, monotonically increasing number that identifies this transaction.

This transaction ID is the thread that ties everything together. Every change made during this transaction will be tagged with this XID — in the WAL records, in the row versions on disk, in the lock table. It's how the database tracks what belongs to whom.

What happens when you read a row?

When you execute SELECT * FROM users WHERE id = 42, the database needs to determine which version of row 42 you should see. This is where MVCC — Multi-Version Concurrency Control — comes in.

Most production databases (PostgreSQL, MySQL InnoDB, Oracle) use MVCC instead of simple locking for reads. The idea: instead of blocking reads when a writer is active, keep multiple versions of each row simultaneously. A reader always sees a consistent snapshot as of a certain point in time; writers create new versions without disturbing readers.

PostgreSQL's implementation: every row has two hidden fields — xmin (the transaction ID that created this version) and xmax (the transaction ID that deleted or updated this version, or 0 if the row is current). When your transaction reads a row, PostgreSQL checks: is xmin from a committed transaction that started before mine? Is xmax zero (row still current) or from a transaction that started after mine? If so, this version is visible to you.

This means readers never block writers and writers never block readers. Each transaction sees a consistent snapshot of the database as it existed when the transaction started (under Read Committed: when the statement started; under Repeatable Read/Serializable: when the transaction started).

What happens when you write a row?

When you execute UPDATE users SET email = 'new@example.com' WHERE id = 42:

First: the database checks if any other transaction holds a conflicting lock on row 42. Under standard locking semantics, two transactions can't both be updating the same row. If there's a conflict, your transaction waits until the other one commits or rolls back.

Second: the database reads the current version of row 42 into the buffer pool — an in-memory cache of database pages. If the page is already in the buffer pool from a recent read, it's already there.

Third: the change is made in memory — in the buffer pool. The original row version on disk is not immediately touched. PostgreSQL creates a new row version in the buffer pool with the new email, sets xmin to your transaction's XID, and marks the old row version as xmax = your XID (meaning: this old version was superseded by your transaction).

Fourth: a WAL record is written describing this change — what page was modified, what the old and new values were, which transaction made the change. This WAL record goes into the WAL buffer (in memory first, not yet on disk).

The actual data pages on disk are not updated yet. The change lives only in the buffer pool and the WAL buffer.

What happens at COMMIT?

This is the critical moment. When you execute COMMIT:

The database writes a commit record to the WAL buffer for your transaction ID.
The database calls fsync — it tells the OS to flush all WAL buffer contents for this transaction to physical disk. This is the durability moment. fsync blocks until the disk confirms the data has been written. This is the one unavoidable disk latency cost of a durable commit.
Once fsync returns, the commit is durable. Even if the machine crashes one millisecond after this point, the WAL contains the full committed transaction and recovery will re-apply it.
Only now does the database return "OK" to the client.

The dirty pages in the buffer pool — the actual modified data files — are written to disk later, in the background, by a process called the bgwriter (in PostgreSQL) during checkpoints. The WAL guarantees that even if dirty pages haven't hit disk yet, the changes can be reconstructed.

What happens at ROLLBACK?

When you execute ROLLBACK (or the connection drops, or a constraint violation forces a rollback):

The database writes an abort record to the WAL. All the row versions your transaction created (with xmin = your XID) are now permanently invisible — other transactions' MVCC visibility checks will see that your XID is aborted and ignore those versions.

Any locks your transaction held are released immediately.

No undo work is needed on the data pages themselves — PostgreSQL's MVCC model is append-only. Your new row versions simply become invisible garbage, later cleaned up by a process called VACUUM. No log needs to be "undone" on disk.

Other databases (MySQL InnoDB, Oracle) maintain explicit undo logs and physically reverse changes on rollback — a different trade-off with its own costs and benefits.

How do isolation levels change this behavior?

The transaction mechanics described above are the same across isolation levels. What changes is the MVCC snapshot timestamp used for reads:

Read Committed: Each SQL statement within the transaction gets a fresh snapshot — it sees all transactions that committed before that statement started. If another transaction commits between two of your SELECT statements, your second SELECT sees the new data.

Repeatable Read: The snapshot is taken once at the start of the transaction. Every read within the transaction sees the same state — the one that existed when BEGIN was executed. Changes committed by others during your transaction are invisible to you.

Serializable: Same snapshot as Repeatable Read, plus additional checks (predicate locks or serialization conflict detection) to ensure the transaction could have been executed serially without anomaly. Transactions may abort and need to retry if a serialization conflict is detected.

What is a deadlock and how does the database handle it?

Two transactions, each waiting for a lock the other holds:

Transaction A holds a lock on row 1, waits for a lock on row 2
Transaction B holds a lock on row 2, waits for a lock on row 1

Neither can proceed. This is a deadlock.

The database has a background process that periodically checks the lock dependency graph for cycles. When it finds a cycle, it picks one transaction to be the "victim" — typically the one that has done less work — and aborts it with an error. The other transaction then proceeds. The aborted transaction must be retried by the application.

Deadlocks are not bugs in the database. They're a fundamental consequence of allowing multiple transactions to hold and acquire locks concurrently. Applications must be written to handle them (retry on deadlock error).

The mental model

code

BEGIN
  → Assign transaction XID
  → Take MVCC snapshot timestamp

READ
  → Check buffer pool (in memory) → or read page from disk into buffer pool
  → Apply MVCC visibility: show only rows where xmin is committed and xmax is absent/future

WRITE
  → Acquire row-level lock
  → Modify page in buffer pool (in memory)
  → Write WAL record to WAL buffer (in memory)

COMMIT
  → Write commit record to WAL buffer
  → fsync: WAL buffer → disk (the durability moment)
  → Release locks
  → Return OK to client
  [Dirty pages flushed to disk later during checkpoint]

ROLLBACK
  → Write abort record to WAL
  → Release locks
  → Row versions created by this XID become invisible via MVCC
  → No disk undo needed (MVCC is append-only)

Dirty pages reach disk via: background writer + periodic checkpoints
Recovery after crash: replay committed WAL records from last checkpoint forward

A transaction is not a simple begin/end wrapper. It is a carefully orchestrated sequence of snapshot management, in-memory mutation, append-only logging, and a single durable flush — designed to give applications atomicity and isolation while keeping the fast path (the write) as cheap as possible.