Featured image of post Transaction Isolation Levels Explained: PostgreSQL vs MySQL, Measured

Transaction Isolation Levels Explained: PostgreSQL vs MySQL, Measured

A payment system moves 100 from one account to another: a subtraction from one row and an addition to another. While those two writes happen, other transactions are working on the same accounts: a balance check, a second transfer, a monthly statement being generated. Some of them will look at the accounts after the subtraction and before the addition. What should they see, and how would you make a database that handles thousands of these at once get it right?

TL;DR
A transaction isolation level is the setting that decides how much one transaction, a group of statements the database runs as one unit, can be affected by others running at the same moment. This post covers the four levels PostgreSQL and MySQL share, the five ways transactions can interfere with each other (dirty reads, non-repeatable reads, phantoms, lost updates and write skew), which of those each level stops on each engine, and what the safe choices cost under load. Every number comes from runs on PostgreSQL 18.6 and MySQL 8.4.13.

The transfer is two statements, UPDATE accounts SET balance = balance - 100 WHERE id = 'A' and then the same with + 100 for B, and between them the database is in a state that should never be visible: A has given the money and B has not received it. The question is what everyone else sees during that gap. A balance check that reads A and B at that moment finds 100 missing from the world. A second transfer that reads A’s balance sees the subtraction and may decide on it, and if the first transfer then fails and rolls back, it decided on money that was never gone. Without a mechanism in the way, this is exactly what happens: every statement reads whatever is in the row right now, finished or not. Seeing a change another transaction has not yet committed is called a dirty read, and it is the first of several anomalies, each one arising from a different arrangement of who reads and writes what, and when. Stopping one of them is never free. The database stops it by making a transaction wait for another to finish, by showing it an older state of the rows, or by refusing it outright, and each of those takes something away from how many transactions get through per second. So the choice is a trade-off between speed and consistency, and the setting that makes it is the transaction isolation level. There are four of them, READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE, each stopping more anomalies and paying more for it than the one below. What each one stops, on each engine, and what it costs, is what the measurements below show.

A quick reminder about transactions

Moving 100 from one account to another takes two changes, one balance down and another up. If the server stops between them, or a second transfer squeezes in between them, money is created or lost, and guarding every such sequence by hand in application code is hard to get right. So the database takes the job on. You open a transaction with BEGIN, run the statements, and end it with COMMIT to keep every change or ROLLBACK to throw every change away. A statement sent without BEGIN is a transaction of its own.

The promise has a well-known name, ACID, and both PostgreSQL and InnoDB, MySQL’s default storage engine, make it for every transaction:

GuaranteeWhat it promises
Atomicall of the transaction’s changes happen, or none of them do
Consistentevery rule the schema declares (keys, constraints) holds when the transaction ends
Isolateda transaction is not disturbed by other transactions running at the same moment
Durableonce committed, the changes survive a crash

This post is about the third row, the only one of the four that comes with a setting: how undisturbed a transaction gets is something the application picks. The pick matters because of how many transactions there are. Every program talking to the database (an application server, a background job, someone typing into a terminal) holds a connection, which both engines call a session; PostgreSQL allows 100 of them by default and MySQL 151, busy systems run thousands, and at any moment many of those sessions are inside a transaction on the same tables. Both engines keep them out of each other’s way with MVCC, multi-version concurrency control: a change keeps the row’s old version around, and each transaction reads from a snapshot, a record of which transactions had committed when it looked, so it sees the versions that were current at that moment. Where the old versions live and what keeping them costs is what the MVCC posts for PostgreSQL and MySQL measure. The isolation level decides how a transaction uses its snapshot.

What an isolation level controls

Both engines read from snapshots, a record of which transactions had committed at the moment the snapshot was taken, so an isolation level comes down to three things: when a snapshot is taken and how long it is kept, what a write does when the row has changed since that snapshot, and whether a plain read takes a lock. Each level answers those for one more anomaly than the level below it.

READ UNCOMMITTED deals with nothing. A plain read returns the newest version of the row whether or not the transaction that wrote it has committed, which is the dirty read the opener described. MySQL offers this; PostgreSQL accepts the name and runs READ COMMITTED instead.

READ COMMITTED deals with dirty reads, which happen when a statement reads a version written by a transaction that has not finished yet. A snapshot is taken at the start of every statement and kept for that statement only, so a statement sees everything committed before it started and nothing that is still in flight. Because the next statement gets a fresh snapshot, two reads of the same row inside one transaction can disagree, and a repeated range query can return rows that were not there the first time. A write waits for any other writer of the row to finish, then applies its change to the newest committed version, on both engines. A plain read takes no lock.

REPEATABLE READ deals with the disagreement above, called a non-repeatable read, which happens when another transaction commits a change to a row between two reads of it in the same transaction. A snapshot is taken at the transaction’s first read and kept for the duration of the transaction, so every plain read inside sees the database as it stood at that moment, including which rows existed. Writes are where the engines split, and that split is the main finding of this post: PostgreSQL finds the row through the snapshot and refuses to change it if someone else changed it after the snapshot was taken; MySQL applies the write to the newest committed version whatever the snapshot says. A plain read still takes no lock.

SERIALIZABLE deals with everything a snapshot alone cannot, which is the case where two transactions each read something the other is about to change, both pass their checks, and both commit. The snapshot is the same one-per-transaction snapshot as REPEATABLE READ. The difference is that reads now count. MySQL turns every plain SELECT into SELECT ... FOR SHARE, a read that takes a shared lock on each row it reads, so a writer has to wait for the reader to finish. PostgreSQL blocks nobody. It records which rows each transaction read and refuses a transaction at commit when the reads and writes of the transactions running together could not have come out of any one-at-a-time order.

A write locks the row it changes, on both engines and at every level, and any other transaction that wants to change that row waits until the first one commits or rolls back. That is the one rule none of the four levels changes.

Here is how the four level names map onto those decisions, as the two manuals describe them:

LevelPostgreSQL 18MySQL 8.4 (InnoDB)
READ UNCOMMITTEDbehaves as READ COMMITTEDplain reads can see changes not yet committed
READ COMMITTEDnew snapshot per statement; a write waits, then applies to the newest committed versionsame as PostgreSQL
REPEATABLE READone snapshot for the transaction; a write to a row changed after it is refusedone snapshot for plain reads; writes go to the newest committed version
SERIALIZABLEREPEATABLE READ, plus a record of what each transaction read, refused when no one-at-a-time order fitsplain reads become FOR SHARE: they lock what they read and see the newest committed version

The names come from the SQL standard, which defines each level by what it must prevent and leaves everything else to the implementation; PostgreSQL’s documentation describes the standard as one “which only describes the minimum protections each isolation level must provide.” An isolation level is therefore a floor: the least protection a transaction gets from other transactions’ work, with each engine free to give more. That is how REPEATABLE READ can mean the two different things in its row of the table above.

The standard’s list of things to prevent is also short. It names three, and all three are about reading. A 1995 paper by Berenson, Bernstein, Gray and others, A Critique of ANSI SQL Isolation Levels, showed those three were not enough to tell the levels apart and named more, including the lost update. These five cover what goes wrong in practice, and each one got its own two-session test:

AnomalyWhat goes wrongThe test
Dirty readA transaction sees another’s change before it commits, and that change may still be rolled backB subtracts 50 from account 1 and rolls back; A reads in between
Non-repeatable readReading the same row twice gives two values, because someone committed a change in betweenA reads account 2 twice; B adds 100 between the reads
PhantomRunning the same search twice finds a row that was not there the first timeA counts balances over 9,990; B inserts one
Lost updateTwo transactions read a value, each writes a new one based on it, and the second wipes out the firsttwo deposits into account 3
Write skewTwo transactions each check a rule and make a change that is fine alone, but together the changes break the ruletwo withdrawals from a pair of accounts

The first three are in the standard. The last two are not, and they are the ones that lose money, because they write something based on what was read.

Matrix of five anomalies by four isolation levels on each engine. Dirty reads happened only in MySQL READ UNCOMMITTED. Lost updates happened at every MySQL level except SERIALIZABLE, but PostgreSQL refused them from REPEATABLE READ up. Write skew happened everywhere except SERIALIZABLE on both. MySQL REPEATABLE READ let a phantom row through an UPDATE.

Five anomalies, tested on both engines

Every cell in that matrix comes from a scripted pair of sessions, A and B, against an accounts table of 1,000,000 rows on each engine, in the same Docker containers as the MVCC posts (default settings, 4 CPUs and 4 GB each). The script runs one step at a time; when a step has to wait for the other session’s lock, it notes that, moves on, and collects the step’s result when the wait ends. Each engine’s default level carries an asterisk.

Reading other people’s changes

The three reading anomalies, each run as a two-session test at every level on both engines.

Dirty read: session B subtracted 50 from account 1 without committing, and session A read the balance. On MySQL at READ UNCOMMITTED, A saw 50.00, a balance that never existed once B rolled back. Everywhere else A saw 100.00, PostgreSQL’s READ UNCOMMITTED included; in the documentation’s words, “PostgreSQL’s Read Uncommitted mode behaves like Read Committed.” There is no setting that gets a dirty read out of PostgreSQL, however hard the interview question tries.

Non-repeatable read: session A read account 2 (100.00), session B added 100 and committed, and A read it again:

LevelA’s second read, PostgreSQLA’s second read, MySQL
READ COMMITTED200.00200.00
REPEATABLE READ100.00100.00
SERIALIZABLE100.00100.00, and B’s update waited until A finished

The one difference is in the last row. Both engines returned 100.00 at SERIALIZABLE, but MySQL got there by making B wait: A’s read had locked account 2.

Phantom: session A counted accounts with a balance over 9,990 (there were none), session B inserted one with 9,999 and committed, and A counted again, then updated every row matching the same condition, then counted a third time:

1
2
3
4
5
6
-- MySQL 8.4.13, REPEATABLE READ
A> SELECT count(*) FROM accounts WHERE balance > 9990       ->  0
B> INSERT INTO accounts VALUES (2000001, 'new', 9999.00, ...)
A> SELECT count(*) FROM accounts WHERE balance > 9990       ->  0
A> UPDATE accounts SET note = 'flagged' WHERE balance > 9990
A> SELECT count(*) FROM accounts WHERE balance > 9990       ->  1

A’s reads followed its snapshot and saw nothing new. Its UPDATE went to the newest committed rows, found B’s, changed it, and from then on A could see it. The MySQL manual says so plainly: “The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements.” On PostgreSQL at REPEATABLE READ the UPDATE followed the snapshot too, changed nothing, and the third count was still 0. At READ COMMITTED both engines showed the new row on the second count.

In this test MySQL’s write rule only flagged one extra row. Once a write depends on a value read a moment earlier, it costs money.

Two deposits, one lost

The deposit test is the pattern most application code uses: read a value, work out a new one, write it back. With one user it is harmless. It goes wrong when two sessions do it to the same row at once.

In the test, account 3 holds 100. Session A deposits 100 and session B deposits 50. Each one reads the balance, adds its deposit to what it read, and writes the result, so A writes 200 and B writes 150. B’s write has to wait for A’s lock, and the moment that matters is when A commits and B’s update wakes up:

Sequence diagram: sessions A and B both read balance 100 from account 3. A updates it to 200 and holds the lock; B’s update to 150 waits. A commits. At READ COMMITTED on both engines and at MySQL REPEATABLE READ, B’s write goes through and the balance is 150, losing A’s deposit. At PostgreSQL REPEATABLE READ and SERIALIZABLE on both engines, B is refused and the balance stays 200.

LevelPostgreSQL 18.6MySQL 8.4.13
READ COMMITTEDB wrote 150, A’s deposit lostB wrote 150, A’s deposit lost
REPEATABLE READB refused: could not serialize access due to concurrent update, balance 200B wrote 150, A’s deposit lost
SERIALIZABLEB refused, same error, balance 200B refused with a deadlock error, balance 200

At READ COMMITTED both engines did exactly what B asked. The lock made B wait its turn, but waiting does not change what B writes, and B had already decided on 150 from a balance that was out of date by the time it got the row.

PostgreSQL’s REPEATABLE READ refused B because the row had changed since B’s snapshot. Its documentation says an application that gets this error “should abort the current transaction and retry the whole transaction from the beginning”, and a retry would read 200 and write 250. MySQL’s REPEATABLE READ wrote 150 over A’s committed 200:

Writes in MySQL read the newest version
In InnoDB the write rule covers UPDATE, DELETE and the locking reads SELECT ... FOR UPDATE and FOR SHARE: all of them work on the newest committed version of the row, whatever the transaction’s snapshot says. So B’s plain read saw 100, its update landed on A’s 200, and nothing compared the two.

SERIALIZABLE kept A’s deposit on both engines. PostgreSQL refused B with the same error as at REPEATABLE READ. On MySQL, A’s and B’s reads had each taken a shared lock on account 3, so each one’s update waited for the other’s. That is a deadlock, two transactions each waiting for a lock the other holds, and InnoDB breaks it by rolling one of them back. Either way the application gets an error and has to try again, which is what not losing the deposit costs.

The quick objection is that the test is rigged: it writes a balance the application worked out (SET balance = 200), where careful code writes SET balance = balance + 100 and lets the database do the arithmetic. For this test that is right. With the relative form the arithmetic happens on the newest committed version, the one that already holds A’s deposit, and neither engine lost the deposit at any level, although PostgreSQL’s REPEATABLE READ and SERIALIZABLE can still refuse the second write and make it retry, as the load test below shows. It stops being enough once the code checks something before it writes, which is what the next test and the load test both do.

Two checks that both pass

Write skew is the one anomaly no level below SERIALIZABLE stopped on either engine, and the reason is that the two transactions never touch the same row, so there is no lock to wait on and no changed row to refuse. A customer has two accounts holding 100 each, and the rule is that the combined balance must not go below zero. Each session checks the total (200), decides that withdrawing 150 is fine, and withdraws it, A from one account and B from the other:

1
2
3
4
5
6
7
8
-- PostgreSQL 18.6, REPEATABLE READ
A> SELECT sum(balance) FROM accounts WHERE owner = 'pair'   ->  200.00
B> SELECT sum(balance) FROM accounts WHERE owner = 'pair'   ->  200.00
A> UPDATE accounts SET balance = balance - 150 WHERE id = 1900001
B> UPDATE accounts SET balance = balance - 150 WHERE id = 1900002
A> COMMIT
B> COMMIT
A> SELECT sum(balance) FROM accounts WHERE owner = 'pair'   ->  -100.00

Both checks passed and the total ended at -100. At SERIALIZABLE, PostgreSQL refused B at commit with could not serialize access due to read/write dependencies among transactions. On MySQL each SELECT sum(...) had locked the rows it read, each UPDATE waited on the other session’s lock, and InnoDB rolled one back as a deadlock. Both ended at a total of 50, one withdrawal done and one refused.

Here the engines agreed at every level. The lost update is where they disagreed, and it fails two different ways for the same code moved between engines.

The same level name, different promises
An application written against PostgreSQL’s REPEATABLE READ, counting on it to refuse lost updates, silently loses data when moved to MySQL with the same setting. The reverse move fails loudly instead: code that never expected could not serialize access starts throwing errors on PostgreSQL. If the code depends on the database refusing a conflict, test that on the engine it actually runs on.

The same mistake under load

The relative update, SET balance = balance - amount, is the usual answer to lost updates, and the load test used it on every transfer. The defaults still overdrew accounts hundreds of times in 20 seconds.

I ran 16 sessions moving money between 100 accounts for 20 seconds, three times per setting. Each transfer reads the source balance, skips the transfer if the balance is too low, and otherwise subtracts the amount from one account and adds it to the other, both as relative updates. A transfer that left the source account below zero counts as an overdraft: two transfers from the same account both read, say, 60, both decide 50 is affordable, and both subtract. No money vanishes (the total across the 100 accounts stayed at 100,000 in every run), but the rule the check was there to enforce is broken. Sessions that got a serialization error or a deadlock rolled back and went on to the next transfer.

Two bar charts, median of three runs. Overdrafts in 20 seconds: PostgreSQL READ COMMITTED 262, MySQL READ COMMITTED 448, MySQL REPEATABLE READ 492, and zero for every other setting. Transactions refused and retried: PostgreSQL REPEATABLE READ 11,033 and SERIALIZABLE 10,124, MySQL SERIALIZABLE 906, and under 20 for the rest.

SettingOverdrafts (3 runs)Refused and retried (median)Transfers per second (median)
PostgreSQL READ COMMITTED267, 262, 225131,292
PostgreSQL REPEATABLE READ0, 0, 011,0331,267
PostgreSQL SERIALIZABLE0, 0, 010,1241,182
PostgreSQL READ COMMITTED with FOR UPDATE0, 0, 0161,147
MySQL READ COMMITTED354, 448, 536151,973
MySQL REPEATABLE READ457, 514, 492152,209
MySQL SERIALIZABLE0, 0, 09061,700
MySQL READ COMMITTED with FOR UPDATE0, 0, 0132,012

The pattern from the deposit test held at scale, including a part I did not expect: MySQL’s REPEATABLE READ overdrew about as often as its READ COMMITTED did, so on this pattern MySQL’s default bought nothing over the weaker level. In hindsight the mechanics predict it. The balance check was each transfer’s first read, so the snapshot was taken at the same moment at both levels, and the subtraction went to the newest committed balance either way.

Each setting that prevented overdrafts paid for it differently: PostgreSQL’s two upper levels by refusing around 10,000 transactions per run for the application to retry, MySQL’s SERIALIZABLE by 906 deadlocks, and FOR UPDATE by almost nothing, because the sessions queued for the row instead of failing.

Throughput is the weaker signal. The fastest and slowest runs of the same setting differed by as much as 45 percent (PostgreSQL with FOR UPDATE: 984 against 1,425 transfers per second), so most of the gaps in the last column sit inside that noise. The one consistent gap was MySQL’s SERIALIZABLE: every one of its runs was slower than every run of the other MySQL settings, and its median was 14 to 23 percent below theirs. This is also a deliberately crowded test (16 sessions fighting over 100 accounts), and with less contention the retry counts would fall.

Choosing a level

Everything above, in one table:

QuestionPostgreSQL 18.6MySQL 8.4.13Source
Default levelREAD COMMITTEDREPEATABLE READdocumented
Can a transaction read another’s uncommitted change?Never, even at READ UNCOMMITTEDOnly at READ UNCOMMITTEDmeasured
Two simultaneous deposits, default levelOne deposit lost, no errorOne deposit lost, no errormeasured
Two simultaneous deposits, REPEATABLE READSecond one refused with an error to retryOne deposit lost, no errormeasured
Two withdrawals that each pass a balance checkStopped only at SERIALIZABLEStopped only at SERIALIZABLEmeasured
Overdrafts in 20 s of crowded transfers, default level (median of 3 runs)262492measured
What stopping them cost in that test (median)10,124 to 11,033 retries at SERIALIZABLE or REPEATABLE READ906 deadlock retries at SERIALIZABLE, 14 to 23% lower throughputmeasured
FOR UPDATE at READ COMMITTED in that test0 overdrafts, 16 retries0 overdrafts, 13 retriesmeasured

Both defaults leave a read followed by a write based on it unprotected. Wherever the application does that (balances, stock counts, seat bookings), two shapes held up in these tests: lock the row you checked with SELECT ... FOR UPDATE, or run that one transaction at SERIALIZABLE with a retry loop around it. A third avoids the separate check altogether by putting the condition inside the write, as in SET stock = stock - 1 WHERE stock > 0, and then reading how many rows it changed; I did not load-test that one. On PostgreSQL, REPEATABLE READ with a retry on could not serialize access also catches the lost update. On MySQL it does not.

A check that spans several rows, like the pair of accounts, needs every row it reads locked, not only the one it writes. I did not run that variant; SERIALIZABLE is the setting that was tested against it.

Whatever the level, keep transactions short. A REPEATABLE READ transaction left open holds back cleanup for the whole server, which the MVCC posts for PostgreSQL and MySQL measure.

Count the errors as well as the money. After a switch to REPEATABLE READ or SERIALIZABLE, the number to watch is serialization failures and deadlocks per second, because each one is a transaction the application retried or dropped.

SELECT insights FROM experience WHERE downtime = 0; -- Ruslan Tolkachev