Part 124: Concurrency Anomalies: Dirty, Non-Repeatable & Phantom Reads
When multiple transactions read and write the same rows simultaneously, data anomalies occur.
1. The Three Classical Anomalies
- Dirty Read: Transaction A modifies a row but hasn't committed. Transaction B reads the uncommitted value. Transaction A then rolls back. Transaction B acted on data that never officially existed!
- Non-Repeatable Read (Fuzzy Read): Transaction A reads a row. Transaction B updates that row and commits. Transaction A reads the row again and gets a different value!
- Phantom Read: Transaction A queries a range of rows (
WHERE salary > 50000). Transaction B inserts a new row matching that filter and commits. Transaction A runs the query again and sees a "phantom" new row!
Key Takeaways
- Concurrency anomalies are mitigated by tuning Transaction Isolation Levels.
In Part 125, we examine ANSI SQL Isolation Levels: Read Uncommitted & Read Committed!