Chapter 18 Concurrency control Section 18.4

1. Chapter 18 Concurrency controlSection 18.4 CS 257 Dr. T.Y.Lin KarthikChalla ID – 112 Student Id: 008036548

2. Agenda • Locks • Lock types • Shared (Read Lock) • Exclusive (Write Lock) • Update lock (prevents deadlock) • Increment Lock • Compatibility Matrix • Upgrading Lock (deadlock scenario)

3. Locks • Imagine a collection of transactions performing their actions in an unconstrained manner. • In such a case different processes might access resources at the time causing instability and corrupting the data. • To prevent this, a scheduler maintains locks on the database elements. • When a transaction obtains locks on the database elements, other transactions cannot access the data.

4. Need for Lock Types • Consider a Transaction ‘T’ which needs to take a lock on a database element to read/write it. • If ‘T’ just wants to read the element then there is no need to lock the element as other transactions can also read the data at the same time. • Operations like these, make it necessary to have different types of locks rather than a single one.

5. Lock Types Shared Lock (read lock) • Read operations on the same element, do not cause conflict. • No need to starve other transaction which wants to read that element • A database element can have any number of Shared locks. Exclusive Lock (write lock) • We can have only one exclusive lock on a database element • Exclusive lock allows the transaction to perform both read/write operations on the database element. • Must use corresponding lock before read or write operation • All locks must be followed by unlock • Shared and Exclusive locks can not overlap.

6. Lock Types Shared - Exclusive Lock T1: sl1 (A) ; r1(A); xl1(B) ; r1(b); w1(B);u1(A); u1(B) T2: sl2 (A) ; r2(A); xl2(B) ; r2(b); u2(A); u2(B) T1T2 sl1(A); r1(A); sl2(A); r2(A); sl2(B); r2(B); xl1(B) Denied u2(A); u2(B); xl1(B); r1(B); w1(B); u1(A); u1(B); Where sl – shared lock, xl – exclusive lock, u - unlock

7. Complexity Matrix • As lock system gets more complex, CM is used to describe policies about various locks and their relationships with other locks. • A complexity matrix has a row and column for each lock mode. • Therows correspond to a lock that is already held on an element X by another transaction and the columns represent the mode of a lock on X that is requested. Example: Shared – Exclusive lock policy with CM | Lock Requested | S X ---------------------------------|--------------------------------------------------- Lock S | Yes No held X | No No in mode |

8. Upgrading Lock (deadlock scenario) • Some transactions need X lock after using S lock for several operations Scenario 1. T1 T2 sl1(A); r1(A); sl2(A); r2(A); sl2(B); r2(B); sl1(B); r1(B); xl1(B) Denied u2(A); u2(B); xl1(B); r1(B); w1(B); u1(A); u1(B);

9. Upgrading Lock (deadlock scenario) Continued… Scenario 2. T1 T2 sl1(A) sl2(A) xl1(B) Denied xl1(B) Denied Both transaction encounters 2 way deadlock and waits indefinitely

10. Update Locks.. Update Lock (ul) • Introduced to avoid deadlock scenario • ul(A) allows transaction to read not write but latter can be upgraded for X lock • While S lock can't be upgraded to be X lock • Can grant U lock while S lock is already active • Once U lock is active no further lock S or X are allowed

11. Update Locks (Continued…) Update Lock (ul) Shared – Exclusive lock policy with CM |Lock Requested | S X U ---------------------------------|--------------------------------------------------- Lock S | Yes No Yes held X | No No No in mode U | No No No

12. Increment Lock Increment Lock • Used to increment/decrement element • Applications: Bank money transfer, A plane ticket • Any number of increment lock can be applied on element A without causing any in-consistent behavior • Add or sub order is not important a +b = b + a Exmaple: INC (A, c) Where A is database element, c = constant + or -

13. Incremental Lock Example T1 T2 sl1(A); r1(A); sl2(A); r2(A); il2(B) ; inc2(B) il1(B) ; inc1(B) sl1(B); r1(B); u2(A); u2(B); u1(A); u1(B);

14. Thank You • Questions?