Database transactions are important to ensuring data validity when running queries. They’re often
used to ensure that either all of the queries run, or if one fails the previous changes are rolled
back. Another important aspect is transaction isolation, which has several modes. Each mode (known
as level) provides more guarantees than the last.
Each of these levels handles possible transaction phenomena differently. Here is a table for each level and if the phenomena is possible (✔) or not possible (✖).
| Level |
Nonrepeatable Read |
Phantom Read |
Serializable Anomaly |
| Read Committed Isolation |
✔ |
✔ |
✔ |
| Repeatable Read Isolation |
✖ |
✖ |
✔ |
| Serializable Isolation |
✖ |
✖ |
✖ |
| Phenomena |
Description |
| Nonrepeatable Read |
The same query can return the same row with different values because other transactions have updated them. |
| Phantom Read |
The same query can return different rows because other transactions have created/deleted/updated them. |
| Serialization Anomaly |
The results of concurrently running transactions can be different than if each transaction was run in serial. |
| Level |
Description |
| Read Committed |
This is the default transaction isolation level. With this level transactions will see changes other transactions have made only when those transactions successfully commit. When doing UPDATEs, a transaction holds a row-level lock until COMMIT or ROLLBACK is run. |
| Repeatable Read |
It uses a snapshot of the database from when the transaction starts to prevent nonrepeatable and phantom reads. If a transaction commits and other transactions are using a stale snapshot, they will be rolled back and fail. |
| Serializable |
If transactions COMMIT in a way that would result in a serialization anomaly, the first transaction will be successful and the others will be rolled back and fail. |
Example of Phenomena
Nonrepeatable Read
| Transaction 1 |
Transaction 2 |
Notes |
SELECT balance FROM accounts WHERE id = ... |
|
|
|
UPDATE accounts SET balance = 0 WHERE id = ... |
|
SELECT balance FROM accounts WHERE id = ... |
|
The value of balance is now different because Transaction 2 modified it. |
Example of nonrepeatable read
Phantom Read
| Transaction 1 |
Transaction 2 |
Notes |
SELECT * FROM accounts |
|
|
|
DELETE FROM accounts ... |
|
SELECT * FROM accounts |
|
The SELECT is now missing columns because Transaction 2 removed them. |
Example of phantom read
Serializable Anomaly
| Transaction 1 |
Transaction 2 |
Notes |
SELECT SUM(quantity) FROM orders WHERE product_id = ... |
|
Transaction 1 gets the total quantity of existing orders. |
|
SELECT SUM(quantity) FROM orders WHERE product_id = ... |
Transaction 2 does the same. |
| … |
… |
Both transactions do a check to see if the total quantity of previous orders is less than the stock of the product. |
INSERT INTO orders (...) VALUES (...) |
|
Transaction 1 inserts new order. |
|
INSERT INTO orders (...) VALUES (...) |
Transaction 2 inserts new order. |
| … |
… |
The quantity of orders could now be greater than the product supply. |
Example of serializable anomaly
References