Database Concurrency Control

1. Database Concurrency Control • 1 Purpose of Concurrency Control • To enforce Isolation (through mutual exclusion) among conflicting transactions. • To preserve database consistency through consistency preserving execution of transactions. • To resolve read-write and write-write conflicts. • Example: • In concurrent execution environment if T1 conflicts with T2 over a data item A, then the existing concurrency control decides if T1 or T2 should get the A and if the other transaction is rolled-back or waits.

2. ACS-4902 Locking What is a lock? A lock is a variable associated with a database item that describes the status of the database item with respect to database operations that can be applied to the database item. Locks are managed by the Lock Manager within the DBMS Database items that could be locked vary from a field value up to a whole database: • field value in a row • row • block • table • database See section on granularity

3. ACS-4902 Binary Locks • a binary lock is in one of two states 0 or 1 • (lock(X) is either 0 or 1) • values of locks can be held in a lock table • two lock operations: unlock_item(X) and lock_item(X) • (these must be implemented as indivisible operations) • used to enforce mutual exclusion on data items • between lock_item(X) and unlock_item(X), it is said that the transaction holds a lock on item X

4. ACS-4902 item lock trx_id X Binary Locks: data structures • lock(X) can have one of two values: • 0 or 1 • unlocked or locked • etc • We require a Wait Queue where we keep track of suspended transactions Lock Table Wait Queue item transaction 1 1 X 2 Y 1 2 3 Y

5. ACS-4902 Binary Locks: operations • lock_item(X) • used to gain exclusive access to item X • if a transaction executes lock_item(X) then • if lock(X)=0 then • the lock is granted {lock(X) is set to 1} and the transaction can carry on • {the transaction is said to hold a lock on X} • otherwise • the transaction is placed in a wait queue until lock_item(X) can be granted • {i.e. until some other transaction unlocks X}

6. ACS-4902 Binary Locks: operations • unlock_item(X) • used to relinquish exclusive access to item X • if a transaction executes unlock_item(X) then • lock(X) is set to 0 • {note that this may enable some other blocked transaction to resume execution}

7. ACS-4902 Example: Binary Locks What are the contents of the wait queue and lock table at times 1 through 9? timeTransaction1 Transaction2 1 lock_item(X) 2 read_item(X) 3 lock_item(X) 4 write_item(X) 5 unlock_item(X) 6 commit 7 read_item(X) 8 unlock_item(X) 9 commit T2 is placed in the wait queue T2 can resume At time 7 the lock_item(X) initiated at time 3 is completed and then the read_item(X) is done

8. Database Concurrency Control Two-Phase Locking Techniques • Locking is an operation which secures • (a) permission to Read • (b) permission to Write a data item for a transaction. • Example: • Lock (X). Data item X is locked in behalf of the requesting transaction. • Unlocking is an operation which removes these permissions from the data item. • Example: • Unlock (X): Data item X is made available to all other transactions. • Lock and Unlock are Atomic operations.

9. Two-Phase Locking Techniques: Essential components Two locks modes: (a) shared (read) (b) exclusive (write). Shared mode: shared lock (X) More than one transaction can apply share lock on X for reading its value but no write lock can be applied on X by any other transaction. Exclusive mode: Write lock (X) Only one write lock on X can exist at any time and no shared lock can be applied by any other transaction on X. Conflict matrix Database Concurrency Control

10. Shared and Exclusive locks Real Time Example • Think of a lockable object as a blackboard (lockable) in a class room containing a teacher (writer) and many students (readers). • While a teacher is writing something (exclusive lock) on the board: • Nobody can read it, because it's still being written, and she's blocking your view => If an object is exclusively locked, shared locks cannot be obtained. • Other teachers won't come up and start writing either, or the board becomes unreadable, and confuses students => If an object is exclusively locked, other exclusive locks cannot be obtained. • When the students are reading (shared locks) what is on the board: • They all can read what is on it, together => Multiple shared locks can co-exist. • The teacher waits for them to finish reading before she clears the board to write more => If one or more shared locks already exist, exclusive locks cannot be obtained.

11. Shared And Exclusive Locks • So a read lock says "you can read now but if you want to write you'll have to wait" whereas a write lock says "you'll have to wait". •  Read locks are also known as shared locks because more than one process can read at the same time. The point of a read lock is to prevent the acquisition by another process of a write lock. By contrast, a write lock inhibits all other operations while a write operation completes which is why it is described as exclusive.

12. Two-Phase Locking Techniques: Essential components Lock Manager: Managing locks on data items. Lock table: Lock manager uses it to store the identify of transaction locking a data item, the data item, lock mode and pointer to the next data item locked. One simple way to implement a lock table is through linked list. Database Concurrency Control

13. Database Concurrency Control Two-Phase Locking Techniques: Essential components • Database requires that all transactions should be well-formed. A transaction is well-formed if: • It must lock the data item before it reads or writes to it. • It must not lock an already locked data items and it must not try to unlock a free data item.

14. Database Concurrency Control Two-Phase Locking Techniques: Essential components • The following code performs the lock operation: B: if LOCK (X) = 0 (*item is unlocked*) then LOCK (X)  1 (*lock the item*) else begin wait (until lock (X) = 0) and the lock manager wakes up the transaction); goto B end;

15. Database Concurrency Control Two-Phase Locking Techniques: Essential components • The following code performs the unlock operation: LOCK (X)  0 (*unlock the item*) if any transactions are waiting then wake up one of the waiting the transactions;

16. 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. Lock-Based Protocols

17. 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.)

18. Example of a transaction performing locking: T2: lock-S(A); read (A); unlock(A); lock-S(B); read (B); unlock(B); display(A+B) Locking as above is not sufficient to guarantee serializability — if A and B get updated in-between the read of A and B, the displayed sum would be wrong. 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 (Cont.)

19. Schedule to Check Grant of shared and Exclusive Locks

20. Schedule to Check Grant of shared and Exclusive Locks • Problem that A+B in T2 leads to a problem • Sum is 250 at the end of T2 which is inconsistent

21. Solution to the problem

23. 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

24. The potential for deadlock exists in most locking protocols. Deadlocks are a necessary evil. Starvation is also possible if concurrency control manager is badly designed. For example: A transaction may be waiting for an X-lock on an item, while a sequence of other transactions request and are granted an S-lock on the same item. The same transaction is repeatedly rolled back due to deadlocks. Concurrency control manager can be designed to prevent starvation. Pitfalls of Lock-Based Protocols (Cont.)

25. 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. It can be proved that the transactions can be serialized in the order of their lock points (i.e. the point where a transaction acquired its final lock). The Two-Phase Locking Protocol

26. 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.)

27. There can be conflict serializable schedules that cannot be obtained if two-phase locking is used. However, in the absence of extra information (e.g., ordering of access to data), two-phase locking is needed for conflict serializability in the following sense: Given a transaction Ti that does not follow two-phase locking, we can find a transaction Tj that uses two-phase locking, and a schedule for Ti and Tj that is not conflict serializable. The Two-Phase Locking Protocol (Cont.)

28. 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. But still relies on the programmer to insert the various locking instructions. Lock Conversions

29. A transaction Ti issues the standard read/write instruction, without explicit locking calls. The operation read(D) is processed as: ifTi has a lock on D then read(D) else begin if necessary wait until no other transaction has a lock-X on D grant Ti a lock-S on D; read(D) end Automatic Acquisition of Locks

30. Implementation of Locking • A lock manager can be implemented as a separate process to which transactions send lock and unlock requests • The lock manager replies to a lock request by sending a lock grant messages (or a message asking the transaction to roll back, in case of a deadlock) • The requesting transaction waits until its request is answered • The lock manager maintains a data-structure called a lock table to record granted locks and pending requests • The lock table is usually implemented as an in-memory hash table indexed on the name of the data item being locked