Concurrency Control

1. Concurrency Control

2. Introduction Transaction processing systems usually allow multiple transactions to run concurrently. By allowing multiple transactions to run concurrently will improve the performance of the system in terms of increased throughout or improved response time, but this allows causes several complications with consistency of the data. Ensuring consistency in spite of concurrent execution of transaction require extra work, which is performed by the concurrency controller system of DBMS.

3. Locking Techniques for Concurrency Control  Some of the main techniques used to control concurrent execution of transactions are based on the concept of locking data items. What is Lock?  A lock is a variable associated with a data item that describes the status of the item with respect to possible operations that can be applied to it. Generally, there is one lock for each data item in the database. Locks are used as a means of synchronizing the access by concurrent transactions to the database items. Types of Locks  Several types of locks are used in concurrency control. To introduce locking concepts gradually, we first discuss binary locks, which are simple but restrictive and so are not used in practice. We then discuss shared/exclusive locks, which provide more general locking capabilities and are used in practical database locking schemes.

4. Binary Locks A binary lock can have two states or values: locked and unlocked.  A distinct lock is associated with each database item A. If the value of the lock on A is 1, item A cannot be accessed by a database operation that requests the item. If the value of the lock on A is 0, then item can be accessed when requested. We refer to the current value of the lock associated with item A as LOCK(A).There are two operations, lock_item and unlock_item are used with binary locking. A transaction requests access to an item A by first issuing a lock_item(A) operation. if Lock(A)=1, the transaction is forced to wait. If LOCK(A) =0 it is set to 1 (the transaction locks the item) and the transaction is allowed to access item A. When the transaction is through using the item, it issues an unlock_item(A) operation, which sets LOCK(A) to 0(unlocks the item) so that A may be accessed by other transactions. Hence a binary lock enforces mutual exclusion on the data item.

5. Rules of Binary Locks • A transaction T must issue the operation lock_item(A) before any read_item(A) or write_item (A) operations are performed in T. • A transaction T must issue the operation unlock_item(A) after all read_item(A) and write_item(A) operations are completed in T. • A transaction T will not issue a lock_item(A) operation if it already holds the lock on item A. • A transaction T will not issue an unlock_item(A) operation unless it already holds the lock on item A. • The lock manager module of the DBMS can enforce these rules. Between the lock_item(A) and unlock_item(A) operations in transaction T, T is said to hold the lock on item A. At most one transaction can hold the lock on a particular item. Thus no two transactions can access the same item concurrently.

6. Disadvantages of Binary Locks As discussed earlier, binary locking scheme is too restrictive for database items, because at most one transaction can hold a lock on a given item. So, binary locking system cannot be used for practical purpose.

7. Share/Exclusive (for Read/Write) Locks We should allow several transactions to access the same item A if they all access A for reading purposes only. However, if a transaction is to write an item A, it must have exclusive access to A. For this purpose, a different type of lock called a multiple-mode lock is used. In this scheme there are shared/exclusive or read/write locks are used.  Locking operations There are three locking operations called read_lock(A), write_lock(A) and unlock(A) represented as lock-S(A),lock-X(A),unlock(A) (Here, S indicates shared lock, X indicates exclusive lock)can be performed on a data item. A lock associated with an item A, LOCK(A), now has three possible states: “read-locked”, “write-locked,” or “unlocked.” A read-locked item is also called share-locked item because other transactions are allowed to read the item, whereas a write-locked item is called exclusive-locked, because a single transaction exclusively holds the lock on the item.

9. Example As an illustration consider the simplified banking system. Let A and B be two accounts that are accessed by transactions T1 and T2. Transaction T1 transfers Rs.50 from account B to account A and is defined as T1 lock-X(B); read(B,b) ; b :=b-50 ; write(B,b) ; unlock(B) ; lock-X(A) read(A,a); a:=a+50; write(A,a); unlock(A);

10. Transaction T2 displays the total amount of money in accounts A and B that is, the sum A+B and is defined as T2 lock-S(A); read(A,a); unlock(A); lock-S(B); read(B,b); unlock(B); display(a+b);  Suppose that the values of accounts A and B are Rs.100 and Rs.200, respectively. If these two transactions are executed serially, either in the order T1 and T2 or the order T2, T1 then transaction T2 will display the value Rs.300. If however, these transactions are executed concurrently, as shown in Schedule 1. In this case, transaction T2 displays Rs.250, which is incorrect. The reason for this mistake is that the transaction T1 unlocked data item B too early, as a result of which T2 shows an inconsistent state.

12. Solution of Inconsistency Problem  Suppose now that unlocking is delayed to the end of the transaction. The transaction T3 corresponds to T1 with unlocking delayed and is defined as T3 lock-X(B); read(B,b) ; b :=b-50 ; write(B,b) ; lock-X(A) ; read(A,a); a:=a+50; write(A,a); unlock(B); unlock(A);

13. Transaction T4 corresponds to T2 with unlocking delayed, and is defined as T4 lock-S(A); read(A,a); lock-S(B); read(B,b); display(a+b); unlock(A); unlock(B);

18. Solution of Starvation Problem •  We can avoid starvation problem of transactions by granting locks as follows: • If a shared lock is requested, the queue of requests is empty, and the object is not currently locked in exclusive mode, the lock manager grants the lock and updates the lock table entry for the object (indicating that the object is locked in shared mode, and incrementing the number of transactions holding a lock by one). • If an exclusive lock is requested, and no transaction currently holds a lock on the object (which also implies the queue of requests is empty) the lock manager grants the lock and updates the lock table entry. • Otherwise, the requested lock cannot be immediately granted and the lock request is added to the queue of lock requests for this object. The transaction requesting the lock is suspended. •  Note that if T1 has a shared lock on A and T2 requests an exclusive lock, T2 request is queued. Now if T3 requests a shared lock, its request enters the queue behind that of T2, even though the requested lock is compatible with the lock held by T1. This rule ensures that T2 does not starve, that is, wait indefinitely while a stream of other transactions; acquire shared locks and thereby prevent T2 from getting the exclusive lock that is waiting for.

23. Problems with two-phase locking protocols There are two problems with two-phase locking protcol.These are: 1.      Deadlock 2.     Cascading roll-back Deadlock As discussed above, two-phase locking does not ensure freedom from deadlock. As shown in transaction T3 and T4 are in two phase, but still there is problem of deadlock. Cascading roll-back As shown in partial schedule shown on next page each transaction observers two-phase locking protocol.

25. Let us consider, if transaction T5 fails after the read(A,a) operation of transaction of T7. Then, T5 must be rollback, which results into rollback of T6 and T7 also. Because, transaction T6 and T7 reads the value of A modified by transaction T5. Since transaction T5 fails and rollback, it means that transaction T5 obtain the original value of A and cancels the modified value of A, but the other transactions T6 and T7 process the modified value of A and result into inconsistent state of database. In order to obtain the consistent state of database transactions T6 and T7, must also rollback and has to start again. Thus we can say that rollback of T5 results in to rollback T6 and T7 also. This problem is called as cascading of rollback.

26. Solutions to avoid cascading of rollbacks There are two solutions to avoid cascading of rollback. These are: ¨      Strict two phase locking protocol ¨     Rigorous two phase locking protocol Strict two-phase locking protocol:  The strict two-phase locking protocol, requires, that in addition to locking being two-phase, all exclusive-mode locks taken by a transaction must be held until that transaction commits. This requirement ensures that any data written by an uncommitted transaction are locked in exclusive mode until the transaction commits, preventing any other transaction from reading the data.

27. Rigorous two-phase locking protocol:  It requires all locks to be held until the transaction commits. It can be easily verified that, with rigorous two-phase locking transactions can be serialized in the order in which they commit. Most database systems implement either strict or rigorous two-phase locking.

28. Conversion of Locks In order to understand the conversion of locks first consider the following example T8 read(A1,a1); read(A2,a2); ……. read(An,an); …….. write(A1,a1); Transaction T9 is defined as T9 read(A1,a1); read(A2,a2); display(a1+a2);

29.  If we follow two-phase locking protocol the transaction T8 must lock item A1 in exclusive mode because it has to perform write operation on item A1. The other transaction T9 which has to just read the value of A1 unable to get shared lock on item A1 until T8 perform write operation on A1.Since, T8 needs an exclusive lock on A1 only at the end of its execution where it perform write operation on A1 ,it is better if T8 could initially lock A1 in shared mode and then could later change lock to exclusive mode when it has to perform write operation and we could get more concurrency. Because, then transaction T8 and T9 could access data item A1 and A2 simultaneously. This observation leads us to a refinement of the basic two-phase locking protocol, in which lock conversions are allowed. We shall provide a mechanism for upgrading a shared lock to an exclusive lock, and downgrading an exclusive lock to a shared lock. We denote conversion from shared to exclusive modes by upgrade, and from exclusive to shared by downgrade. Lock conversion cannot be allowed to occur arbitrarily. Upgrading can take place in only the growing phase, whereas downgrading can take place in only the shrinking phase. Returning to our example, transactions T8 and T9 can run concurrently under the refined two-phase locking protocol, as shown in the incomplete schedule given below where only some of the locking instructions are shown.

31. ¨      When a transaction Ti issues a read(Q) operation. It issue a lock-S(Q) instruction followed by read(Q) instruction. ¨      When a transaction Ti issues a write(Q) operation, the system check to see whether Ti already holds a shared locked on data item Q. If it does the system issue an updgrade(Q) instruction followed by write(Q) instruction. ¨      Otherwise the system issue a lock-X(Q) instruction followed by write(Q) instruction. ¨      All lock obtained by the transaction are unlocked after the transaction commit or abort.

32. Non two-phase locking protocol  In order to develop those protocols, which are not based on two phase locking technique, one must have additional information on how each transaction will access the database. There are various models that differ in the amount of such information required. There are two types of protocols which are not be on two phase locking scheme: ¨   Graph Based Protocol ¨   Time stamp-based Protocol