How Database Transactions Work: ACID, Isolation Levels and MVCC
What BEGIN and COMMIT really promise, and how PostgreSQL and MySQL keep concurrent users from corrupting your data

Two customers click "Buy" on the last concert ticket in the same millisecond. A bank moves $100 between accounts and the server loses power halfway through. In both cases, the thing standing between you and a very bad day is the database transaction.
Most developers type BEGIN and COMMIT without thinking much about what happens in between. This guide explains how database transactions work: what the four ACID guarantees really promise, how isolation levels trade safety for speed, how multiversion concurrency control (MVCC) lets readers and writers stay out of each other's way, and why your code sometimes needs to retry. Examples use PostgreSQL and MySQL, the two most common open-source engines.
What a transaction is
A transaction is a group of statements that the database treats as one unit of work. The classic example, straight from the PostgreSQL manual, is a transfer between two accounts:
BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT; -- or ROLLBACK; to undo both
If anything goes wrong before COMMIT, a ROLLBACK (explicit, or automatic after an error or crash) erases every change in the group. Many drivers run in autocommit mode by default, which means each statement is quietly wrapped in its own one-line transaction.
The four ACID guarantees
Database people summarize what a transaction promises with the acronym ACID. MySQL's manual describes the model as a set of reliability principles meant to keep business data from being corrupted by crashes or hardware faults.
- Atomicity: all or nothing. The statements in a transaction succeed together or fail together.
- Consistency: a transaction moves the database from one valid state to another, so constraints like foreign keys, unique keys and checks are never left violated.
- Isolation: concurrent transactions don't see each other's half-finished work. How strictly this is enforced is tunable, which is the subject of most of this article.
- Durability: once
COMMITreturns, the change survives a crash or power cut.
How durability actually works: the write-ahead log
Writing every changed table and index page to disk on each commit would be painfully slow. Instead, databases use a write-ahead log (WAL). PostgreSQL's documentation explains the rule: a change to the data files may only be written after a log record describing it has been flushed to permanent storage. After a crash, the server replays the log to redo anything the data files missed.
Because the log is appended sequentially, syncing it is far cheaper than flushing scattered data pages, and one disk sync can commit many small transactions at once. MySQL's InnoDB applies the same idea with its redo log, and exposes a setting, innodb_flush_log_at_trx_commit, that lets you trade some durability for throughput.
Isolation levels: the knob most people never touch
Perfect isolation would mean running transactions one at a time. That's safe but slow, so the SQL standard defines four isolation levels in terms of which concurrency glitches, called phenomena, each one must prevent:
- Dirty read: you see data another transaction wrote but hasn't committed yet.
- Non-repeatable read: you read the same row twice and get different values because someone committed a change in between.
- Phantom read: you rerun a query and new rows have appeared (or vanished) that match your condition.
- Serialization anomaly: the final result of several committed transactions doesn't match any order of running them one after another.
| Isolation level | Dirty read | Non-repeatable read | Phantom read | Serialization anomaly |
|---|---|---|---|---|
| Read Uncommitted | Allowed (not in PostgreSQL) | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Allowed (not in PostgreSQL) | Possible |
| Serializable | Prevented | Prevented | Prevented | Prevented |
The standard only sets minimum protections, so engines are free to do better. PostgreSQL treats a Read Uncommitted request as Read Committed, and its Repeatable Read also blocks phantoms.
Defaults differ, and that matters
PostgreSQL's default is Read Committed: each statement sees a fresh snapshot of data committed before that statement began, so two SELECTs in one transaction can return different answers. MySQL's InnoDB defaults to Repeatable Read: every plain read in a transaction uses the snapshot taken at the first read. The same application code can therefore behave differently when you move between the two. You can change the level per transaction:
-- PostgreSQL and MySQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
MVCC: how readers and writers avoid blocking
Older databases enforced isolation mostly with locks, so a long report could stall every writer touching the same rows. Modern engines use multiversion concurrency control. Rather than overwriting a row in place, an update creates a new version and keeps the old one around for a while. Each transaction reads from a snapshot: the set of versions that were committed when its snapshot was taken.
The payoff, in the PostgreSQL manual's words, is that reading never blocks writing
, and the reverse is also true. A long analytics query keeps reading its consistent snapshot while new orders keep flowing in.
- Transaction A takes a snapshot and reads a row at version 1.
- Transaction B updates the row, creating version 2, and commits.
- A keeps seeing version 1 (at Repeatable Read) because version 2 isn't in its snapshot.
- Once no running transaction can still see version 1, it becomes dead and can be cleaned up.
The cleanup bill
Old versions don't disappear on their own. In PostgreSQL, an UPDATE or DELETE leaves the previous row version behind until VACUUM reclaims the space, and the built-in autovacuum daemon normally does this in the background. InnoDB keeps old versions in undo logs and purges them in a similar way. It's the same idea as garbage collection in programming languages: versions that nobody can reach anymore get freed.
Serializable isolation and the retry loop
Snapshot-based Repeatable Read still allows a subtle bug called write skew. PostgreSQL's docs give an example: two transactions each sum one group of rows and insert the total into the other group. Neither overwrites the other's rows, so both commit, yet no one-at-a-time order could produce that result.
PostgreSQL's Serializable level catches this with Serializable Snapshot Isolation (SSI). It tracks read/write dependencies using non-blocking predicate locks and, when it spots a dangerous pattern, rolls one transaction back with SQLSTATE 40001. MySQL takes a more traditional approach: at Serializable, InnoDB turns plain reads inside a transaction into shared locking reads.
The practical consequence is the same everywhere: at Repeatable Read or Serializable, your application must be ready to retry the whole transaction.
import psycopg
def transfer(conn, src, dst, amount, attempts=5):
for _ in range(attempts):
try:
with conn.transaction():
conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
conn.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, src))
conn.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, dst))
return
except psycopg.errors.SerializationFailure:
continue # rerun everything from the start
raise RuntimeError("transfer failed after retries")
Add a short, growing delay between attempts on busy systems, the same backoff habit that applies when an API answers with a 429, as covered in our guide to API rate limiting.
Practical tips for everyday code
- Keep transactions short. Do slow work such as HTTP calls or file uploads before
BEGINor afterCOMMIT. - Know your default. Don't assume PostgreSQL and MySQL behave alike.
- Use Read Committed for simple, single-row updates like the transfer above, and step up when you enforce business rules across many rows.
- Mark read-only work with
READ ONLYwhere your engine supports it, which helps PostgreSQL's SSI avoid extra tracking. - Index the rows you lock or update. Narrow lookups mean fewer rows touched and fewer conflicts; see how database indexes work.
Key takeaways
- A transaction groups statements into one all-or-nothing unit of work.
- ACID means atomicity, consistency, isolation and durability; durability comes from a write-ahead log.
- Isolation levels decide which concurrency glitches you can see; PostgreSQL defaults to Read Committed, InnoDB to Repeatable Read.
- MVCC keeps multiple row versions so readers and writers rarely block each other, at the cost of background cleanup.
- At stricter levels, serialization failures are normal: retry the whole transaction.
Frequently asked questions
Which isolation level should I use?
Start with your database's default and move to Serializable for logic that checks a condition across several rows before writing, such as booking limits or inventory rules. Make sure your code can retry.
Is Repeatable Read the same in PostgreSQL and MySQL?
No. Both read from a stable snapshot, but PostgreSQL rolls back an update that conflicts with a concurrent commit, while InnoDB's locking reads and writes work against the latest committed data using gap and next-key locks.
Does autocommit mean I am not using transactions?
You still are. In autocommit mode each statement runs as its own transaction and commits immediately, so multi-statement work needs an explicit BEGIN and COMMIT.
Why does my database grow even though I delete rows?
Under MVCC, deleted and updated rows leave old versions behind until cleanup runs. In PostgreSQL, standard VACUUM makes that space reusable but usually doesn't shrink the file.
Sources
- PostgreSQL documentation: Transaction Isolation
- PostgreSQL documentation: Concurrency Control introduction (MVCC)
- PostgreSQL documentation: Write-Ahead Logging
- PostgreSQL documentation: Routine Vacuuming
- MySQL 8.4 Reference Manual: Transaction Isolation Levels
- MySQL 8.4 Reference Manual: InnoDB and the ACID Model











Comments
No comments yet — be the first to share your thoughts.
Join the discussion