Chapter 22 Concurrency Control Techniques

1. Chapter 22Concurrency Control Techniques Csci455 reza@aero.und.edu

2. Objectives • Concurrency control (CC) techniques in Centralized DB • two phase Locking (2PL) • Timestamp Ordering • Multi-version CC Techniques • Validation (Optimistic) CC Techniques • Deadlock and Livelock detection and prevention techniques • Granularity of Data items

3. T2 Tn T1 Transaction Mgr (TM) Scheduler Keys: • TM: Performs transactions operations • Scheduler: Controls the relative order • RM: responsible for ensuing that the database contains all of the effects of committed transactions • CM: performs retrieve or update operations on DB using Read (x) and write (x) operations Recovery Mgr (RM) Data Mgr(DM) Cache Mgr (CM) DB ch18

4. Two phase locking (2PL) • A protocol based on locking mechanism • Guarantees the very important property of schedule, which is the serializability of schdule

5. Concurrency Control: LOCKS • Lock? • A variable associated with a data item in DB • Describes the status of an item w.r.t. two operations • Read • write • Used as a means to synchronize simultaneousaccess to database items

6. Locks: Types • Two types of Locks • Binary locks • Shared /Exclusive (or read/write) locks • Multi-mode (read, write, certify)

7. Binary Locks • Binary Locks • A Lock having two states (or values) • Lock(X)=1 (i.e., Data-Item is taken) • Lock(X)=0 (i.e., Data-item is available) • Uses two atomic operations to access the items • Lock_item(x) • Unlock_item(x)

8. Lock: Implementation • Lock can be implemented as a record having the following fields • Data_Item (e.g., X) • Lock (1 or 0) • Locking _Transactions (e.g.,Ti, Tj, etc.) • Lock Table • Used to maintain the records of ALL those items that are currently locked • Can be organized very efficiently (Hash Table) • Lock Mgr • Controls access to locks

9. Binary Locks: Rules • Using Binary locks, every transaction must obey the following rules: • A transaction T must FIRST issue lock_item(X) before any access • A transaction T must issue unlock_item(X) after all read_item(X)/write_item(X) • A transaction T will not issue a lock_item(X) operation if it already holds the lock on item X • A transaction T will not issue an unlock_item(X) operation unless it already holds the lock on item X

10. Problem with Binary Lock • Binary locks are too restrictive • At any time, ONLY one Transaction can have a lock • Too much blocking • Limits concurrency

11. Shared /Exclusive Locks • Works with multiple-mode • A LOCK (X) has (at least) three-four states: • Read-locked, • Write-locked • Certify-locked (discussed later) • Unlocked • Provide three operations • Shared-locked (read_locked) • Exclusive-locked (write_locked) • Unlocked

12. Example of lock record • Example of lock record: • Data_Item • Lock • Locking_Transactions • No_of_reads

13. Shared and Exclusive locks (cont.) • Binary lock rules plus the following additional rules: • A transaction T will not issue a read_lock (X) operation if it already holds read or write lock on item X. This rule may be relaxed. • A transaction T will not issue a write_lock(X) if it already holds read or write lock on item X. This rule may be relaxed.

14. Locks Conversion • Upgrade • Initially T issues read_lock(X) and then issues write_lock(X) • Downgrade • Initially T issues write_lock(X) and then issues read_lock(X)

15. Guaranteeing Serializabilty by 2PL • Locking by itself does not guarantee serializability • To guarantee serializabilty, need an additional rule • the positioning of locking and unlocking operations in every transaction • Best known protocol is 2PL

16. Fig.18.3: Transactions that do not obey 2PL • Suppose Initial values are • X=20; Y=30 • S1: • T1; T2; • X=50, Y=80; • S2: • T2;T1; • X=70, Y=50

17. For X=20, and Y=30 • S (non-serializable) • X=50; y=50; • Wrong!

18. 2PL Protocol • Each transaction issues lock and unlock requests in 2 phases: • Growing • A transaction may ONLY obtain locks, but may not release any locks • Shrinking • A transaction may ONLY release locks, but may not obtain any new locks

19. 2PL: Main Properties • Properties • Ensure conflict serializable (GOOD) • Deadlock may occur (BAD) • Implemented by majority of DBMS

20. 2PL: Example • Consider the following two transactions:T1: • read(A);read(B);if(A==0) then B=B+1;write(B) • T2: • read(B);read(A);if(B==0) then A=A+1;write(A) • (a) Add lock and unlock instructions to transaction T1 and T2 so that they observe the two-phase locking (2PL) protocol. ch18

21. Answer • T1: • Read_lock(A) ; • Read(A); • Write_lock(B); • Read (B); • if(A==0) then B=B+1; • Write (B); • unlock(A); • unlock(B); • T2: • Read_lock(B); • read(B); • Write_lock(A); • Read (A); • if(B==0) then A=A+1; • write (A); • unlock(B); • unlock(A);

22. 2 PHASE LOCKING TECHNIQUES • Variations of 2PL • Basic 2PL (B2PL) • Conservative 2PL (C2PL) • Strict 2PL • Rigorous 2PL

23. Conservative 2PL • Conservative 2PL • requires a transaction to lock ALLitems it may need before the transaction begins execution • uses readsets and/or writesets to pre-declare its needed items • Main Properties • deadlock-free (GOOD) • Not Feasible (BAD)

24. Strict 2PL • Strict 2PL (S2PL) • guarantees strict schedules • a transaction T does not release any of its write_locks until after it commits (or aborts) • Rigorous 2PL (R2PL) • T does not release any of its write_locks or read_locks until after it commits (or aborts)

25. Difference between C2PL and S2PL • The main difference between C2PL and S2PL: • the C2PL must lock all its items before it starts (all-or-nothing semantics) • C2PL is deadlock free (GOOD) • C2PL is not feasible (BAD) • S2PL does not unlock any of its items until after it commits or aborts • S2PL is NOT deadlock-free (BAD) • Very popular (GOOD)

26. Variations of 2PL locks locks locks B2PL S2PL C2PL time

27. Dealing with Deadlock and starvation • Deadlock? • An extreme form of blocking in which processing comes to a halt • Happens when one or more transactions is waiting for some item locked by other transactions)

28. Deadlock: Example • Can the execution of these transactions result in a deadlock? • consider the following partial schedule S:T’1: • read-lock(y); • read-item(y); • write-lock(x) • T’2: • read-lock(x); • read-item(x); • write-lock(y)

29. Wait-for-graph

30. Approaches to deal with deadlocks • Two fundamental approaches • Deadlock Prevention Techniques (DPT) • Deadlock Detection Protocols (DDP)

31. Deadlock Prevention Techniques (DPT): BTO • Using Timestamp based protocol such as Basic Time Stamp Ordering (BTO) • TS(T) • a unique identifier assigned to each transaction • TS(T1) <TS (T2) (i.e., T1 is older than T2) • Decision is made to • Abort T1 • Block T1 • Preempt T2

32. Deadlock Prevention Techniques (DPT)using BTO: 2 • Two rules Based on BTO • Wait-Die • Wounded-Wait

33. Wait-Die Policy • Wait-Die (favor younger T) • TS(Ti) < TS (Tj) (i.e., Ti is older than Tj) • If the requesting transaction Ti is older than holding transction TJ then Tiwaits otherwise Tidies and restart it later • Non-preemptive protocol • Only a younger transaction requesting a lock can be aborted

34. Wound-Wait Policy • Wound-wait (favor older T) • TS(Ti)<TS(Tj) • if Ti is the older transaction, then it wounds the younger transaction Tj else Tiwaits and restart it later • It is a preemptive protocol • The younger transaction is being aborted repeatedly • Problem: Livelock (BAD)

35. More on Deadlock Prevention using BTO: 3 Both schemes abort the younger of the two transactions that may be involved in a deadlock Both have been proved to be deadlock free (GOOD)

36. Deadlock Prevention Techniques (DPT) using locks: 1 • Based on Locking • Using C2PL • No waiting: • abort T if T cannot get a required lock • Cautious waiting: • If Tj is not blocked then Ti is blocked Else abort Ti • Reduces the number of endless aborts/restarts • Deadlock free • b(Ti) <b(Tj) (i.e., Ti can wait on Tj when Tj is not blocked)

37. Deadlock Prevention Techniques using Timeout • Timeout technique • if a transaction waits longer than a system defined timeout, then the system aborts it • Simple and low overhead technique (GOOD) • Starvation (BAD) • Happens when a transaction cannot proceed for an indefinite period of time while other transactions can continue • Use a fair policy (e.g., FCFS, or priority)

38. Deadlock Detection techniques (DDT) using Wait-for graph • Use wait-for-graph to detect a deadlock • Victim selection • Which Transaction should be aborted? • Attempts to avoid aborting a heavy loaded Transaction • Works fine if there is a little interference among transactions

39. Performance of lock-based concurrency control (CC):1 • An understanding of locking performance is important to quality of system deign • The following assumptions are made • All transactions require the same number of locks • All data items are accessed with the equal probability • All locks are write locks • S2PL is used (i.e., data items are locked before they are accessed and are released ONLY after commit or abort)

40. Performance of lock-based concurrency control (CC): 2 • number of choices need to be made in designing a good lock-based CC: • Should we use • deadlock prevention? Not practical • deadlock-detection? • if we use deadlock-detection, how often should we check for deadlock? • if we use deadlock-detection and identify a deadlock, which transactionshould be aborted?

41. CC based on Timestamp-Ordering (TO) Protocol • Order transactions based on their timestamps • DBMS assigns a unique timestamp to each transaction • TS(T) • TS = timestamp • T = Transaction • Timestamps TS can be created by • Counter • Current Date/time • Ensure conflict serializabilty • Ordering transactions using TS is equals to serialization order • Ti.Oi conflict Tj.Oj, (i.e., if Operation of Ti occurs before Operation of Tj ) • Deadlock free (Good) • Livelock (Bad)

42. The Timestamp Ordering Algorithm • Each data item X has two timestamps • Write_TS(X): (i.e., who was the last transaction changed item X) • Refers to the largest timestamps (youngest) of any transaction that successfully executed write_Item(X) • Write_TS(X)= TS(T) • Read_TS(X): (i.e., who was the last transaction T read or query the item X) • Refers to the largest timestamps (the youngest) of any transaction that successfully executed read_Item(X) • Read_TS(X) = TS(T)

43. Basic Timestamp-Ordering (BTO) • Compares TS(T) with read_TS(X) or write_TS (X) to ensure the proper ordering of transactions • In case of ordering violation, aborts the offending transaction • Ensures conflict serializabilty (GOOD) • Cascading rollback problem may occur (BAD)

44. Basic Timestamp Ordering (BTO) • The algorithm checks to see if there exists conflicting operations that violate the Time stamp Ordering as follows • When a transaction T issues write_item(x) If read_TS(x) > TS(T) OR write_TS(x)>TS(T), then abort T Else Execute the write_item (X) Set Write_TS(X) :=TS(T) • When a transaction T issues read_item(x) If Write_TS(X) >TS(T) then Reject the operation of T Else Execute read_item(x), Set Read_TS(X):= MAX{TS(T), Read_TS(x)}

45. Strict Timestamp Ordering (STO) • Strict Timestamp Ordering (STO) • Guarantees that the schedule are • Strict (recoverable) • Conflict Serializable • STO works as follows • A transaction T issuing Read/write operations is delayed until transaction T’ that wrote the data item X used by T has either committed or aborted • Deadlock free because T waits on T’ only if the T is younger one (i.e.,TS(T)>TS(T’) )

46. Thomas’ write Rule • Thomas’ write rule • Rejects fewer write operations (GOOD) • Does not guarantee conflict serializabilty (BAD) • When T requests write_item(X) • if TS(T) < read_TS(X) then abort T • if TS(T) < write_TS(X) then IGNORE write and continue Else write_item(X); write_TS(X) := TS(T) C. If neither A or B occurs, then execute the write_item(X)

47. Thomas' write Rule :Example time ignore

48. Multi-version CC • Maintain the old values (versions) of a data item when the items are changed • The goal is to ensure that a transaction never has to wait to read a database item • maintain several versions of each database items • A major drawback of the approach is it demands more storage (BAD)

49. Multiversion approaches. • Two main approaches: • BTO • 2PL • Optimistic CC also maintain multiple versions of the data items (discussed later)

50. Multi-version technique using BTO: Case 1 (write_item) • Maintain several versions X1, ..Xnof each data item X • For each version, it keeps the value of version Xi, and two timestamps: • Read_TS (Xi) (i.e., the youngest transaction who read this version) • Write_TS (Xi) (i.e., the youngest transaction who wrote this version) • To perform write_item(X) by T • If TS(T)  write_TS(Xi) and TS(T) < read_TS(Xi) • then abort T • else write a new version of X and Set write_TS(Xi)= read_TS(Xi)=TS(T) • T is aborted because it is trying to write Xi that already is read by other transaction T’