Chapter 8 Concurrency Control

1. Chapter 8 Concurrency Control • 8.1 Lock-Based Protocols • 8.2 Multiple Granularity • 8.3 Deadlock Handling • 8.4 Insert and Delete Operations

2. 8.1 Lock-Based Protocols One way to ensure serializability is to require that data items be accessed in a mutually exclusive manner.(lock) 1. Shared: if a transaction Ti has obtained a shared-mode lock on item Q, then Ti can read, but cannot write Q. 2. Exculsive: if a transaction Ti has obtained an exclusive-mode lock on item Q, then Ti can both read and write Q. Compatibility function:

3. T1: lock-X(B) read(B) B:=B-50 write(B) unlock(B) lock-X(A) read(A) A:=A+50 write(A) unlock(A) T2: lock-S(A) read(A) unlock(A) lock-S(B) read(B) unlock(B) display(A+B) concurrency-control manager grant-X(B,T1) grant-S(A,T2) grant-S(B,T2) grant-X(A,T1) T2 lock-S(A) read(A) unlock(A) lock-S(B) read(B) unlock(B) display(A+B) 8.1 Lock-Based Protocols T1 lock-X(B) read(B) B:=B-50 write(B) unlock(B) lock-X(A) read(A) A:=A+50 write(A) unlock(A)

4. T3: lock-X(B) read(B) B:=B-50 write(B) lock-X(A) read(A) A:=A+50 write(A) unlock(B) unlock(A) T4: lock-S(A) read(A) lock-S(B) read(B) unlock(A) unlock(B) display(A+B) 8.1 Lock-Based Protocols T4 lock-S(A) read(A) lock-S(B) T3 lock-X(B) read(B) B:=B-50 write(B) lock-X(A) deadlock

5. 8.1 Lock-Based Protocols When a transaction requests a lock on a data item in a particular node, and no other transaction has a lock on the same data item in a conflicting mode, the lock can be granted. … Ti lock-S(A) …… T2 lock-S(A) A:=A+50 unlock(A) T3 lock-S(A) A:=A+10 unlock(A) T4 lock-S(A) A:=A-20 unlock(A) T1 lock-X(A) wait wait wait wait …. starvation

6. 8.1.3 The Two-Phase Locking Protocol One protocol that ensures serializability is the two-phase locking protocol. This protocol requires that each transaction issue lock and unlock requests in two phases: ①Growing phase. A transaction may obtain locks, but may not release any lock. ②Shrinking phase. A transaction may release locks, but may not obtain any new locks.

7. growing phase shrinking phase 8.1.3 The Two-Phase Locking Protocol T3 lock-S(B) read(B) unlock(B) lock-X(A) A:=B+1 write(A) unlock(A) T4 lock-S(A) wait wait wait lock-S(A) read(A) unlock(A) lock-X(B) B:=A+1 write(B) unlock(B) T2 lock-S(A) wait wait wait wait lock-S(A) read(A) lock-X(B) B:=A+1 write(B) unlock(B) unlock(A) T3、T4 Serializable Not two phase lock point T1 lock-S(B) read(B) lock-X(A) A:=B+1 write(A) unlock(B) unlock(A) • The two-phase locking protocol ensures conflict serializability T1、T2 Serializable two phase

8. 8.1.3 The Two-Phase Locking Protocol T7 lock-S(A) read(A) T4 lock-S(A) read(A) lock-S(B) T6 lock-S(A) read(A) write(A) unlock(A) T3 lock-X(B) read(B) B:=B-50 write(B) lock-X(A) T5 lock-X(A) read(A) lock-S(B) read(B) write(A) unlock(A) T3、T4 two phase T5、T6、T7 two phase • Two-phase locking does not ensure freedom from deadlock. • Cascading rollback may occur under two-phase locking. deadlock

9. 8.1.3 The Two-Phase Locking Protocol • Strict two-phase locking protocol:This protocol requires not only that locking be two phase, but also that all exclusive-mode locks taken by a transaction be held until that transaction commits. • Rigorous two-phase locking protocol: This protocol requires that all locks be held until the transaction commits.

10. T8: read(a1); read(a2); … read(an); write(a1); T9: read(a1); read(a2); display(A+B) 8.1.3 The Two-Phase Locking Protocol • Upgrade: share to exclusive mode Upgrading can take place in only the growing phase • Downgrade: exclusive to share mode downgrading can take place in only the shrinking phase T9 lock-S(a1) lock-S(a2) unlock(a1) unlock(a2) T8 lock-S(a1) lock-S(a2) lock-S(a3) lock-S(a4) lock-S(an) upgrade(a1) lock conversion

11. 8.1.3 The Two-Phase Locking Protocol • When a transaction Ti issues a read(Q) operation, the system issues a lock-S(Q) instruction followed by the read(Q) instruction. • When Ti issues a write (Q) operation, the system checks to see whether Ti already holds a shared lock on Q. if it does, then the system issues an up-grade (Q) instruction, followed by the write (Q) instruction. Otherwise, the system issues a lock-X (Q) instruction, followed by the write (Q) instruction. • All locks obtained by a transaction are unlocked after that transaction commits or aborts.

12. 8.1.4 Graph-Based Protocols Tree protocol: the only lock instruction allowed is lock-X. each transaction Ti can lock a data item at most once, and must observe the following rules: ①The first lock by Ti may be on any data item. ②Subsequently, a data item Q can be locked by Ti only if the parent of Q is currently locked by Ti. ③Data items may be unlocked at any time. ④ A data item that has been locked and unlocked by Ti cannot subsequently be relocked by Ti.

13. A B C F E D I G H J 8.1.4 Graph-Based Protocols Lock and unlock instructions: T10: lock-X(B); lock-X(E); lock-X(D); unlock(B); unlock(E); lock-X(G); unlock(D); unlock(G); T11: lock-X(D); lock-X(H); unlock(D); unlock(H); T12: lock-X(B); lock-X(E); unlock(E); unlock(B); T13: lock-X(D); lock-X(H); unlock(D); unlock(H);

14. A B C F E D I G H J 8.1.4 Graph-Based Protocols T12 lock-X(B) lock-X(E) unlock(E) unlock(B) T13 lock-X(D) lock-X(H) unlock(D) unlock(H) T11 lock-X(D) lock-X(H) unlock(D) unlock(H) • Tree protocol does not ensure recoverabilityand cascadelessness. T10 lock-X(B) lock-X(E) lock-X(D) unlock(B) unlock(E) lock-X(G) unlock(D) unlock(G) • Tree protocol ensuresconflict serializabilityandfreedom from deadlock

15. 8.1.4 Graph-Based Protocols • Advantages: ①unlike two-phase locking, it is deadlock-free, so no rollbacks are required.unlocking may occur earlier. ② Earlier unlocking may lead to shorter waiting times, and to an increase in concurrency. • Disadvantages: ① in some cases, a transaction may have to lock data items that it does not access. ② Without prior knowledge of what data items will need to be locked, transactions will have to lock the root of the tree, and that can reduce concurrency greatly.

16. 8.2 Timestamp-Based Protocols • A method for determining the serializability order is to select an ordering among transactions in advance.(timestamp-ordering scheme) • Timestamps: with each transaction Ti in the system, we associate a unique fixed timestamp, denoted by TS(Ti). This timestamp is assigned by the database system before the transaction Ti starts execution. If a transaction Ti has been assigned timestamp TS(Ti), and a new transaction Tj enters the system, then TS(Ti)< TS(Tj). There are two simple methods for implementing this scheme: ① Use the value of the system clock as the timestamp; that is, a transaction’s timestamp is equal to the value of the clock when the transaction enters the system. ② Use a logical counter that is incremented after a new timestamp has been assigned; that is, a transaction’s timestamp is equal to the value of the counter when the transaction enters the system.

17. 8.2 Timestamp-Based Protocols Timestamp-ordering protocol: 1.Suppose that transaction Ti issues read(Q). a. If TS(Ti)<W-timestamp(Q), then Ti needs to read a value of Q that was already overwritten. Hence, the read operation is rejected, and Ti is rolled back. b. If TS(Ti)>=W-timestamp(Q) then the read operation is executed, and R-timestamp(Q) is set to the maximum of R-timestamp(Q) and TS(Ti).

18. 8.2 Timestamp-Based Protocols 2.Suppose that transaction Ti issues write(Q). a. If TS(Ti)<R-timestamp(Q), then the value of that Ti is producing was needed previously, and the system assumed that value would never be produced. Hence, the system rejects the write operation and rolls Ti back. b. If TS(Ti)<W-timestamp(Q) then Ti is attempting to write an obsolete value of Q. hence, the system rejects this write operation and rolls Ti back. c. Otherwise, the system executes the write operation and sets W -timestamp(Q) to TS(Ti)

19. T14: read(B); read(A); display(A+B); T15: read(B); B:=B-50; write(B); read(A); A:=A+50; write(A); display(A+B); 8.2 Timestamp-Based Protocols TS(T14)< TS(T15) T15 read(B) B:=B-50 write(B) read(A) A:=A+50 write(A) display(A+B) T14 read(B) read(A) display(A+B) R-timestamp(B)= TS(T15) W-timestamp(B)= TS(T15) R-timestamp(A)= TS(T15)

20. 8.4 Multiple Granularity

21. 8.4 Deadlock Handling • A system is in a deadlock state if there exists a set of transactions such that every transaction in the set is waiting for another transaction in the set. • There two principal methods for dealing with the deadlock problem: ①deadlock prevention ②deadlock detection&deadlock recovery

22. 8.4.1 Deadlock Prevention There are two approaches to deadlock prevention: ①one approach ensures that no cyclic waits can occur by ordering the requests for locks, or requiring all locks to be acquired together. Disadvantages: a. It is often hard to predict, before the transaction begins,what data items need to be locked. b. Data-item utilization may be very low. ②the other approach is closer to deadlock recovery, an performs transaction rollback instead of waiting for a lock, whenever the wait could potentially result in a deadlock.

23. T22 lock-S(A) wait T23 lock-X(A) T4 lock-S(A) read(A) lock-S(B) T24 lock-X(A) rollback T3 lock-X(B) read(B) write(B) lock-X(A) 8.4.1 Deadlock Prevention 1.The wait-die scheme is a nonpreemptive technique. When transaction Ti requests a data item currently held by Tj, Ti is allowed to wait only if it has a timestamp smaller than that of Tj(that is, Ti is older than Tj). Otherwise, Ti is rolled back(dies). Timestamp: T3<T4 T4 rollback

24. T22 lock-S(A) T23 lock-X(A) rollback T4 lock-S(A) read(A) lock-S(B) T24 lock-X(A) wait T3 lock-X(B) read(B) write(B) lock-X(A) 8.4.1 Deadlock Prevention 2. The wound-wait scheme is a primitive technique. It is a counterpart to the wait-die scheme. When transaction Ti requests a data item currently held by Tj, Ti is allowed to wait only if it has a timestamp larger than that of Tj(that is, Ti is younger than Tj). Otherwise, Tj is rolled back(Tj is wounded by Ti). Timestamp: T3<T4 T4 rollback

25. 8.4.1 Deadlock Prevention Similarity:both the wound-wait and the wait-die schemes avoid starvation. Difference:operation ① In the wait-die scheme, the older the transaction gets, the more it tends to wait. By contrast, in the wound-wait scheme, an older transaction never waits for a younger transaction. ②In the wait-die scheme, if a transaction Ti dies and is rolled back because it requested a data item held by transaction Tj, the Ti may reissue the same sequence of requests when it is restarted. If the data item is still held by Tj, then Ti will die again. Thus, Ti may die several times before acquiring the needed data item.contrast this series of events with what happens in the wound-wait scheme. There may be fewer rollbacks in the wound-wait scheme.

26. 8.4.2 Timeout-Based Scheme Lock timeouts:a transaction that has requested a lock waits for at most a specified amount of time. If the lock has not been granted within that time, the transaction is said to timeout ,and it rolls itself back and restarts. If there was in fact a deadlock, one or more transactions involved in the deadlock will time out and roll back, allowing the others to proceed.

27. T26 T28 T26 T28 T25 T25 T27 T27 8.4.3 Deadlock Detection and Recovery Deadlock Detection: wait-for graph:G=(V,E)V:is a set of verticesE:is a set of edgesTi→Tj:transaction Ti is waiting fortransaction Tj to release a data item that it needs. Transaction T28 is requesting an item held byT27. Deadlock: contains a cycle

28. 8.4.3 Deadlock Detection and Recovery Recovery from Deadlock: roll back ①selection of a victim: Given a set of deadlocked transactions, we must determine which transaction to rollback to break the deadlock.(minimum cost) ② Rollback: Once we have decided that a particular transaction must be rolled back, we must determine how far this transaction should be rolled back.(total rollback, partial rollback) ③ Starvation: We must ensure that transaction can be picked as a victim only a finite number of times.(include the number of rollbacks in the cost factor)