Concurrency Control: 18.4 Locking Systems with Several Lock Modes

1. Concurrency Control: 18.4 Locking Systems with Several Lock Modes CS257 Spring/2009 Professor: Tsau Lin Student: Suntorn Sae-Eung ID: 212

2. 18.4 Locking Systems with Several Lock Modes • In 18.3, if a transaction must lock a database element (X) either reads or writes, • No reason why several transactions could not read X at the same time, as long as none write X • Introduce locking schemes • Shared/Read Lock ( For Reading) • Exclusive/Write Lock( For Writing)

3. 18.4.1 Shared & Exclusive Locks • Transaction Consistency • Cannot write without Exclusive Lock • Cannot read without holding some lock • Consider lock for writing is “stronger” than for reading • This basically works on 2 principles 1. A read action can only proceed a shared or an exclusive lock 2. A write lock can only proceed a exclusive lock • All locks need to be unlocked before commit

4. 18.4.1 Shared & Exclusive Locks (cont.) • Two-phase locking (2PL) of transactions Ti • Notation: sli (X)– Ti requests shared lock on DB element X xli (X)– Ti requests exclusive lock on DB element X ui (X)– Ti relinquishes whatever lock on X Lock  R/W  Unlock

5. 18.4.1 Shared & Exclusive Locks (cont.) • Legality of Schedules • An element may be locked by: one write transaction or by several read transactions shared mode, but not both

6. 18.4.2 Compatibility Matrices • A convenient way to describe lock-management policies • Rows correspond to a lock held on an element by another transaction • Columns correspond to mode of lock requested. • Example :

7. 18.4.3 Upgrading Locks • A transaction (T) taking a shared lock is friendly toward other transaction. • When Twants to read and write a new value X, 1. T takes a shared lock on X. 2. performs operations on X (may spend long time) 3. When T is ready to write a new value, “Upgrade” shared lock to exclusive lock on X.

8. 18.4.3 Upgrading Locks (cont.) • Observe the example • T1 cannot take an exclusive lock on B until all locks on B are released. ‘B’ is released T1 retry and succeed

9. 18.4.3 Upgrading Locks (cont.) • Upgrading can simply cause a “Deadlock”. • Both the transactions want to upgrade on the same element Both transactions will wait forever !!

10. 18.4.4 Update locks • The third lock mode resolving the deadlock problem, which rules are • Only “Update lock” can be upgraded to a write (exclusive) lock later. • An “Update lock” is allowed to grant on X when there are already shared locks on X. • Once there is an “Update lock,” it prevents additional any kinds of lock, and later changes to a write (exclusive) lock. • Notation: uli (X)

11. 18.4.4 Update locks (cont.) • Example

12. 18.4.4 Update locks (cont.) • Compatibility matrix (asymmetric)

13. 18.4.5 Increment Locks • A useful lock for transactions which increase/decrease value. e.g. money transfer between two bank accounts. • If 2 transactions (T1, T2) add constants to the same database element (X), • It doesn’t matter which goes first, but no reads are allowed in between transaction processing • Let see on following exhibits

14. 18.4.5 Increment Locks (cont.) CASE 1 A=7 T2: INC (A,10) T1: INC (A,2) A=5 A=17 A=15 T2: INC (A,10) T1: INC (A,2) CASE 2

15. 18.4.5 Increment Locks (cont.) • What if A=7 T2: INC (A,10) T1: INC (A,2) A=5 A=15 A=5 A=15 T2: INC (A,10) A != 17 T1: INC (A,2) A=5 A=7 A=5

16. 18.4.5 Increment Locks (cont.) • INC (A, c) – • Increment action of writing on database element A, which is an atomic execution consisting of 1. READ(A,t); 2. t = t+c; 3. WRITE(A,t); • Notation: • ili (X)– action of Ti requesting an increment lock on X • inci (X)– action of Ti increments X by some constant; don’t care about the value of the constant.

17. 18.4.5 Increment Locks (cont.) • Example

18. 18.4.5 Increment Locks (cont.) • Compatibility matrix

20. References • H. Garcia-Molina, J. Ullman, and J. Widom, “Database System: The Complete Book,” second edition: chapter 18.3-18.4, p.897-913, Prentice Hall, New Jersy, 2008

21. For your attention