Concurrency Control

1. Concurrency Control By Ankit Patel 112 Aniket Mulye 111

2. INTRODUCTION • Enforcing serializability by locks • Locks • Locking scheduler • Two phase locking • Locking systems with several lock modes • Shared and exclusive locks • Compatibility matrices • Upgrading/updating locks • Incrementing locks

3. Locks • It works as follows : • A request from transaction • Scheduler checks in the lock table • Generates a serializable schedule of actions.

4. Consistency of transactions • Actions and locks must relate each other • Transactions can only read & write only if has a lock and has not released the lock. • Unlocking an element is compulsory. • All locks need to be unlocked before commit • Legality of schedules • No two transactions can aquire the lock on same element without the prior one releasing it.

5. Locking scheduler • The order in which the individual steps of different transactions occur is regulated by the scheduler. • The general process of assuring that transactions preserve consistency when executing simultaneously is called concurrency control. • Grants lock requests only if it is in a legal schedule. • Lock table stores the information about current locks on the elements.

6. The locking scheduler (contd.) • A legal schedule of consistent transactions but unfortunately it is not a serializable.

7. Locking schedule (contd.) • The locking scheduler delays requests that would result in an illegal schedule.

8. Two-phase locking • Guarantees a legal schedule of consistent transactions is conflict-serializable. • All lock requests proceed all unlock requests. • The growing phase: • Obtain all the locks and no unlocks allowed. • The shrinking phase: • Release all the locks and no locks allowed.

9. Working of Two-Phase locking • Assures serializability. • Two protocols for 2PL: • Strict two phase locking : Transaction holds all its exclusive locks till commit / abort. • Rigorous two phase locking : Transaction holds all locks till commit / abort. • Possible to find a transaction Tj that has a 2PL and a schedule S for Ti ( non 2PL ) and Tj that is not conflict serializable.

10. Failure of 2PL. • 2PL fails to provide security against deadlocks.

11. Locking Systems with Several Lock Modes • Previous locking schemes were too simple to be practical. • Locking Scheme • Shared/Read Lock ( For Reading) • Exclusive/Write Lock( For Writing) • Compatibility Matrices • Upgrading Locks • Update Locks • Increment Locks

12. Shared & Exclusive Locks • Consistency of Transactions • Cannot write without Exclusive Lock • Cannot read without holding some lock • This basically works on these principles – 1. Consistency of Transactions • A read action can only proceed a shared or an exclusive lock • A write lock can only proceed a exclusive lock • All locks need to be unlocked before commit

13. Continued.. 2. Two-phase locking of transactions • Locking Must precede unlocking 3. Legality of Schedules • An element may be locked exclusively by one transaction or by several in shared mode, but not both.

14. Compatibility Matrices • Has a row and column for each lock mode. • Rows correspond to a lock held on an element by another transaction • Columns correspond to mode of lock requested. • Example :The column for S says that we can grant a shared lock on an element if the only locks held on that element currently are shared locks.

15. Upgrading Locks • Suppose a transaction wants to read as well as write : • It acquires a shared lock on the element • Performs the calculations on the element • And when its ready to write, It is granted a exclusive lock. • Transactions with unpredicted read write locks can use upgrading locks.

16. Upgrading locks (cont.) • Indiscriminating use of upgrading produces a deadlock. (Limitation) • Example : Both the transactions want to upgrade on the same element

17. Update locks • Solves the deadlock occurring in upgrade lock method. • A transaction in an update lock can read but cant write. • Update lock can later be converted to exclusive lock. • An update lock can only be given if the element has shared locks.

18. Update locks (cont.) • An update lock is like a shared lock when you are requesting it and is like a exclusive lock when you having it. • Compatibility matrix : - Columns for U & S locks are the same and rows for U & X are the same

19. Increment Locks • Used for incrementing & decrementing stored values. • E.g. - Transfer money from one bank to another, Ticket selling transactions in which number seats are decremented after each transaction.

20. Increment lock (cont.) • A increment lock does not enable either read or write locks on element. • Any number of transaction can hold increment lock on an element at any time. • Shared and exclusive locks cannot be granted if an increment lock is granted on element

21. Refrences • Database systems : The complete book • Hector Garcia, Jeffrey Ullman, Jennifer Widom • Topics 18.3 , 18.4

22. Any questions ?

23. Thank you