Transactions — The Columns Not in the Table Are Most of the Job
In a nutshell
The textbook table of isolation levels covers only three anomalies, but most concurrency bugs in practice are a fourth phenomenon that is not in that table: write skew.
Why this was needed
When you meet a concurrency bug, a prescription often comes up: "raise the isolation level to repeatable read." And in quite a few cases that prescription does not fix the problem. Balances go negative, stock is oversold, and duplicate reservations keep appearing.
There are two reasons. First, the definitions learned from the table differ from the actual implementations. Second, there is one anomaly that none of the four levels catches.
How it works
Starting with ACID: atomicity is the property that everything is applied or everything is canceled, consistency is the property of keeping the constraints, isolation is the property that concurrent execution looks like sequential execution, and durability is the property that committed results do not disappear. Durability is usually guaranteed with a write-ahead log (WAL). Before modifying a data page, the change is first recorded in the log and the log is flushed to disk. After a crash, recovery replays the log.
The isolation levels and anomalies defined by the standard are as follows.
| Isolation level | dirty read | non-repeatable read | phantom read | write skew |
|---|---|---|---|---|
| Read Uncommitted | Allowed by the standard | Allowed | Allowed | Allowed |
| Read Committed | Not possible | Allowed | Allowed | Allowed |
| Repeatable Read | Not possible | Not possible | Allowed by the standard | Allowed |
| Serializable | Not possible | Not possible | Not possible | Not possible |
The rightmost column is the key. Every level except Serializable allows write skew, and most services run at Read Committed.
The differences in implementation are also worth knowing. PostgreSQL implements isolation with MVCC, so even if you request Read Uncommitted, it behaves as Read Committed. The syntax is accepted, but a dirty read is structurally impossible. This is why the proposal to "lower the isolation level to raise performance" has no effect at all in PostgreSQL. Conversely, PostgreSQL's Repeatable Read is snapshot isolation, so it is stronger than the standard and phantoms are not visible either. The price is that a transaction dies with an error on a write conflict. When you raise the isolation level, the application takes on the responsibility to retry. Raising only the level without retry logic is just a change that shows errors to users more often.
What it looks like in the field
A classic example of write skew is the on-call doctor problem. The rule is "at least one person must remain on call". Two doctors open transactions at the same time, each reads count(*) = 2 and passes the condition, and then each updates a different row. The writes do not overlap, so snapshot isolation detects no conflict at all. Both succeed, and the number of people on call becomes 0.
Bugs of the same structure recur. Checking stock and then deducting it, checking for duplicate seat reservations, and checking for duplicate sign-ups without a unique constraint are all write skew. Raising the isolation level by one step fixes none of them.
There are three options. If the rows to lock are clear, pessimistic locking such as SELECT ... FOR UPDATE is the simplest and most predictable. If conflicts are rare and user interaction sits in the middle, optimistic locking with a version column is better. If the invariant spans several rows or several tables and you cannot specify what to lock, Serializable is the answer.
And where possible, moving an invariant that you were protecting with isolation levels into a constraint is the most robust. A unique index or exclusion constraint always holds regardless of the isolation level, needs no retry, and cannot be bypassed by mistake by a team member who joins later.
Where to put the retry
If you have decided to raise the isolation level, retry is not optional but its partner. But if you put the retry anywhere, the result is worse than the error. You only need to keep three things.
Put only database work inside the retry block. If you sent a payment request or sent an email inside the transaction, that request does not come back even if the transaction is rolled back. The same thing happens again in the second attempt, and the customer is charged twice. Take external calls out of the transaction, or at least defer them until after the commit is finished.
Put an upper bound on the count and the interval. Serialization failures occur more often the heavier the contention, so if you immediately resubmit the failed transaction, contention gets worse. Rest briefly and try again, and after a few failures give up and notify the user.
Distinguish what caused the failure by SQLSTATE. A serialization failure is 40001 and a deadlock is 40P01. If you branch on the wording of error messages, it breaks silently on an upgrade. These two codes are worth retrying, but errors that give the same result even when repeated, such as constraint violations, are not retry targets.
What you will do in the next lab
You will run two sessions overlapping on a real PostgreSQL. You will first create a lost update and block it with a row lock and with repeatable read respectively, and then reproduce, with an on-call roster, the write skew that even repeatable read does not block. Finally, you will catch it with the serializable level and use skip locked to split and process a work queue.