iTranslated by AI
Explaining the Relationship Between Shared/Exclusive Locks, Two-Phase Locking, and Transaction Isolation Levels in RDB with Sequences
Overview
In RDBMS (Relational Database Management Systems), it is necessary to guarantee the 'I' (Isolation) property, which is one of the ACID properties.
In other words, you need to design systems such that no inconsistencies occur when transactions are executed concurrently.
Key concepts regarding concurrency control for transactions include:
- Shared Locks / Exclusive Locks
- Two-Phase Locking (2PL)
- Transaction Isolation Levels
Since these are representative terms, many articles cover them, but few explain the relationship between these three.
This article aims to explain and analyze the relationship between Shared/Exclusive Locks, Two-Phase Locking, and Transaction Isolation Levels.
Conclusion
To state the conclusion immediately:
- Transaction Isolation Levels are "conceptual definitions of the levels to isolate transactions" supported by SQL. In other words, how to achieve them depends on the DBMS implementation.
- Shared/Exclusive Locks and Two-Phase Locking are concrete locking techniques.
- Transaction Isolation Levels can be achieved using Shared/Exclusive Locks and Two-Phase Locking.
- Two-Phase Locking is a protocol that defines "how to acquire" Shared/Exclusive Locks.
We will now look at more specific relationships.
Glossary
Before diving into the main subject, I will provide brief descriptions as a review.
For detailed explanations, there are many articles and literature available that serve as good references.
| Term | Description |
|---|---|
| Shared Lock | A read-only lock. Other transactions can acquire shared locks but cannot acquire exclusive locks. |
| Exclusive Lock | A read/write lock. Other transactions cannot acquire either shared or exclusive locks. |
| Two-Phase Locking | A method to acquire and release locks in two phases. (Phase 1) Acquire locks. (Phase 2) After acquiring all necessary locks (after phase 1 ends), release the locks. |
| Transaction Isolation Level | Literally, the level to isolate transactions. There are the following (1) to (4): |
| (1)SERIALIZABLE | The strongest level. Guarantees that even if transactions are executed in parallel, the results are the same as serial execution. Phantom reads do not occur. |
| (2)REPEATABLE READ | The next strongest level. Non-repeatable reads do not occur. |
| (3)READ COMMITTED | The third level. Only reads committed information (dirty reads do not occur). |
| (4)READ UNCOMMITTED | A state where no restrictions are imposed. |
Main Subject
From here, I will explain the relationship between the four transaction isolation levels and each locking method.
For clarity, I will explain starting from the lowest isolation level.
- (4) READ UNCOMMITTED
- (3) READ COMMITTED and Shared/Exclusive Locks
- (2) REPEATABLE READ and Two-Phase Locking
- (1) SERIALIZABLE and Serial Execution
(4) READ UNCOMMITTED
READ UNCOMMITTED imposes no restrictions.
In other words, simply not applying any locks is sufficient (in practice, exclusive locks are acquired; see reference).
Various undesirable things can happen, but in particular, dirty reads occur.
Here is a simple example:
- Initial balance of A's account is 500 yen
- A wants to deposit 100 yen
- B wants to send 100 yen
As a result, an inconsistency occurred.
(Since the balance increased, it might be 'happy' for A.)
(3) READ COMMITTED and Shared/Exclusive Locks
Specifying READ COMMITTED prevents dirty reads.
This can be achieved by applying the following types of locks:
- Apply a shared lock (it can be released immediately after reading is finished).
- Apply an exclusive lock according to Two-Phase Locking.
Let's consider the same situation as the READ UNCOMMITTED example:
- Initial balance of A's account is 500 yen
- A wants to deposit 100 yen
- B wants to send 100 yen
This time, consistency is maintained.
(2) REPEATABLE READ and Two-Phase Locking
Specifying REPEATABLE READ prevents non-repeatable reads.
This can be achieved by applying Two-Phase Locking (for both shared and exclusive locks).
Let's consider a non-repeatable read in a different case.
First, a case where a non-repeatable read occurs with READ COMMITTED:
- Initial balance of A's account is 500 yen
- A wants to withdraw 300 yen
- A credit card company wants to debit 300 yen
That was a bit lucky, so it's 'happy', right? (?)
Next, consider the case where a non-repeatable read does not occur with REPEATABLE READ (Two-Phase Locking):
- Initial balance of A's account is 500 yen
- A wants to withdraw 300 yen
- A credit card company wants to debit 300 yen
As a result, a deadlock occurred (it might not have been a very good example, but a non-repeatable read was prevented).
After this, one of the transactions will roll back, and a consistent state will be achieved. Note that which transaction rolls back depends on the deadlock resolution method.
Another Workaround
Occurring deadlocks is not very good (or rather, it's not a clean example).
I will discuss ways to acquire exclusive locks during reads in a separate article.
(1) SERIALIZABLE and Serial Execution
SERIALIZABLE guarantees behavior identical to serial execution.
This can be achieved by acquiring locks at the table level.
(Supplementary note)
Up to the previous section, I considered locking at the row level (Account A in the Bank Account table).
In SERIALIZABLE, even for reading/writing Account A, a lock is applied to the entire Bank Account table.
In particular, phantom reads stop occurring.
Phantom reads occur due to row insertion/deletion, but this stops occurring by locking the entire table.
(I felt this was relatively easy to understand, so the sequence is omitted.)
Summary
I explained the relationship between locking methods and transaction isolation levels.
Writing the details in sequences deepened my personal understanding as well.
Discussion