ACID and MVCC Explained with a Bank Transfer Example

ACID and MVCC answer two different questions. ACID is what a transactional database promises: a transaction is Atomic, leaves data Consistent, runs Isolated from other transactions, and is Durable once committed. MVCC (multi-version concurrency control) is one of the main techniques databases use to keep the isolation promise without making every reader wait for every writer. PostgreSQL uses it for all tables.

The easiest way to understand both is with the classic example: moving money between two bank accounts. This guide goes through ACID one letter at a time using that example, shows what PostgreSQL does internally for each guarantee, and then explains how MVCC lets a report read an account while a transfer is updating it. Examples use PostgreSQL 16.

The bank transfer example

Two accounts, and a rule that no balance may go negative:

CREATE TABLE accounts (
    id      text PRIMARY KEY,
    balance numeric(12,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts VALUES ('A', 1000.00), ('B', 200.00);

A transfer of 300 from A to B is two statements that must behave as one:

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 'A';
UPDATE accounts SET balance = balance + 300 WHERE id = 'B';
COMMIT;

Everything that can go wrong with this transfer maps to one of the four ACID letters.

Atomicity: all or nothing

If the server crashes, the connection drops, or the second UPDATE fails, A must not lose 300 while B gains nothing. Either both changes happen or neither does.

Try a transfer that breaks the rule:

BEGIN;
UPDATE accounts SET balance = balance - 1500 WHERE id = 'A';
-- ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
UPDATE accounts SET balance = balance + 1500 WHERE id = 'B';
-- ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;

After the first error, PostgreSQL refuses to run anything else in the transaction. You can only roll back. That's deliberate: continuing after a partial failure is exactly how half-finished transfers happen. If you need to recover from an expected error inside a transaction, use a SAVEPOINT and ROLLBACK TO SAVEPOINT.

How PostgreSQL does it. PostgreSQL doesn't undo changes by rewriting rows. Each transaction has an ID, and its final status (in progress, committed or aborted) is recorded in the commit log under pg_xact/. Rows written by an aborted transaction stay in the table, but every reader checks the status and treats them as if they never existed. Vacuum removes them later. Rolling back is therefore cheap, whether the transaction changed one row or a million.

Consistency: the rules still hold afterwards

Consistency means a transaction moves the database from one valid state to another. "Valid" is defined by rules. Some of those rules you declare in the database, and some exist only in your application.

The database enforces what you declare:

• CHECK (balance >= 0) rejects an overdraft, as shown above.
• PRIMARY KEY and UNIQUE reject duplicates.
• FOREIGN KEY rejects a transfer record that points at an account that doesn't exist.
• NOT NULL rejects missing values.

Other invariants are your responsibility. The database doesn't know that the total money across A and B should be the same before and after a transfer. If a bug subtracts 300 from A and adds 30 to B, every constraint passes and the data is wrong. The more business rules you express as constraints, the fewer of these bugs can reach the data. That's why the C in ACID is the one letter that depends as much on schema design as on the database engine.

Isolation: concurrent transactions don't interfere

Real systems run many transfers at once. Isolation means each transaction behaves as if it had the database to itself, up to a level you choose. The SQL standard defines four levels, and PostgreSQL implements three of them distinctly:

Isolation is where MVCC does its work, covered below. Which anomalies each level allows, including lost updates and write skew, is covered in transaction isolation levels and write skew. A common application bug is also worth naming here: reading a balance into application code, subtracting there, and writing the result back. Two concurrent requests can both read 1000 and both write 700. Doing the arithmetic in SQL (balance = balance - 300) or locking the row avoids it, as described in preventing race conditions in payment systems.

Durability: committed means committed

Once COMMIT returns, the transfer must survive a power cut, a kernel panic or a restart.

How PostgreSQL does it. Before COMMIT returns, the transaction's changes are written to the write-ahead log (WAL) and flushed to disk with fsync. The modified data pages can stay in memory and be written later. After a crash, PostgreSQL replays the WAL from the last checkpoint and rebuilds any change that hadn't reached the data files. The processes involved, the WAL writer and checkpointer, are described in PostgreSQL architecture.

Two settings control this:

• fsync = on: never turn it off on a database whose data you care about. With it off, a crash can corrupt the whole cluster, not just lose recent transactions.
• synchronous_commit = on (the default): commit waits for the WAL flush. Setting it to off makes commits faster, and a crash can lose the last few hundred milliseconds of commits (up to three times wal_writer_delay) but can't corrupt data. That's a reasonable trade for data like page-view events, not for money:

BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO page_views (path, viewed_at) VALUES ('/pricing', now());
COMMIT;

Durability on one machine doesn't survive losing the disk. For that you need replicas, and synchronous replication if a commit must be on two machines before it returns.

How MVCC works in PostgreSQL

With a plain locking approach, a transaction that updates a row locks it, and anyone who wants to read the row waits. MVCC takes a different approach: keep several versions of the row, and show each transaction the version it's entitled to see.

In PostgreSQL every row version (tuple) carries two hidden fields:

• xmin: the ID of the transaction that created this version.
• xmax: the ID of the transaction that deleted or replaced it, or 0 if none has.

An UPDATE never changes a row in place. It marks the current version's xmax and inserts a new version with a new xmin. A DELETE only sets xmax. Where those versions sit inside the table's 8KB pages is covered in PostgreSQL storage internals.

Each query runs with a snapshot: a record of which transactions had committed when it was taken.

SELECT pg_current_snapshot();
-- 1049:1052:1051

This reads as follows: every transaction below 1049 has finished, transactions from 1052 onward hadn't started yet, and 1051 was still running. A row version is visible if its xmin committed before the snapshot and its xmax is empty, aborted, or not yet committed in that snapshot. In Read Committed, each statement takes a fresh snapshot. In Repeatable Read and Serializable, the transaction keeps its first one.

Why readers don't block writers

Start again from the original balances (A = 1000, B = 200). Run the transfer in session A, leave it uncommitted, and read from session B:

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 'A';
SELECT pg_current_xact_id();   -- 1051

-- Session B
SELECT ctid, xmin, xmax, balance FROM accounts WHERE id = 'A';
--  ctid  | xmin | xmax | balance
-- (0,1)  | 1049 | 1051 | 1000.00

Session B returns immediately. It sees the old version: xmax is 1051, but 1051 hasn't committed, so the version is still live for B. B doesn't wait for A, and A doesn't wait for B. Now commit A and repeat the query in B:

--  ctid  | xmin | xmax | balance
-- (0,3)  | 1051 |    0 |  700.00

B now sees the new version, stored at a different position in the page. The old version at (0,1) is dead and waits for vacuum.

Writers still block writers on the same row. If B runs UPDATE accounts SET balance = balance + 50 WHERE id = 'A' while A is uncommitted, B waits. When A commits, B (in Read Committed) re-reads the latest version and applies its change to 700, giving 750. In Repeatable Read, B would instead fail with "could not serialize access due to concurrent update" and should retry.

Compare this with a pure locking design, where a long report holds shared locks and blocks every transfer that touches the rows it read.

The cost of MVCC: dead tuples and vacuum

Old row versions don't disappear on their own. PostgreSQL's answer is VACUUM, normally run by autovacuum, which marks dead versions' space as reusable once no running transaction can still see them.

Three practical consequences:

1. Table bloat. Frequent updates create dead tuples. If autovacuum falls behind, tables and indexes grow and scans slow down.
2. Long transactions block cleanup. A transaction open for an hour holds a snapshot that may need hour-old versions, so vacuum can't remove anything newer than that across the whole database. Sessions left idle in transaction by an application bug are the usual cause.
3. Transaction ID wraparound. Transaction IDs are 32-bit counters. Vacuum "freezes" old rows so the IDs can be reused safely. If vacuum is blocked for too long, PostgreSQL eventually stops accepting writes to protect the data.

Check for both problems:

-- tables with the most dead tuples
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- oldest open transactions
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

A safety net for forgotten transactions:

ALTER ROLE app SET idle_in_transaction_session_timeout = '5min';

PostgreSQL also reduces update overhead with HOT (heap-only tuple) updates. When no indexed column changes and the new version fits on the same page, indexes don't need a new entry. Leaving free space in pages with a lower fillfactor makes HOT updates more likely on update-heavy tables.

Other databases implement MVCC differently. MySQL's InnoDB and Oracle update rows in place and keep old versions in undo logs, which moves the cleanup cost elsewhere but doesn't remove it.

FAQ

What does ACID stand for in databases?

Atomicity, Consistency, Isolation and Durability: all-or-nothing transactions, rules that hold before and after, concurrent transactions that don't interfere, and committed data that survives crashes.

Does MVCC mean there are no locks in PostgreSQL?

No. Reads don't take locks that block writes, but two transactions updating the same row still conflict, and the second waits. DDL such as ALTER TABLE also takes locks.

Is PostgreSQL fully ACID compliant?

Yes, with the default settings. Turning off fsync breaks durability and can corrupt data. Turning off synchronous_commit can lose recent commits after a crash but keeps data consistent.

Why do I need VACUUM if PostgreSQL deletes rows?

A DELETE or UPDATE only marks the old version as dead, because other transactions may still need to see it. Vacuum reclaims that space later, when no snapshot can see it anymore.

What is the default isolation level in PostgreSQL?

Read Committed. Each statement sees data committed before it began. You can raise it per transaction with BEGIN ISOLATION LEVEL REPEATABLE READ or SERIALIZABLE.

ACID and MVCC checklist

• Put every multi-step change that must succeed or fail together in one transaction.
• Encode business rules as constraints wherever you can: CHECK, UNIQUE, FOREIGN KEY, NOT NULL.
• Do arithmetic in SQL or lock the row. Don't read, modify in application code, and write back.
• Leave fsync on, and relax synchronous_commit only for data you can afford to lose.
• Keep transactions short, set idle_in_transaction_session_timeout, and watch dead tuples.

If you're designing a system where these guarantees carry real money, Vectorkub builds and reviews that kind of backend.