Isolation levels and anomalies
The anomalies between concurrent transactions (dirty, non-repeatable and phantom reads, lost update) and the four levels of the SQL standard: what each one prevents and what it costs.
- 25 cards
- 5 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- On the timeline
- Scenarios
A few cards from the deck
Dirty read: what is it?
Show the answer
Reading a value written by another transaction that has not committed yet: if it rolls back, you read a value that never existed.
Only READ UNCOMMITTED allows it, according to the standard. At every higher level a read only sees committed data.
On PostgreSQL, at READ COMMITTED, the balance is 100. T1 sets it to 50 without committing. T2 then reads the balance. What does T2 see?
100500- An error: the row is being modified
Show the answer
100READ COMMITTED forbids dirty reads: T2 only sees the last committed value, 100, without waiting for T1. If T1 rolls back, T2 will have read nothing false.
The SQL standard defines four levels, from the most permissive to the strictest: READ UNCOMMITTED, …, REPEATABLE READ and ….
Show the answer
The SQL standard defines four levels, from the most permissive to the strictest: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE.
SERIALIZABLE guarantees a result identical to running the transactions one after another. READ COMMITTED is the default level of many databases, PostgreSQL among them.
Non-repeatable read: what is it?
Show the answer
The same row, read again within the same transaction, has changed value because another transaction committed a change between the two reads.
It concerns a row already read that gets modified. It remains possible in READ COMMITTED and disappears from REPEATABLE READ on.
At READ COMMITTED, a report reads customer 42's limit at the start, then reads that same row again at the end of the same transaction and gets two values: an adviser changed that limit and committed in between. What is the lowest level, according to the standard, that fixes it?
- READ UNCOMMITTED
- SERIALIZABLE
- REPEATABLE READ
- READ COMMITTED with a SAVEPOINT between the two reads
Show the answer
REPEATABLE READ
A row already read that changes: that is a non-repeatable read, which REPEATABLE READ forbids. SERIALIZABLE fixes it too, but costs more. READ UNCOMMITTED is even more permissive, and a SAVEPOINT freezes no read.