week 10
Teaching and Learning Methods to be Employed
1. Illustrated lecture on transactions, ACID properties, and isolation levels
2. Concurrency problem demonstration with parallel transaction scenarios
3. Recovery mechanisms and log-based recovery exercise
4. Final course review and exam preparation session
Learning Outcomes / Objectives
By the end of this week, students should be able to:
1. Define a transaction and explain the ACID properties.
2. Describe the concurrency problems that arise when transactions execute simultaneously.
3. Explain lock-based and timestamp-based concurrency control protocols.
4. Describe transaction isolation levels and their trade-offs.
5. Explain the concept of database failure and recovery.
6. Describe log-based recovery and the ARIES algorithm concepts.
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)