Concurrency Control

1. Concurrency Control

2. S X -- Ö Ö Ö -- S Ö Ö X Ö Locking: A Technique for C. C. • Concurrency control usually done via locking. • Lock info maintained by a “lock manager”: • Stores (XID, RID, Mode) triples. • This is a simplistic view; suffices for now. • Mode Î {S,X} • Lock compatibility table: • If a Xact can’t get a lock, it is suspended on a wait queue.

3. lock point # of locks shrinking phase growing phase Time Two-Phase Locking (2PL) • 2PL: • If T wants to read an object, first obtains an S lock. • If T wants to modify an object, first obtains X lock. • If T releases any lock, it can acquire no new locks! • Locks are automatically obtained by DBMS. • Guarantees serializability! • Why?

4. # of locks Time Strict 2PL • Strict 2PL: • If T wants to read an object, first obtains an S lock. • If T wants to modify an object, first obtains X lock. • Hold all locks until end of transaction. • Guarantees serializability, and recoverable schedule, too! • also avoids WW problems!

5. Lock Management • Lock and unlock requests are handled by the lock manager • Lock table entry: • Number of transactions currently holding a lock • Type of lock held (shared or exclusive) • Pointer to queue of lock requests • Locking and unlocking have to be atomic operations • Lock upgrade: transaction that holds a shared lock can be upgraded to hold an exclusive lock

6. Lock Manager Implementation • Question 1: What are we locking? • Tuples, pages, or tables? • Finer granularity increases concurrency, but also increases locking overhead. • Question 2: How do you “lock” something?? • Lock Table: A hash table of Lock Entries. • Lock Entry: • OID • Mode • List: Xacts holding lock • List: Wait Queue

7. Handling a Lock Request Lock Request (XID, OID, Mode) Mode==S Mode==X Currently Locked? Empty Wait Queue? Yes No Yes Currently X-locked? Yes No Put on Queue No Grant Lock

8. More Lock Manager Logic • On lock release (OID, XID): • Update list of Xacts holding lock. • Examine head of wait queue. • If Xact there can run, add it to list of Xacts holding lock (change mode as needed). • Repeat until head of wait queue cannot be run. • Note: Lock request handled atomically! • via latches (i.e. semaphores/mutex; OS stuff).

9. Lock Upgrades • Think about this scenario: • T1 locks A in S mode, T2 requests X lock on A, T3 requests S lock on A. What should we do? • In contrast: • T1 locks A in S mode, T2 requests X lock on A, T1 requests X lock on A. What should we do? • Allow such upgrades to supersede lock requests. • Consider this scenario: • S1(A), X2(A), X1(A): DEADLOCK! • BTW: Deadlock can occur even w/o upgrades: • X1(A), X2(B), S1(B), S2(A)

10. Deadlocks • Deadlock: Cycle of transactions waiting for locks to be released by each other. • Two ways of dealing with deadlocks: • Deadlock prevention • Deadlock detection

11. Deadlock Prevention X1(A), X2(B), S1(B), S2(A) • Assign a timestamp to each Xact as it enters the system. “Older” Xacts have priority. • Assume Ti requests a lock, but Tj holds a conflicting lock. • Wait-Die: If Ti has higher priority, it waits; else Ti aborts. (non-preemptive) • Wound-Wait: If Ti has higher priority, abort Tj; else Ti waits. (preemptive) • Note: After abort, restart with original timestamp! • Both guarantee deadlock-free behavior! Pros and cons of each?

12. Deadlock Detection • Create a waits-for graph: • Nodes are transactions • There is an edge from Ti to Tj if Ti is waiting for Tj to release a lock • Periodically check for cycles in the waits-for graph. • “Shoot” some Xact to break the cycle. • Simpler hack: time-outs. • T1 made no progress for a while? Shoot it.

13. Deadlock Detection (Continued) Example: T1: S(A), R(A), S(B) T2: X(B),W(B) X(C) T3: S(C), R(C) X(A) T4: X(B) T1 T2 T1 T2 T4 T3 T3 T3

14. Prevention vs. Detection • Prevention might abort too many Xacts. • Detection might allow deadlocks to tie up resources for a while. • Can detect more often, but it’s time-consuming. • The usual answer: • Detection is the winner. • Deadlocks are pretty rare. • If you get a lot of deadlocks, reconsider your schema/workload!

15. Database Tables Pages Tuples Multiple-Granularity Locks • Hard to decide what granularity to lock (tuples vs. pages vs. tables). • Shouldn’t have to decide! • Data “containers” are nested: contains

16. IS IX S X -- Ö Ö Ö Ö Ö -- IS Ö Ö Ö Ö IX Ö Ö Ö S Ö Ö Ö Ö X Solution: New Lock Modes, Protocol • Allow Xacts to lock at each level, but with a special protocol using new “intention” locks: • Before locking an item, Xact must set “intention locks” on all its ancestors. • For unlock, go from specific to general (i.e., bottom-up). • SIX mode: Like S & IX at the same time.

17. Multiple Granularity Lock Protocol • Each Xact starts from the root of the hierarchy. • To get S or IS lock on a node, must hold IS or IX on parent node. • What if Xact holds SIX on parent? S on parent? • To get X or IX or SIX on a node, must hold IX or SIX on parent node. • Must release locks in bottom-up order. Protocol is correct in that it is equivalent to directly setting locks at the leaf levels of the hierarchy.

18. IS IX S X -- Ö Ö Ö Ö Ö -- IS Ö Ö Ö Ö IX Ö Ö Ö Ö S Ö Ö Ö X Examples • T1 scans R, and updates a few tuples: • T1 gets an SIX lock on R, then repeatedly gets an S lock on tuples of R, and occasionally upgrades to X on the tuples. • T2 uses an index to read only part of R: • T2 gets an IS lock on R, and repeatedly gets an S lock on tuples of R. • T3 reads all of R: • T3 gets an S lock on R. • OR, T3 could behave like T2; can uselock escalation to decide which.

19. Timestamp CC • Idea: Give each object a read-timestamp (RTS) and a write-timestamp (WTS), give each Xact a timestamp (TS) when it begins: • If action ai of Xact Ti conflicts with action aj of Xact Tj, and TS(Ti) < TS(Tj), then ai must occur before aj. Otherwise, restart violating Xact.

20. Timestamp-Ordering Protocol • Timestamp order equals to serialization order • Ensure conflict serializability • TS(Ti) < TS(Tj) implies Ti runs before Tj in a serial schedule • Each data item Q have two timestamps • WTS(Q): the largest timestamp of any transaction that successfully executed write(Q) • RTS(Q): the largest timestamp of any transaction successfully executed read(Q)

21. When Xact T wants to read Object O • If TS(T) < WTS(O), this violates timestamp order of T w.r.t. writer of O. • So, abort T and restart it with a new, larger TS. (If restarted with same TS, T will fail again! Contrast use of timestamps in 2PL for ddlk prevention.) • If TS(T) > WTS(O): • Allow T to read O. • Reset RTS(O) to max(RTS(O), TS(T)) • Change to RTS(O) on reads must be written to disk! This and restarts represent overheads.

22. When Xact T wants to Write Object O • If TS(T) < RTS(O), this violates timestamp order of T w.r.t. writer of O; abort and restart T. • If TS(T) < WTS(O), violates timestamp order of T w.r.t. writer of O. • Thomas Write Rule: We can safely ignore such outdated writes; need not restart T! (T’s write is effectively followed by another write, with no intervening reads.) Allows some serializable but non conflict serializable schedules: • Else,allow T to write O. T1 T2 R(A) W(A) Commit W(A) Commit

23. Timestamp-Ordering Protocol • Consider the following history: r1[x], r2[x], w2[x], r1[y], r2[y]Let TS(T1) and TS(T2) be 1 and 2 respectively. Show that the above history can be accepted by the timestamp-ordering protocol • Ans: Assume the initial timestamp of x and y = 0, consider each of the cases: • r1[x]: Since TS(T1)≥W-timestamp(x), read is executed and R-timestamp(x)=1 • r2[x]: Since TS(T2)≥W-timestamp(x), read is executed and R-timestamp(x)=2 • w2[x]: Since TS(T2)≥W-timestamp(x) and TS(T2)≥R-timestamp(x), write is executed and W-timestamp(x)=2 • r1[y]: Since TS(T1)≥W-timestamp(y), read is executed and R-timestamp(y)=1 • r2[y]: Since TS(T2)≥W-timestamp(y), read is executed and R-timestamp(y)=2

24. Timestamp CC and Recoverability T1 T2 W(A) R(A) W(B) Commit • Unfortunately, unrecoverable schedules are allowed: • Timestamp CC can be modified to allow only recoverable schedules: • Buffer all writes until writer commits (but update WTS(O) when the write is allowed.) • Block readers T (where TS(T) > WTS(O)) until writer of O commits. • Similar to writers holding X locks until commit, but still not quite 2PL.

25. Exercise • Suppose that initially the read-timestamp of data item X is 25 and the write timestamp of X is 20. If [r,t] (respectively [w,t]) denotes a read (write) operation on X by any transaction with timestamp t, describe the behavior of Basic Timestamp Ordering on the following sequence of operations: [r,19], [r,22], [w,21], [w,26], [r,28], [w,27], [w,32], [w,31]

26. Exercise • Determine whether each of following executions is serializable or not. For each serializable execution, give the serial schedule which is equivalent to the given schedule. • R1(X), W2(X), W1(X), R2(Y) • R1(X), W2(X), W3(X), R1(X) • R1(X), W2(X), W3(Y), W1(Y)

27. Exercise • Transactions T1, T2, T3 are to be run concurrently. The following gives details of the proposed interleaving of read/write operations and the time when each such operation is to be scheduled. Time T1 T2 T3 1 read(A) 2 read(A) 3 read(D) 4 write(D) 5 write(A) 6 read(C) 7 write(B) 8 write(B) • Determine whether the operations can be executed in this order if concurrency is to be controlled using • two-phase locking • timestamp ordering