week 10
10.4 Concurrency Control Protocols
10.4.1 Lock-Based Protocols
Locking is the most common mechanism for concurrency control. Before accessing a data item, a transaction must acquire a lock on it.
|
Lock Type |
Description |
|
Shared Lock (S-lock / Read Lock) |
Multiple transactions can hold a shared lock on the same item simultaneously. Used for READ operations. Any transaction can read, but none can write while a shared lock exists. |
|
Exclusive Lock (X-lock / Write Lock) |
Only ONE transaction can hold an exclusive lock on an item. Used for WRITE operations. No other transaction can read or write the item. |
TWO-PHASE LOCKING (2PL) PROTOCOL:
1. Phase 1 (Growing Phase): A transaction may only ACQUIRE locks. It cannot release any locks.
2. Phase 2 (Shrinking Phase): A transaction may only RELEASE locks. It cannot acquire any new locks.
2PL guarantees serialisability (concurrent execution is equivalent to some serial order), but can cause DEADLOCKS.
DEADLOCK: Two transactions each waiting for a lock held by the other. E.g., T1 holds lock on Account_A and wants Account_B; T2 holds lock on Account_B and wants Account_A — circular wait. DBMS resolves deadlock by aborting (rolling back) one transaction.
10.4.2 Timestamp-Based Protocols
Each transaction is assigned a unique timestamp when it begins. The DBMS uses timestamps to order transactions and ensure serialisability without locks — eliminating deadlocks.
3. Each data item has two timestamps: Read Timestamp (W-TS) and Write Timestamp (R-TS)
4. If a younger transaction tries to write data that an older transaction has already read, the younger transaction is rolled back (Thomas Write Rule)