What are dirty reads, non-repeatable reads, and phantom reads?
Answer
Dirty read: Transaction A reads data that Transaction B has modified but not yet committed. If B rolls back, A read invalid data. Prevented by Read Committed+. Non-repeatable read: Transaction A reads a row, Transaction B updates and commits it, then A reads the same row again and gets a different value. The same query returns different results within a transaction. Prevented by Repeatable Read+. Phantom read: Transaction A queries rows matching a condition, Transaction B inserts a new matching row and commits, then A re-runs the query and sees a new "phantom" row. Prevented only at Serializable level. Each requires progressively higher isolation to prevent.
Previous
What are isolation levels in database transactions?
Next
What is query optimization and what is a query execution plan?
More Database Design / Normalization Questions
View all →- Intermediate What are database anomalies and how does normalization prevent them?
- Intermediate What is denormalization and when is it used?
- Intermediate What are isolation levels in database transactions?
- Intermediate What is query optimization and what is a query execution plan?
- Intermediate What is a covering index?