Database Transactions
A transaction is a way for an application to group several reads and writes together into a logical unit: either the entire transaction succeeds (commit) or it fails (rollback). If the transaction fails, the application can safely retry.
Transaction guarantees (ACID properties) are promoted by database vendors as an essential requirements for “serious applications” with “valuable data”. Among these guarantees, Isolation property needs a detailed explanation.
Defining isolation levels by phenomena (or anomalies) are intended to allow non-lock-based implementations of the SQL standard. In other words, database should spare the developer from worrying about explicit locking and focus on his application logic.
Dirty Write: One client overwrites data that another client has written, but not yet committed. Almost all transaction implementations prevent dirty writes.
Dirty Read: One client reads another client’s writes before they have been committed. The read committed isolation level and stronger levels prevent dirty reads.
Fuzzy Read: A transaction reads objects that match some search condition. Another client modifies or delete some records that affects the results of that search. Snapshot isolation prevents fuzzy reads.
Phantom Read: A transaction reads objects that match some search condition. Another client makes a write that affects the results of that search. Snapshot isolation prevents phantom reads.
Lost Updates: Two clients concurrently perform a read-modify-write cycle. One overwrites the other’s write without incorporating its changes, so data is lost. Some implementations of snapshot isolation prevent this anomaly automatically, while others require a manual lock ( SELECT FOR UPDATE ).
Read Skew: A client sees different parts of the database at different points in time. This issue is most commonly prevented with snapshot isolation, which allows a transaction to read from a consistent snapshot at one point in time.
Write skew: A transaction reads something, makes a decision based on the value it saw, and writes the decision to the database. However, by the time the write is made, the premise of the decision is no longer true. Only serializable isolation prevents this anomaly.
Weak isolation levels protect against some of these anomalies. Application developer has to handle others manually (e.g., using explicit locking).
We will be using the following entities for detailing all the phenomena (or anomalies)
References — For further reads
- Designing Data-Intensive Applications
- A Critique of ANSI SQL Isolation Levels
- A beginner’s guide to Read and Write Skew phenomena
Hope this style of writing helps folks who read or watch on the go 🚌 🚇 If you like, please show your support by sharing the blog 😆
