Chapter 16 : Concurrency Control

Chapter 16 : Concurrency Control

Lock-Based Protocols Chapter 16: Concurrency Control

A lock is a mechanism to control concurrent access to a data item Data items can be locked in two modes : 1. exclusive (X) mode. Data item can be both read as well as written. X-lock is requested using lock-X instruction. 2. shared (S) mode. Data item can only be read. S-lock is requested using lock-S instruction. Lock requests are made to concurrency-control manager. Transaction can proceed only after request is granted. A locking protocol is a set of rules followed by all transactions while requesting and releasing locks. Locking protocols restrict the set of possible schedules. Lock-Based Protocols

Lock-compatibility matrix A transaction may be granted a lock on an item if the requested lock is compatible with locks already held on the item by other transactions Any number of transactions can hold shared locks on an item, but if any transaction holds an exclusive on the item no other transaction may hold any lock on the item. If a lock cannot be granted, the requesting transaction is made to wait till all incompatible locks held by other transactions have been released. The lock is then granted. Lock-Based Protocols (Cont.)

Lock-Based Protocols (Cont.) Consider that T1 transfers $50 from account B to account A, and T2 displays the total amount in A and B. Suppose that the values of A and B are $100 and $200, respectively. T1: lock-X(B); T2: lock-S(A); read(B); read(A); B := B - 50; unlock(A); write(B); lock-S(B); unlock(B); read(B); lock-X(A); unlock(B); read(A); display(A + B); A := A + 50; write(A); unlock(A); Executed serially, then T2 displays $300. But executed concurrently(see Schedule 1), then T2 display $250, which is incorrect. The reason is that T1 unlocked data item B too early.

Lock-Based Protocols (Cont.) T1 T2 lock-X(B)read(B)B := B – 50write(B)unlock(B) lock-S(A) read(A) unlock(A) lock-S(B) read(B) unlock(B) display(A + B) lock-X(A)read(A)A := A + 50 write(A)unlock(A) Schedule 1.

Lock-Based Protocols (Cont.) Suppose now that unlocking is delayed to the end of the transaction. T3 and T4 correspond to T1 and T2 with unlocking delayed, respectively: T3: lock-X(B); T4: lock-S(A); read(B); read(A); B := B - 50; lock-S(B); write(B); read(B); lock-X(A); display(A + B); read(A); unlock(A); A := A + 50; unlock(B); write(A); unlock(B); unlock(A);

Consider the partial schedule Neither T3 nor T4 can make progress — executing lock-S(B) causes T4 to wait for T3 to release its lock on B, while executing lock-X(A) causes T3 to wait for T4 to release its lock on A. Such a situation is called a deadlock. To handle a deadlock one of T3 or T4 must be rolled back and its locks released. Pitfalls of Lock-Based Protocols

This is a protocol which ensures conflict-serializable schedules. Phase 1: Growing Phase transaction may obtain locks transaction may not release locks Phase 2: Shrinking Phase transaction may release locks transaction may not obtain locks The protocol assures serializability. The Two-Phase Locking Protocol

Two-phase locking does not ensure freedom from deadlocks Cascading roll-back is possible under two-phase locking. To avoid this, follow a modified protocol called strict two-phase locking. Here a transaction must hold all its exclusive locks till it commits/aborts. Rigorous two-phase locking is even stricter: here all locks are held till commit/abort. In this protocol transactions can be serialized in the order in which they commit. The Two-Phase Locking Protocol (Cont.)

Partial Schedule Under Two-Phase Locking Cascading rollback may occur under two-phase locking: Each transaction observes the two-phase locking protocol, but the failure of T5 after the read(A) step of T7 leads to cascading rollback of T6 and T7. Partial schedule under two-phase locking

Two-phase locking with lock conversions: – First Phase: can acquire a lock-S on item can acquire a lock-X on item can convert a lock-S to a lock-X (upgrade) – Second Phase: can release a lock-S can release a lock-X can convert a lock-X to a lock-S (downgrade) This protocol assures serializability. Lock Conversions

Lock Conversions Rigorous two-phase locking protocol: requires all locks to be held until the transaction commits. Consider T8: read(a1); T9: read(a1); read(a2); read(a2); ... display(a1 + a2). read(an); write(a1). Two-phase locking protocol ==> serial

Lock Conversions If we employ the two-phase locking protocol, then T8 must lock a1 in exclusive mode. Therefore, any concurrent execution of both transactions amounts to a serial execution. Notice, however, that T8 needs an exclusive lock on a1 only at the end of its execution, when it writes a1. Thus, if T8 could initially lock a1 in shared mode, and then could later change the lock to exclusive mode, we could get more concurrency, since T8 and T9 could access a1 and a2 simultaneously. ● lock conversion: - upgrade: shared → exclusive modes in growing phase. - downgrade: exclusive → shared mode in shrinking phase.

Incomplete Schedule With a Lock Conversion See the incomplete schedule with a lock conversion. ● Strict two-phase locking and rigorous two-phase locking (with lock conversions) are used extensively in commercial database systems.

End of Chapter 16

Chapter 16 : Concurrency Control