CS 405G: Introduction to Database Systems

1. CS 405G: Introduction to Database Systems 19. Transaction Management

2. Dr. Qian is attending a conference at DC. • Miss Ruxin Dai is the instructor of this class, who will officially become a professor in a few month. Chen Qian @ University of Kentucky

3. Basic knowledge • Transaction view of DBMS • Read(x) • Write(x) • ACID • Atomicity: TX’s are either completely done or not done at all • Consistency: TX’s should leave the database in a consistent state • Isolation: TX’s must behave as if they are executed in isolation • Durability: Effects of committed TX’s are resilient against failures • SQL transactions -- Begins implicitly SELECT …; UPDATE …; ROLLBACK | COMMIT; Chen Qian @ University of Kentucky

4. T1: read(A); write(A); read(B); write(B); commit; T2: read(A); write(A); read(C); write(C); commit; A B C Concurrency control • Goal: ensure the “I” (isolation) in ACID Chen Qian @ University of Kentucky

5. T1 T2r(A)w(A)r(B)w(B) r(A) w(A) r(C) w(C) T1 T2r(A)w(A) r(A) w(A)r(B) r(C)w(B) w(C) T1 T2r(A) r(A)w(A) w(A)r(B) r(C)w(B) w(C) Good versus bad schedules Good! Bad! Good! (But why?) Read 400 Read 400 Write400 – 100 Write400 – 50 Chen Qian @ University of Kentucky

6. Serial schedule • Execute transactions in order, with no interleaving of operations • T1.r(A), T1.w(A), T1.r(B), T1.w(B), T2.r(A), T2.w(A), T2.r(C), T2.w(C) • T2.r(A), T2.w(A), T2.r(C), T2.w(C), T1.r(A), T1.w(A), T1.r(B), T1.w(B) • Isolation achieved by definition! • Problem: no concurrency at all • Question: how to reorder operations to allow more concurrency Chen Qian @ University of Kentucky

7. Conflicting operations • Two operations on the same data item conflict if at least one of the operations is a write • r(X) and w(X) conflict • w(X) and r(X) conflict • w(X) and w(X) conflict • r(X) and r(X) do not • r/w(X) and r/w(Y) do not • Order of conflicting operations matters • E.g., if T1.r(A) precedes T2.w(A), then conceptually, T1 should precede T2 Chen Qian @ University of Kentucky

8. T1 T2r(A)w(A) r(A) w(A)r(B) r(C)w(B) w(C) T1 T2r(A) r(A)w(A) w(A)r(B) r(C)w(B) w(C) T1 T1 T2 T2 Precedence graph • A node for each transaction • A directed edge from Ti to Tj if an operation of Ti precedes and conflicts with an operation of Tj in the schedule Good:no cycle Bad:cycle Chen Qian @ University of Kentucky

9. Conflict-serializable schedule • A schedule is conflict-serializable iff its precedence graph has no cycles • A conflict-serializable schedule is equivalent to some serial schedule (and therefore is “good”) • In that serial schedule, transactions are executed in the topological order of the precedence graph • You can get to that serial schedule by repeatedly swapping adjacent, non-conflicting operations from different transactions Chen Qian @ University of Kentucky

10. Remember those from OS class? • Lock: a high-level concept that describe the state of a data item with respect to read/write operations • Spinlock • Semaphore • Monitor • Deadlock: • A set of processes is deadlocked if each process is waiting for an event that only another process in the set can cause • Starvation: • A program continues to run indefinitely but fail to make any progress Chen Qian @ University of Kentucky

11. Next • Guarantee conflict-serializable schedule with 2 phase locking Chen Qian @ University of Kentucky

12. Locking • Rules • If a transaction wants to read an object, it must first request a shared lock (S mode) on that object • If a transaction wants to modify an object, it must first request an exclusive lock (X mode) on that object • Allow one exclusive lock, or multiple shared locks Mode of the lock requested Mode of lock(s)currently heldby other transactions Grant the lock? Compatibility matrix Chen Qian @ University of Kentucky

13. Add 1 to both A and B (preserve A=B) Multiply both A and B by 2(preserves A=B) lock-X(A) Read 100 Write 100+1 unlock(A) lock-X(A) T1 Possible scheduleunder locking Read 101 Write 101*2 unlock(A) T2 But still notconflict-serializable! lock-X(B) Read 100 Write 100*2 unlock(B) lock-X(B) Read 200 Write 200+1 unlock(B) Basic locking is not enough T1 T2r(A)w(A) r(A) w(A) r(B) w(B) r(B)w(B) A¹B! Chen Qian @ University of Kentucky

14. T1 T2r(A)w(A) r(A) w(A) r(B) w(B) r(B)w(B) T1 T2r(A)w(A) r(A) w(A) r(B)w(B) r(B) w(B) lock-X(A) lock-X(B) unlock(A) lock-X(A) lock-X(B) Cannot obtain the lock on Buntil T1 unlocks unlock(B) Two-phase locking (2PL) • All lock requests precede all unlock requests • Phase 1: obtain locks, phase 2: release locks 2PL guarantees aconflict-serializableschedule Chen Qian @ University of Kentucky

15. T1 T2r(A)w(A) r(A) w(A) r(B)w(B) r(B) w(B) Problem of 2PL • T2 has read uncommitted data written by T1 • If T1 aborts, then T2 must abort as well • Cascading aborts possible if other transactions have read data written by T2 Abort! • Even worse, what if T2 commits before T1? • Schedule is not recoverable if the system crashes right after T2 commits Chen Qian @ University of Kentucky

16. Strict 2PL • Only release locks at commit/abort time • A writer will block all other readers until the writer commits or aborts • Used in most commercial DBMS Chen Qian @ University of Kentucky

17. Next ... • A few examples Chen Qian @ University of Kentucky

18. Non-2PL, A= 1000, B=2000, Output =? Chen Qian @ University of Kentucky

19. 2PL, A= 1000, B=2000, Output =? Chen Qian @ University of Kentucky

20. Strict 2PL, A= 1000, B=2000, Output =? Chen Qian @ University of Kentucky

21. Lock Management • Lock and unlock requests handled by Lock Manager • LM keeps an entry for each currently held lock. • Entry contains: • List of xacts currently holding lock • Type of lock held (shared or exclusive) • Queue of lock requests Chen Qian @ University of Kentucky

22. Lock Management, cont. • When lock request arrives: • Does any other xact hold a conflicting lock? • If no, grant the lock. • If yes, put requestor into wait queue. • Lock upgrade: • Shared lock can request to upgrade to exclusive Chen Qian @ University of Kentucky

23. Deadlocks • Deadlock: Cycle of transactions waiting for locks to be released by each other. • Two ways of dealing with deadlocks: • prevention • detection • Many systems just punt and use Timeouts • What are the dangers with this approach? Chen Qian @ University of Kentucky

24. Deadlock Detection • Create and maintain a “waits-for” graph • Periodically check for cycles in graph Chen Qian @ University of Kentucky

25. Deadlock Detection (Continued) Example: T1: S(A), S(D), S(B) T2: X(B) X(C) T3: S(D), S(C), X(A) T4: X(B) Deadlock! T1 T2 T4 T3 Chen Qian @ University of Kentucky

26. Deadlock Prevention • Assign priorities based on timestamps. • Say Ti wants a lock that Tj holds Two policies are possible: Wait-Die: If Ti has higher priority, Ti waits for Tj; otherwise Ti aborts Wound-wait: If Ti has higher priority, Tj aborts; otherwise Ti waits • Why do these schemes guarantee no deadlocks? • Important detail: If a transaction re-starts, make sure it gets its original timestamp. -- Why? Chen Qian @ University of Kentucky

27. Summary • Correctness criterion for isolation is “serializability”. • In practice, we use “conflict serializability,” which is somewhat more restrictive but easy to enforce. • Two Phase Locking and Strict 2PL: Locks implement the notions of conflict directly. • The lock manager keeps track of the locks issued. • Deadlocks may arise; can either be prevented or detected. Chen Qian @ University of Kentucky