Lecture 7 on Database Concurrency Control

1. Lecture 7 on Database Concurrency Control This lecture shows the transactions concurrency management by using Lock compatibility table, two phase lock, and basic time stamp. The objective of concurrency control is to allow maximum concurrent transactions while maintaining their serializability on their update to database systems.

2. Database concurrency control If the DBMS is to serve multiple users, there are two possibilities: either the users must schedule themselves to take turns using the system, or the system must explicitly provide mechanisms to handle multiple user requests received concurrently from multiple workstations. The DBMS must have built-in mechanisms for controlling concurrent processes such that the integrity of the database is preserved.

3. Integrity of database The problem of integrity is the problem of ensuring that the data in the database is accurate – that is, the problem of guarding the database against invalid updates. In other words, it is possible that two concurrently executing transactions, each correct in itself, may interfere with each other in such a manner as to produce incorrect results.

4. Serializability of update transactions Serializability is a formal criterion for the correctness of executing concurrent updates. A given interleaved execution of concurrent update transactions will be considered correct if it is serializable, that is, if it produces the same result as some serial execution of those same transactions. Locking and time stamping schemes can be set up to ensure serializability.

5. Update Inconsistency

6. Time inconsistency This problem arises when one process carries out a sequential analysis of some part of the database while another process performs random updates. If a process is totaling certain amounts and another process transfers a sum already included in the total to a record which has not yet been reached in the totaling, then although the database is consistent at the end of both processes, the totaling process produces a result which is inconsistent with any real state of the database.

7. Time Inconsistency

8. Lockout mechanism To preserve the integrity of the database, a part of the database is preserved for the exclusive use at most one writing process at a time. A transaction can obtain a lock on a record by issuing a request to a system component called the Lock Manager. If transaction T holds a lock on record R, then T will almost certainly be guaranteed that no concurrent transaction will be able to update R until T releases its lock.

9. Exclusive Locks If transaction T holds an exclusive lock on some objects, then no distinct transaction T’ can acquire a lock of any type on the object until T releases its lock. Any transaction that intends to update a record R must first acquire an X lock on it. If the lock cannot be acquired, the transaction goes into a wait state; the transaction will resume processing when the record becomes available and the lock can be granted.

10. Without lock mechanism A’s update is lost at time t4

11. With exclusive lock B is forced to wait for A’s update complete XFIND – find a desired record and acquire a lock XRELEASE – release the lock on a record UPD – update a record FIND – obtain addressability to some specific record occurrence

12. Shared locks If transaction T holds a shared lock on some objects, then a distinct transaction T’ can acquire a shared lock on that object. The distinct transaction T’ can acquire an exclusive lock on that object only when all shared locks on it have been released.

13. Error occurs because shared lock “SFIND” is not applied for each Account record Transaction A produces incorrect result

14. Update Locks An update lock represents an indication that the transaction may be going to update the record; it is compatible with S locks but not with other U locks, or X locks. Any transaction that intends to update a record R must first acquire an U lock on it. After the transaction has acquired the lock, any subsequent update of the record will promote that lock to X level.

15. Replacing the two SFINDs by UFINDs will prevent deadlock Deadlock occurs at time t4

16. Lock compatibility

17. Deadlock is a situation in which two or more transactions are in a simultaneous wait state, each waiting for the others to release a lock before it can proceed. Deadlock occurs at time t4

18. Non-serializability with locks Initial value x=20 y=30, T1-then-T2 X=50 Y=80, T2-then-T1 x=70 Y=50, T1-and-T2 X=50 Y=50, non-serializble

19. Two-Phase Locking • “Growing Phase” - Before operating on any object the transaction first acquires a lock on that object. • “Shrink Phase”- After releasing a lock, the transaction never acquires any more locks. If transactions are two-phase, then their executions are serializable.

20. The interleaved execution of A and B is serializable and produces F=1, G=0, H=1, same as A-then-B, yet A is not in two phase lock.

21. Execution of Schedule of interleaved A and B Transaction A Time Transaction B (H = 1) t0 (F=1) F = 0 t1 ATEMP = 0 t2 t3 G = 0 BTEMP = 0 t4 F = 0 t5 F = 0 + 1 = 1 t6 H = 1 t7 H = 0 + 1 = 1 t8 t9 Result: F = 1, H = 1, G = 0

22. Schedule of A-then-B Transaction A Time Transaction B (H = F+1) t0 (F=G+1) SFIND F: copy F into ATEMP t1 SRELEASE F t2 XFIND H t3 UPD H: replace H by ATEMP+1 t4 COMMIT t5 t6 SFIND G: copy G into BTEMP t7 XFIND F t8 UPD F: replace F by BTEMP+1 t9 COMMIT

23. Execution of Schedule of A-then-B Transaction A Time Transaction B (H = 1) t0 (F=1) F = 0 t1 ATEMP = 0 t2 H = 1 t3 H = 0 + 1 = 1 t4 t5 G = 0 t6 BTEMP = 0 t7 F = 0 t8 F = 0 + 1 = 1 t9 Result: F = 1, H = 1, G = 0

24. The interleaved execution of A and C produces F=1,G=0,H=1, not A-then-C and not C-then-A, therefore, it is not serializable.

25. Application of 2PL (two-phase lock) In the above two examples, if 2PL is applied, serializability will be enforeced. As a result, Two-phase lock provides guidelines for the safe design of transactions.

26. Timestamp-based concurrency control Timestamps are processed so that their execution is equivalent to a serial execution in their timestamp order. The timestamp-based concurrency control allows a transaction to read or write a data item x only if x had been last written by an older transaction. Otherwise, it rejects the operation and restarts the transaction.

28. Conflict Operations Two operations are in conflict if they operation on the same data item, one of them is a write operation, and they are issued by different Timestamps (T). e.g. <Ri (x), Wj (x)> and <Wi (x), Wj (x)> Basic problem: Ti and Tj may issue operations which would cause conflicts between them, say, both try to write to the same data item. The schedule of transactions are in the order of conflict operations.

29. Deadlock detection Many transactions are waiting indefinitively for each other as shown in wait-graph below: Edge 1  T1 waits for T2 etc Deadlock is detected when a cycle appears in the wait-graph For example: T1  T2  T3  T1, T1  T2  T4  T1 Use of time out method to abort transaction, but this technique does not necessarily detect deadlock.

31. 6. Let TS(Wi) be the timestamp of a write operation Wi of transaction Ti performed on data item X. IF TS(Wi) < RTM(x) or TS(Wi) < WTM(x) then Wi is rejected and Ti restarted else Wi is executed and WTM(x) = TS(Wi) Here TS(Wi) < RTM(x) means that last read operation is reading x value written by Wi impossible (since last read is supposed to occur before Wi). Also TS(Wi) < WTM(x) means that can’t have Wi performed prior to WTM(x), which had already occurred.

34. Example of Basic Timestamp Control Mechanism

35. Lecture Summary Transaction concurrency control of two phase lock and basic time stamp can prevent deadlock and maintain serializabiliy of the application database system. Nevertheless, implementation of the two phase lock and basic time stamp is an issue, especially in performance.

36. Review question How can deadlock be detected in online transaction processing? Can timeout method be able to detect deadlock? Can two phase lock guarantee serializability? Justify your answer. Can Basic Time Stamp guarantee serializability? Justify your answer.

37. Tutorial question “Although Two Phase Lock guarantee serializability, it does not permit all possible serializable schedule” • Explain the above statement in transactions concurrency control. (b) Illustrate your answer with an example not using Two Phase lock with justification, and (c) another example using Two Phase lock with justification.

38. Reading Assignment Chapter 18 Concurrency Control Techniques of “Fundamentals of Database Systems” by Elmasri and Navathe, Pearson, 2008.