1. Intro
When dealing with atomic operations in databases, it’s essential to use transactions effectively. However, simply processing transactions in a First-In-First-Out (FIFO) order can lead to serious logical inconsistencies.
Imagine a scenario where Transaction A interrupts and updates data that Transaction B has already read and is relying on. Or consider a case where data is deleted in the middle of a transaction, leading to unexpected results. These are just a few examples of the logical contradictions that can arise when transactions aren’t managed properly.
Fortunately, modern Database Management Systems (DBMS) offer a solution: Transaction Isolation Levels. By setting an appropriate isolation level, we can control how transactions interact with each other, thus preventing these contradictions and ensuring data consistency and reliability.
2. Read Uncommit
| Transaction A | Transaction B |
|------------------------------|------------------------------------|
| BEGIN TRANSACTION A; | |
| SELECT balance FROM accounts;| |
| -> Reads 1000 | |
| | BEGIN TRANSACTION B; |
| | UPDATE accounts SET balance = 800; |
| | (Not committed) |
| SELECT balance FROM accounts;| |
| -> Reads 800 (Uncommitted) | |
| | ROLLBACK TRANSACTION B; |
| | (Reverts to 1000) |Let’s just start with an example. See the diagram above.
Initially, Transaction A reads the balance as 1000. However, Transaction B then updates the balance to 800, which is uncommitted. Transaction A subsequently reads this uncommitted value of 800, demonstrating how Uncommitted Read allows for Dirty Reads. This level of isolation is not typically considered reliable because it can lead to data inconsistencies. In this example, even though Transaction B rolls back its changes, Transaction A has already read the incorrect value, 800, which does not reflect the true state of the database.
3. Read Committed
| Transaction A | Transaction B |
|------------------------------|-----------------------------------|
| BEGIN TRANSACTION A; | |
| SELECT balance FROM accounts;| |
| -> Reads 1000 | |
| | BEGIN TRANSACTION B; |
| | UPDATE accounts SET balance = 800;|
| | (Not committed yet) |
| SELECT balance FROM accounts;| |
| -> Reads 1000 | |
| | (Still reads the old value) |
| | COMMIT TRANSACTION B; |
| SELECT balance FROM accounts;| |
| -> Reads 800 (Committed) | |
With the Read Committed isolation level, a transaction only reads values that have been committed by other transactions. As shown in the diagram above, Transaction A reads the value 1000 in its second query, since Transaction B’s changes are not yet committed. Transaction A then reads the updated value 800 only after Transaction B has committed its changes.
4. Repeatable Read
| Transaction A | Transaction B |
|-----------------------------------|---------------------------|
| BEGIN TRANSACTION A; | |
| SELECT balance FROM accounts | |
| WHERE id = 1; | |
| -> Reads 1000 | |
| | BEGIN TRANSACTION B; |
| | UPDATE accounts |
| | SET balance = 800; |
| | COMMIT TRANSACTION B; |
| SELECT balance FROM accounts | |
| WHERE id = 1; | |
| -> Reads 1000 | |
| (Repeatable Read) | |Transaction A continues to read the value 1000, even after Transaction B has committed its change. This is because Transaction A takes a snapshot of the initial data, ensuring that the values remain unchanged throughout the transaction, even if read multiple times.
This behavior is facilitated by MVCC (Multi-Version Concurrency Control), which maintains multiple versions of data. When Transaction B commits its changes, the original value that Transaction A read is stored in the Undo area, allowing Transaction A to see a consistent snapshot of the data as it was at the start of the transaction.
The values in the Undo area are only ReadOnly.
| Transaction A | Transaction B |
|--------------------------------|------------------------------------|
| BEGIN TRANSACTION A; | |
| SELECT * FROM accounts | |
| WHERE balance > 1200; | |
| -> Reads (2, 1500) | |
| | BEGIN TRANSACTION B; |
| | INSERT INTO accounts (id, balance) |
| | VALUES (3, 1300); |
| | COMMIT TRANSACTION B; |
| SELECT * FROM accounts | |
| WHERE balance > 1200; | |
| -> Reads (2, 1500), (3, 1300) | |
| (Phantom Read) | |
When a transaction queries data using a range condition, any new records inserted within that range by another transaction can be included in subsequent queries. This occurs because the newly inserted records are not considered modified records by the initial transaction; instead, they are seen as additions that meet the query’s criteria.
This is called Phantom Read.
| Transaction A | Transaction B |
|------------------------------|----------------------------------|
| BEGIN TRANSACTION A; | |
| SELECT balance FROM accounts | |
| WHERE id = 1; | |
| -> Reads 1000 | |
| | BEGIN TRANSACTION B; |
| | SELECT balance FROM accounts |
| | WHERE id = 1; |
| | -> Reads 1000 |
| | |
| | UPDATE accounts SET balance = 900|
| | WHERE id = 1; |
| | COMMIT TRANSACTION B; |
| UPDATE accounts | |
| SET balance = 1100 | |
| WHERE id = 1; | |
| COMMIT TRANSACTION A; | |
| -> Error: Write Conflict | |
When Transaction B commits a change to a record that was initially read by Transaction A, the original version of the record is moved to the Undo area for Transaction A’s reference. Since records in the Undo area are read-only, Transaction A cannot update this value, ensuring that the data seen by Transaction A remains consistent with its initial read, but preventing any modifications to outdated versions.
5. Serializable
| Transaction A | Transaction B |
|----------------------------- |--------------------------------------|
| BEGIN TRANSACTION A; | |
| SELECT balance FROM accounts | |
| WHERE id = 1; | |
| -> Reads 1000 | |
| | BEGIN TRANSACTION B; |
| | UPDATE accounts |
| | SET balance = 800 WHERE id = 1; |
| | -> Blocked (Waiting for A to commit) |
| UPDATE accounts | |
| SET balance = 1100 | |
| WHERE id = 1; | |
| COMMIT TRANSACTION A; | |
| | -> Resumes after A commits |
| | UPDATE accounts |
| | SET balance = 800 WHERE id = 1; |
| | COMMIT TRANSACTION B; |
Serializable Isolation Level guarantees that transactions are completely isolated from each other, effectively running as if they were executed in a strict sequence. This prevents any form of interference between transactions, such as dirty reads, non-repeatable reads, and phantom reads, by ensuring that once a transaction accesses data, no other transaction can modify or insert data until the first transaction completes. This strict isolation can, however, lead to reduced concurrency and potential performance bottlenecks, making it most suitable for scenarios where data consistency is of the utmost importance, such as in financial applications like banking systems.
6. Conclusion
- Read Uncommitted allows transactions to see uncommitted changes from others, leading to potential dirty reads and data inconsistencies.
- Read Committed prevents dirty reads by only showing committed data, but it can still result in non-repeatable reads as data can change between queries within a transaction.
- Repeatable Read maintains the consistency of data seen within a transaction, preventing non-repeatable reads but allowing phantom reads, where new records can appear in subsequent range queries.
- Serializable offers the highest level of isolation, preventing all anomalies by making transactions appear sequential, though this can lead to decreased concurrency and performance.
Leave a Reply