Understanding Database Isolation Levels
The default isolation level in many databases allows race conditions. Do you know what yours is set to?
The Phenomena#
- Dirty Read: Reading uncommitted data from another transaction.
- Non-Repeatable Read: Reading the same row twice gets different data (someone updated it).
- Phantom Read: Running the same conceptual query gets different rows (someone inserted a new row).
The Levels#
| Level | Dirty Read | Non-Repeatable | Phantom |
|---|---|---|---|
| Read Uncommitted | Yes | Yes | Yes |
| Read Committed (Postgres Default) | No | Yes | Yes |
| Repeatable Read | No | No | Yes |
| Serializable | No | No | No |
When to use Serializable?#
When correctness is more important than speed. E.g., Preventing double-spending in a banking ledger.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- Your logic
COMMIT;It guarantees that the result is the same as if transactions were executed one after another.