Concurrency Control Techniques Chapter 18

1. Concurrency Control TechniquesChapter 18 Dr. Muhammad Shafique Concurrency Control Techniques

2. Outline • Granularity of data items • Concurrency control techniques • Locking techniques • Timestamp ordering techniques • Using locks for concurrency control in indexes • Other concurrency control issues • Summary Concurrency Control Techniques

3. Concurrency Control Techniques • Categories of concurrency techniques • Locking techniques • Timestamp ordering techniques • Multi-version concurrency control techniques • Optimistic concurrency control techniques • Advanced concurrency control techniques Concurrency Control Techniques

4. Granularity of Data Items • Data item granularity Size or granule of data for concurrency control. It could be • An attribute value • A database record • A disk block • A whole file • The whole database • Data item granularity and degree of concurrency • Fine granularity --- refers to small item sizes • Coarse granularity --- refers to large item sizes • Larger the data item size, lower the degree of concurrency • Data item size depends on the types of transactions Concurrency Control Techniques

5. Concurrency Control Using Locks • Lock • A variable associated with a data item • Describes status of the data item with respect to operations that can be performed on it • Types of locks • Binary locks • Locked/unlocked • Enforces mutual exclusion • Multiple-mode locks: • Each data item can be in one of three lock states • Read lock or shared lock • Write lock or exclusive lock • Unlock • Lock table managed by lock manager subsystem of DBMS Concurrency Control Techniques

6. Lock and Unlock Operations for Binary Locks Concurrency Control Techniques

7. Locking Rules • T must issue read_lock(X) or write_lock(X) before any read_item(X) operation is performed in T • T must issue write_lock(X) before any write_item(X) operation is performed in T • T must issue unlock(X) after all read_item(X) and write_item(X) operations are completed in T • T will not issue a read_lock(X) if it already holds a read lock or write lock on X (may be relaxed) • T will not issue a write_lock(X) if it already holds a read lock or write lock on X (may be relaxed) • T will not issue unlock (X) request unless it holds a read lock write lock on X Concurrency Control Techniques

8. Conversion of Locks • A transaction T that holds a lock on item X, under certain conditions, is allowed to convert the lock from one state to another • Upgrade Convert read_lock to write_lock • Downgrade Convert write_lock to read_lock • Using locks does not guarantee serializability of schedules on its own Concurrency Control Techniques

9. Two Transactions T1 read_lock(Y); read_item(Y); unlock(Y); write_lock(X); read_item(X); X:=X+Y; write_item(X); unlock(X); T2 read_lock(X); read_item(X); unlock(X); write_lock(Y); read_item(Y); Y:=X+Y; write_item(Y); unlock(Y); Let’s assume serial schedule S1: T1,T2 Initial values: X=20, Y=30  Result: X=50, Y=80 Concurrency Control Techniques

10. Locks Alone Don’t Insure Serializability Let’s run T1 and T2 in interleaved fashion Schedule S T1 read_lock(Y); read_item(Y); unlock(Y); write_lock(X); read_item(X); X:=X+Y; write_item(X); unlock(X); T2 read_lock(X); read_item(X); unlock(X); write_lock(Y); read_item(Y); Y:=X+Y; write_item(Y); unlock(Y); unlocked too early! Non-serializable! Result: X=50, Y=50 Concurrency Control Techniques

11. Two-Phase Locking (2PL) Protocol • Transaction is said to follow the two-phase-locking protocol if all locking operations precede the first unlock operation • Expanding (growing) phase • Shrinking phase • During the shrinking phase no new locks can be acquired • Downgrading ok • Upgrading is not • Example Concurrency Control Techniques

12. 2PL Example T1’ read_lock(Y); read_item(Y); write_lock(X); unlock(Y); read_item(X); X:=X+Y; write_item(X); unlock(X); T2’ read_lock(X); read_item(X); write_lock(Y); unlock(X); read_item(Y); Y:=X+Y; write_item(Y); unlock(Y); • Both T1’ and T2’ follow the 2PL protocol • Any schedule including T1’ and T2’ is guaranteed to be serializable • Limits the amount of concurrency Concurrency Control Techniques

13. Locking Techniques for Concurrency Control • Quick review of ideas discussed so far • Locks and system lock tables • Lock table and lock manager • Binary locks --- lock & unlock operations (critical sections) • Shared/exclusive locks --- read-lock, write-lock, and unlock • Conversion of locks • Upgrade the lock • Downgrade the lock • Concurrency control subsystem generates read-lock and write-lock requests on behalf of the transactions • Using locks in transactions does not guarantee serializability of schedules on its own Concurrency Control Techniques

14. Locking Techniques for Concurrency Control (Cont.) • Two-phase locking protocol (2PL)All lock operations precede the first unlock operation • Expanding phase and shrinking phase • Upgrading of locks must be done in expanding phase and downgrading of locks must be done in shrinking phase • If every transaction in a schedule follows 2PL protocol then the schedules is guaranteed to be serializable. • Variants of 2PLBasic, conservative, strict, and rigorous Concurrency Control Techniques

15. Locking Techniques for Concurrency Control(Cont.) • Basic 2PLAll lock operations before the first unlock operation • Conservative 2PL or static 2PL Lock all the items it accesses before the transaction begins execution. • Deadlock free protocol • Read-set and write-set of the transaction should be known • Strict 2PL No exclusive lock will be unlocked until the transaction commits or aborts • Strict 2PL guarantees strict schedules • Rigorous 2PL No lock will be unlocked until the transaction commits or aborts Concurrency Control Techniques

16. Dealing with Deadlocks and Starvation in 2PL • 2PL can produce deadlocks • Deadlock and starvation in 2PL Deadlock occurs when each transaction T in a set of two or more transactions is waiting for some item that is locked by some other transaction T’ in the set. • Example (next slide) Concurrency Control Techniques

17. Dealing with Deadlocks and Starvation in 2PL Concurrency Control Techniques

18. Dealing with Deadlocks and Starvation in 2PL • Deadlock prevention protocols • Conservative 2PL, lock all needed items in advance • Ordering all items in the database • Possible actions if a transaction is involved in a possible deadlock situation • Block and wait • Abort and restart • Preempt and abort another transaction Concurrency Control Techniques

19. Dealing with Deadlocks and Starvation in 2PL • Two schemes that prevent deadlock (Timestamp based) • Wait-die An older transaction is allowed to wait on a younger transaction whereas a younger transaction requesting an item from held by an older transaction is aborted and restarted with the same timestamp • Wound-wait A younger transaction is allowed to wait on an older transaction whereas an older transaction requesting an item from held by a younger transaction preempts the younger transaction by aborting it. Concurrency Control Techniques

20. Dealing with Deadlocks and Starvation in 2PL • Two schemes that prevent deadlock and do not require timestamps • No waiting Abort immediately and restart after a certain time delay • Cautious waiting • If a transaction holding the lock is not waiting for another item to be locked then allow T to wait otherwise abort and restart T Concurrency Control Techniques

21. Dealing with Deadlocks and Starvation in 2PL • Deadlock detection and timeouts • Construct and maintain a wait-for graph • Victim selection • Timeouts • StarvationA transaction cannot proceed for an infinite period of time while other transactions in the system continue normally • Unfair waiting scheme • Victim selection Concurrency Control Techniques

22. Concurrency Control Based on Timestamp Ordering • Timestamp --- a unique identifier created by the DBMS to identify a transaction • read-TS(X) • The largest timestamp among all the timestamps of transactions that have successfully read item X • write-TS(X) • The largest timestamp among all the timestamps of transactions that have successfully written item X • Timestamp Ordering (TO) algorithms • Basic timestamp ordering • Strict timestamp ordering • Thomas’s write rule Concurrency Control Techniques

23. Concurrency Control Based on Timestamp Ordering(Cont.) • Basic timestamp ordering algorithmOrder transactions based on their timestamps • T issues a write(X) • If read-TS(X) > TS(T) or if write-TS(X) > TS(T) the abort and rollback T and reject the operation. • If condition above does not occur then execute the operation and set write-TS(X) = TS(T) • T issues a read(X) • If write-TS(X) > TS(T) then abort and rollback T and reject the operation. • If write-TS(X)  TS(T) then execute the operation and set read-TS(X) = max ( read-TS(X), TS(T) ) • The schedules produced by basic TO are guaranteed to be conflict serializable • No deadlocks but cyclic restart are possible (hence starvation) Concurrency Control Techniques

24. Concurrency Control Based on Timestamp Ordering(Cont.) • Strict Timestamp Ordering (strict TO) • A transaction T that issues a read-item(X) or write-item(X) such that TS(T) > write-TS(X) has its read or write operation delayed until the transaction T’ that wrote the value of X (hence TS(T’ ) = write-TS(X)) has committed or aborted. • No deadlocks, since T waits for T’ only if TS(T) > TS(T’) • Strict TO ensures that the schedules are both strict (for easy recoverability) and (conflict) serializable Concurrency Control Techniques

25. Concurrency Control Based on Timestamp Ordering (Cont.) • Thomas’s write rule • It rejects fewer write operations, by modifying the basic TO checks for the write-item(X) operation as follows: • If read-TS(X) > TS(T), then abort and roll back T and reject the operation. • If write-TS(X) > TS(T), then do not execute the write operation but continue processing. [This is because some transaction with timestamp greater than TS(T)—and hence after T in the timestamp ordering—has already written the value of X. Hence, we must ignore the write_item(X) operation of T because it is already outdated and obsolete. Notice that any conflict arising from this situation would be detected by case (1).] • If neither the condition in part (1) nor the condition in part (2) occurs, then execute the write-item(X) operation of T and set write-TS(X) to TS(T). • Thomas’ write rule does not enforce conflict serializability Concurrency Control Techniques

26. Using Locks for Concurrency Control in Indexes • Two phase locking can be applied to indexes. Here the nodes of an index correspond to disk pages. • Tree structure of the index can be taken advantage of • A conservative approach for insertions can be to lock the root node in exclusive mode. • Index locking using B-link tree rather than B+ tree • In B-link tree, sibling nodes at the same level are linked together at every level Concurrency Control Techniques

27. Other Concurrency Control Issues • Insertion, deletion, and phantom records • Insertion operation • New data item inserted in the database • Deletion operation • Phantom record • Use index locking • Interactive transactions • No rollback • Delay output of transactions until they have committed • Latches • Locks held for a short duration are called latches • Latches are used to , for example, write a buffer to a page Concurrency Control Techniques

28. Summary • Granularity of data items • Concurrency control techniques • Locking techniques • Timestamp ordering techniques • Using locks for concurrency control in indexes • Other concurrency control issues Thank you Concurrency Control Techniques