Recap: Why Concurrency Control?
- Concurrency control ensures correct execution of transactions in multi-user databases.
- Problems without concurrency control:
- Lost Updates: Two transactions modify the same value at the same time.
- Dirty Reads: A transaction reads uncommitted changes from another.
- Non-Repeatable Reads: A transaction sees different values for the same data.
- Phantom Reads: A transaction reads a set of rows that change due to another transaction.
Solution: Use locking mechanisms to synchronize access to shared data.
Lock Types
Basic Lock Modes
| Lock Type |
Description |
| Shared (S-LOCK) |
Allows multiple transactions to read a data item. |
| Exclusive (X-LOCK) |
Grants exclusive access to a data item (needed for writes). |
Locks vs. Latches
| Feature |
Locks |
Latches |
| Scope |
Used by transactions |
Used by threads |
| Protects |
Database contents |
In-memory data structures |
| Held Until |
End of transaction |
Short-lived (critical section) |
| Modes |
Shared, Exclusive |
Read, Write |
| Deadlock Handling |
Detection & Resolution |
Avoidance (spinlocks) |
Transactions request locks before accessing objects to ensure correctness.
Two-Phase Locking (2PL)
What is 2PL?
- A concurrency control protocol that guarantees serializability.
- Transactions follow two phases:
- Growing Phase
- A transaction acquires all the locks it needs.
- Shrinking Phase
- A transaction releases locks and cannot acquire new ones.
2PL ensures transactions execute in a serializable order.
2PL Execution Example