1 / 42

Lec 10: Concurrency Control Techniques

Lec 10: Concurrency Control Techniques. Chapter Outline. Databases Concurrency Control Purpose of Concurrency Control Two-Phase locking Limitations of CCMs Index Locking Lock Compatibility Matrix Lock Granularity. Database Concurrency Control. 1 Purpose of Concurrency Control

princes
Download Presentation

Lec 10: Concurrency Control Techniques

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lec 10: Concurrency Control Techniques

  2. Chapter Outline • Databases Concurrency Control • Purpose of Concurrency Control • Two-Phase locking • Limitations of CCMs • Index Locking • Lock Compatibility Matrix • Lock Granularity

  3. Database Concurrency Control • 1 Purpose of Concurrency Control • To enforce Isolation (through mutual exclusion) among conflicting transactions. • To preserve database consistency through consistency preserving execution of transactions. • To resolve read-write and write-write conflicts. • Example: • In concurrent execution environment if T1 conflicts with T2 over a data item A, then the existing concurrency control decides if T1 or T2 should get the A and if the other transaction is rolled-back or waits.

  4. Two-Phase Locking Techniques • Locking is an operation which secures • (a) permission to Read • (b) permission to Write a data item for a transaction. • Example: • Lock (X). Data item X is locked in behalf of the requesting transaction. • Unlocking is an operation which removes these permissions from the data item. • Example: • Unlock (X): Data item X is made available to all other transactions. • There are three locking operations: Read_lock(X), write_lock(X), unlock(X)

  5. Multiple-mode lock: (a) shared (read) (b) exclusive (write). Shared mode: shared lock (X) More than one transaction can apply share lock on X for reading its value but no write lock can be applied on X by any other transaction. Exclusive mode: Write lock (X) Only one write lock on X can exist at any time and no shared lock can be applied by any other transaction on X. Conflict matrix Two-Phase Locking Techniques: Essential components

  6. Lock Manager: Managing locks on data items. Lock table: Lock manager uses it to store the identify of transaction locking a data item, the data item, lock mode and pointer to the next data item locked. One simple way to implement a lock table is through linked list. Two-Phase Locking Techniques: Essential components

  7. Two-Phase Locking Techniques: Essential components • Database requires that all transactions should be well-formed. A transaction is well-formed if: • It must lock the data item before it reads or writes to it. • It must not lock an already locked data items and it must not try to unlock a free data item.

  8. Database Concurrency Control Two-Phase Locking Techniques: Essential components • The following code performs the read operation: B: if LOCK (X) = “unlocked” then begin LOCK (X)  “read-locked”; no_of_reads (X)  1; end else if LOCK (X)  “read-locked” then no_of_reads (X)  no_of_reads (X) +1 else begin wait (until LOCK (X) = “unlocked” and the lock manager wakes up the transaction); go to B end;

  9. Database Concurrency Control Two-Phase Locking Techniques: Essential components • The following code performs the write lock operation: B: if LOCK (X) = “unlocked” then LOCK (X) )  “write-locked” else begin wait (until LOCK (X) = “unlocked” and the lock manager wakes up the transaction); go to B end;

  10. Database Concurrency Control Two-Phase Locking Techniques: Essential components • The following code performs the unlock operation: if LOCK (X) = “write-locked” then begin LOCK (X)  “unlocked”; wakes up one of the transactions, if any end else if LOCK (X)  “read-locked” then begin no_of_reads (X)  no_of_reads (X) -1 if no_of_reads (X) = 0 then begin LOCK (X) = “unlocked”; wake up one of the transactions, if any end end;

  11. Conversion of locks • Lock conversion • Lock upgrade: existing read lock to write lock if Ti has a read-lock (X) and Tj has no read-lock (X) (i  j) then convert read-lock (X) to write-lock (X) else force Ti to wait until Tj unlocks X • Lock downgrade: existing write lock to read lock Ti has a write-lock (X) (*no transaction can have any lock on X*) convert write-lock (X) to read-lock (X)

  12. Two-Phase Locking Techniques: • A transaction is said to follow the Two-Phase locking protocol if all locking operations (read_lock,write_lock) precede the first unlock operation in the transaction. • Two-Phase locking protocol: • Locking (Growing) Phase: • A transaction applies locks (read or write) on desired data items one at a time. • Unlocking (Shrinking) Phase: • A transaction unlocks its locked data items one at a time. • Requirement: • For a transaction these two phases must be mutually exclusively, that is, during locking phase unlocking phase must not start and during unlocking phase locking phase must not begin.

  13. Two phase locking Locks of Ti growing shrinking time Each transaction has to follow, no locking anymore after the first unlocking

  14. Transactions that do not obey two-phase locking T1T2Result read_lock (Y); read_lock (X); Initial values: X=20; Y=30 read_item (Y); read_item (X); Result of serial execution unlock (Y); unlock (X); T1 followed by T2 write_lock (X); Write_lock (Y); X=50, Y=80. read_item (X); read_item (Y); Result of serial execution X:=X+Y; Y:=X+Y; T2 followed by T1 write_item (X); write_item (Y); X=70, Y=50 unlock (X); unlock (Y);

  15. Cont. Two-Phase Locking Techniques: The algorithm T1 T2 Result read_lock (Y); X=50; Y=50 read_item (Y); Nonserializable because it. unlock (Y); violated two-phase policy. read_lock (X); read_item (X); unlock (X); write_lock (Y); read_item (Y); Y:=X+Y; write_item (Y); unlock (Y); write_lock (X); read_item (X); X:=X+Y; write_item (X); unlock (X); Time

  16. Cont. • Transactions T1 and T2 of the previous figure do not follow the two-phase locking protocol because the write_lock(X) operation follows the unlock(Y) operation in T1, and similarly the write_lock(Y) operation follows the unlock(X) operation in T2. • If we enforce two-phase locking , the transactions can be rewritten as T’1 and T’2 in next slide

  17. Cont. T’1T’2 read_lock (Y); read_lock (X); T1 and T2 follow two-phase read_item (Y); read_item (X); policy but they are subject to write_lock (X); Write_lock (Y); deadlock, which must be unlock (Y); unlock (X); dealt with. read_item (X); read_item (Y); X:=X+Y; Y:=X+Y; write_item (X); write_item (Y); unlock (X); unlock (Y);

  18. Two-Phase Locking Techniques • There are a number of variations of two-phase locking (2PL). The two-phase locking techniques as follows: • Basic: • Transaction locks data items incrementally. This may cause deadlock which is dealt with. • Conservative (static): • Prevents deadlock by locking all desired data items it access before the transaction begins execution. • Strict: • unlocking is performed after a transaction terminates (commits or aborts). This is the most commonly used two-phase locking techniques. • Is not deadlock-free.

  19. Conservative 2PL • Difficult but deadlock free first action starts growing locks shrinking time

  20. Strict 2PL • T does not release any write locks until it commits or aborts

  21. Dealing with Deadlock and Starvation Deadlock T’1T’2 read_lock (Y); T1 and T2 did follow two-phase read_item (Y); policy but they are deadlock read_lock (X); read_item (X); write_lock (X); (waits for X) write_lock (Y); (waits for Y) • Illustrating the deadlock problem. A partial schedule T’1 and T’2 that is in a state of deadlock.

  22. Dealing with Deadlock and Starvation • Deadlock prevention • A transaction locks all data items it needed before it begins execution. • This way of locking prevents deadlock since a transaction never waits for a data item. • The conservative two-phase locking uses this approach.

  23. Dealing with Deadlock and Starvation • Deadlock detection and resolution • In this approach, deadlocks are allowed to happen. The system construct and maintain a wait-for-graph for detecting cycle. If a cycle exists, then one transaction involved in the cycle is selected (victim) abort and rolled-back.

  24. Dealing with Deadlock and Starvation • Starvation • Starvation occurs when a particular transaction waits or restarted and never gets a chance to proceed further. • In a deadlock resolution it is possible that the same transaction may be selected as victim and rolled-back. • One solution for starvation is to have a fair waiting scheme, such as using a first-come-first-served queue; transactions are enabled to lock an item in the order in which they originally requested the lock.

  25. Concurrency Control based on Timestamp ordering • Timestamp • is a unique identifier created by the DBMS to identify a transaction. • Typically, timestamp values are assigned in the order in which the transactions are submitted to the system, so a timestamp can be thought of as the transaction start time. • We will refer to the timestamp of transaction T as TS(T). • Concurrency control techniques based on timestamp ordering do not use locks; hence, deadlocks cannot occur.

  26. Cont. Timestamps can be generated in several ways: • One possibility is to use a counter that is incremented each time its value is assigned to a transaction. The transaction timestamps are numbered 1, 2, 3, ... in this scheme. A computer counter has a finite maximum value, so the system must periodically reset the counter to zero when no transactions are executing for some short period of time. • Another way to implement timestamps is to use the current date/time value of the system clock and ensure that no two timestamp values are generated during the same tick of the clock.

  27. The Timestamp Ordering Algorithm The algorithm must ensure that, for each item accessed by conflicting operations in the schedule, the order in which the item is accessed does not violate the serializability order. To do this, the algorithm associates with each database item X two timestamp (TS) values: 1- Read_TS(X): The read timestamp of item Xi this is the largest timestamp among all the timestamps of transactions that have successfully read item X-that is, read_TS(X) = TS(T), where T is the youngest transaction that has read X successfully. 2- Write_TS(X): The write timestamp of item Xi this is the largest of all the timestamps of transactions that have successfully written item X-that is, write_TS(X) = TS(T), where T is the youngest transaction that has written X successfully.

  28. Basic Timestamp Ordering 1- Transaction T issues a read_item(X) operation: • If write_TS(X) > TS(T), then abort and roll back T and reject the operation. This should be done because some younger transaction with timestamp greater than TS(T)-and hence after T in the timestamp ordering-has already written the value of item X before T had a chance to read X. • If write_TS(X) :s; TS(T), then execute the read_item(X) operation of T and set read_TS(X) to the larger of TS(T) and the current read_TS(X).

  29. Basic Timestamp Ordering 2- Transaction T issues a write_item(X) operation: • If read_TS(X) > TS(T) or if write_TS(X) > TS(T), then abort and roll back T and reject the operation. This should be done because some younger transaction with a timestamp greater than TS(T)-and hence after T in the timestamp ordering-has already read or written the value of item X before T had a chance to write X, thus violating the timestamp ordering. • If the condition in part (a) does not occur, then execute the write_tem(X) operation ofT and set write_TS(X) to TS(T).

  30. Thomas’s Write Rule • 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_i tem(X) operation of T because it is already outdated and obsolete. • If the conditions given in 1 and 2 above do not occur, then execute write_item(X) of T and set write_TS(X) to TS(T).

  31. Validation (Optimistic) Concurrency Control Schemes • In this technique only at the time of commit serializability is checked and transactions are aborted in case of non-serializable schedules. • There are three phases for this concurrency control protocol: 1. Read and execution phase: • During this phase, the system execute transaction Ti. It read the values of the various data items and stores them in variables local to Ti. It performs all write operations on temporary local variables without updates of the actual database.

  32. Validation (Optimistic) Concurrency Control Schemes 2. Validation phase: Transactions Ti performs a validation test to determine if local variables can be written without violating seializability. 3. Write phase: If Ti is validated successfully (step2), the updates are applied to the database; otherwise, Ti is rolled back.

  33. Validation-based protocol To perform the validation test, we need to know when the phases of transaction Ti took place. Thus, each transaction Ti is associated three timestamp: • Start(Ti): the time when Ti started its execution. • Validation(Ti): the time when Ti entered its validation phase. • Finish(Ti): the time when Ti finished its write phase. Serializability order is determined by timestamp given at validation time., to increase concurrency. Thus TS(Ti) is given the value of validation(Ti).

  34. Validation test for transaction Tj • The validation test for transaction Tj requires that, for all transaction Ti with TS(Ti)< TS(Tj), one of the following two conditions holds: • Finish (Ti)<Start(Tj) since Ti completes its execution before Tj started, the serializability order is then maintained. • Start(Tj)<Finish(Ti)<Validation(Tj) and the set of data items written by Ti does not intersect with the set of data items read by Tj. Then validation succeeds and Tj can be committed. Otherwise, validation fails and Tj is aborted.

  35. Granularity of data items and Multiple Granularity Locking • A lockable unit of data defines its granularity. Granularity can be coarse (entire database) or it can be fine (a tuple or an attribute of a relation). • Data item granularity significantly affects concurrency control performance. Thus, the degree of concurrency is low for coarse granularity and high for fine granularity. • Example of data item granularity: • A field of a database record (an attribute of a tuple) • A database record (a tuple or a relation) • A disk block • An entire file • The entire database

  36. A granularity hierarchy illustrates multiple granularity level locking from coarse (database) to fine (record). Cont.

  37. Cont. • To manage such hierarchy, in addition to read (shared) and write (exclusive), three additional locking modes, called intention lock modes are defined: • Intention-shared (IS): indicates that a shared lock(s) will be requested on some descendent nodes(s). • Intention-exclusive (IX): indicates that an exclusive lock(s) will be requested on some descendent node(s). • Shared-intention-exclusive (SIX): indicates that the current node is locked in shared mode but an exclusive lock(s) will be requested on some descendent nodes(s).

  38. Lock compatibility matrix for multiple granularity locking: Cont.

  39. Cont. • Transaction Ti can lock a node using the following rules for producing serializable schedule: • The lock compatibility matrix must be observed. • The root of the tree must be locked first, in any mode.. • A node N can be locked by a transaction T in S or IX mode only if the parent node is already locked by T in either IS or IX mode. • A node N can be locked by T in X, IX, or SIX mode only if the parent of N is already locked by T in either IX or SIX mode. • T can lock a node only if it has not unlocked any node (to enforce 2PL policy). • T can unlock a node, N, only if none of the children of N are currently locked by T.

  40. Consider we have the following three transaction: • T 1 wants to update record r111 and record r211. • T2 wants to update all records on page P12. • T3 wants to read record r11j and the entire f2 file. The next figure shows a possible serializable schedule for these three transaction

  41. Lock operations to illustrate a serializable schedule T1 T2 T3 ____________________________________________ IX(db) IX(f1) IX(db) IS(db) IS(f1) IS(p11) IX(p11) X(r111) IX(f1) X(p12) S(r11j) IX(f2) IX(p21) IX(r211) Unlock (r211) Unlock (p21) Unlock (f2) S(f2)

  42. Cont. T1 T2 T3 _________________________________________ unlock(p12) unlock(f1) unlock(db) unlock(r111) unlock(p11) unlock(f1) unlock(db) unlock (r111j) unlock (p11) unlock (f1) unlock(f2) unlock(db)

More Related