PostgreSQL Basics #6 Transactions and MVCC: The Fundamentals of Concurrency

Infrastructure PostgreSQL Database
4 min read

So far it’s been a single-user database, but in a real service dozens or hundreds of connections read and write the same tables simultaneously. The reason a balance never gets deducted twice, and half-saved orders never become visible, is transactions and MVCC. These principles are the floor that the practice series’ VACUUM, locking, and replication all stand on, so we lay them down here as the last hill of the basics.

Transactions: all or nothing #

Transfer transaction 
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;  -- both UPDATEs become final as one unit. If something goes wrong midway: ROLLBACK;

A world where only one of a transfer’s two UPDATEs applies must not exist. Work between BEGIN and COMMIT is bundled into a single unit: either all of it becomes final (COMMIT) or all of it never happened (ROLLBACK). An error or a dropped connection midway rolls back automatically. Note that a single SQL statement executed without an explicit BEGIN is also its own transaction (autocommit). “Do these statements need to be one unit” is the criterion for whether to use BEGIN.

MVCC: readers don’t block writers #

The naive implementation of concurrency is locking — block other writers while someone reads. Safe, but you end up with a database where everyone waits for everyone. PostgreSQL’s answer is MVCC (Multi-Version Concurrency Control): instead of overwriting data, keep multiple versions, and let each transaction see the version valid at its snapshot.

  • While transaction A runs a long aggregation, transaction B can UPDATE the same table with no waiting on either side. A sees the old versions in its snapshot; B just creates new versions.
  • So in PostgreSQL, reads and writes never block each other. The only thing that blocks is a write against a write on the same row (that locking story comes in the practice series).

One consequence matters enormously. UPDATE is not an in-place edit — it adds a new row version and marks the old one as disposable. DELETE doesn’t actually delete either; it only marks. The janitor that cleans up these accumulating old versions (dead tuples) is VACUUM, and this is why VACUUM shows up as a major topic in the practice series.

Isolation levels: what the default Read Committed means #

When the snapshot is taken is the isolation level. PostgreSQL’s default is Read Committed, which takes a fresh snapshot at the start of every statement. That means other sessions’ committed changes become visible from your next statement onward. It’s enough for most web applications, but there’s one trap: two SELECTs inside the same transaction can see different results (if someone else’s commit lands in between). If your logic reads, then decides, then writes, that gap is a problem.

Isolation levelSnapshot timingWhen to use
Read Committed (default)Every statementMost ordinary OLTP
Repeatable ReadOnce at transaction startReports and consistency checks that need “one coherent photograph”
SerializableOnce at start + serial-execution validationMoney math and anything where even concurrent interleavings must be excluded
Set isolation level 
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- every SELECT inside sees the same snapshot
COMMIT;

Know the price of raising it. At Repeatable Read and above, colliding with a concurrent modification raises an error (serialization failure), and the application must be prepared to retry the transaction. It doesn’t hand you safety for free — it trades “silently tolerating conflicts” for “reporting them as errors.”

Meanwhile, the more common practical fix for read-then-decide-then-write is not raising the isolation level but collapsing it into one atomic statement. UPDATE accounts SET balance = balance - 500 WHERE id = 1 AND balance >= 500 puts the balance check and the deduction in a single statement — no gap, regardless of isolation level.

Summary #

  • Transactions are all or nothing. Bundle multi-statement units with BEGIN/COMMIT; errors roll back automatically.
  • Thanks to MVCC, reads and writes never block each other. Each transaction sees its own snapshot.
  • UPDATE and DELETE mean new versions plus disposal marks. The cleanup of the resulting dead tuples (VACUUM) is a big practice-series topic.
  • The default Read Committed snapshots per statement. Even within one transaction, two SELECTs can disagree.
  • Need one coherent snapshot? Repeatable Read. Full serializability? Serializable — but be ready to retry. And collapse read-decide-write into one atomic statement first.
X