More About Transaction Management

1. More About Transaction Management Chapter 10

2. Contents • Transactions that Read Uncommitted Data • View Serializability • Resolving Deadlocks • Distributed Databases • Long-Duration Transactions

3. The Dirty-Data Problem Data is “dirty” if it has been written by a transaction that is not yet committed Example 1: A written by T1 is a dirty data ,T2’s reading of A leaves the database with an inconsistent state

4. T1 T2 A B 25 25 :l1(A);r1(A); A:=A+100; w1(A);l1(B);u1(A); 125 l2(A);r2(A); A:=A*2; w2(A);u2(A); 250 l2(B);Denied r1(B); Abort;u1(B); l2(B); u2(A); r2(B); B:=B*2; w2(B);u2(B) 50 T1 writes dirty data and then aborts

5. Example 2: • T1 T2 T3 A B C • 150 175 RT=0 RT=0 RT=0 • WT=0 WT=0 WT=0 • w2(B) WT=150 • r1(B) • r2(A) RT=150 • r3(C) RT=175 • w2(C) • Abort WT=0 • w3(A)) WT=175 T1 has read dirty data from T2 and must abort when T2 does

6. Cascading Rollback • When transaction T aborts,we must find each transaction U that read dirty data from T,abort U,find any transaction V that read dirty data from U,abort V,and so on • Both a timestamp-based scheduler with a commit bit and a validation-based scheduler avoids cascading rollback

7. Managing Rollbacks • Strict Locking: A transaction must not release any write locks (or other locks, such as increment locks that allow values to be changed) until the transaction has either committed or aborted,and the commit or abort log record had been flushed to disk • A schedule of transactions that obey the strict locking rule is called recoverable

8. View Serializability Conflict equivalent View equivalent Conflict serializable View serializable

9. Motivating example Schedule Q T1 T2 T3 Read(A) Write(A) Write(A) Write(A)

10. P(Q): T1 T2 T3  Not conflict serializable! Same as Q = r1(A) w2(A) w1(A) w3(A)

11. But now compare Q to Ss, a serial schedule: Q T1 T2 T3 Read(A) Write(A) Write(A) Write(A) Ss T1 T2 T3 Read(A) Write(A) Write(A) Write(A)

12. T1 reads same thing in Q, Ss • T2,T3 read same thing (nothing?) • After Q or Ss, DB is left in the same state  So what is wrong with Q?

13. Definition Schedules S1,S2 areView Equivalent if: (1) If in S1: wj(A)  ri(A) then in S2: wj(A)  ri(A) (2) If in S1: ri(A) reads initial DB value, then in S2: ri(A) also reads initial DB value (3) If in S1: Ti does last write on A, then in S2: Ti also does last write on A  means “reads value produced”

14. Definition Schedule S1 is View Serializable if it is view equivalent to some serial schedule

15. View Serializable  Conflict Serializable e.g., See Schedule Q • Conflict Serializable  View Serializable ? ? View Conflict Serializable Serializable

16. Lemma Conflict Serializable  View Serializable Proof: Swapping non-conflicting actions does not change what transactions read nor final DB state

17. Venn Diagram All schedules View Serializable Conflict Serializable

18. Note: All view serializable schedules that are not conflict serializable, involve useless write S = W2(A) … W3(A)….. no reads

19. How do we test for view-serializability? P(S) not good enough… (see schedule Q)

20. w4(A) this action can move if this write exists • One problem: some swaps involving conflicting actions are OK… e.g.: S = ….w2(A)……r1(A).... w3(A)…

21. Another problem: useless writes S = …..W2(A)…….. W1(A)….. no A reads

22. To check if S is View Serializable (1) Add final transaction Tf that reads all DB(eliminates condition 3 of V-S definition) E.g.: S = …..W1(A)…….. W2(A)… rf(A) Last A write ? add

23. (2) Add initial transaction Tb that writes all DB(eliminates condition 2 of V-S definition) E.g.: S = wb(A) ... r1(A) … w2(A) … ? add

24. 0 (3) Create labeled precedence graph of S: (3a) If wi(A)  rj(A) in S, add Ti  Tj

25. (3b) For each wi(A)  rj(A) do consider each wk(A): [TkTb] - If TiTb  TjTf then insert Tk Ti some new p Tj Tk - If Ti=Tb  TjTf then insert Tj Tk - If TiTb  Tj=Tf then insert Tk Ti p p 0 0

26. (4) Check if there is some selection from each arc pair that turn s LP(S) into “acyclic” (if so, S is V-S)

27.   rule 3(a) rule 3(b) 0 0 rule 3(b) 0 0 0 0 Example: check if Q is V-S: Q = r1(A) w2(A) w1(A) w3(A) Q’ = wb(A) r1(A) w2(A) w1(A) w3(A) rf(A) T1 T2 Tf Tb LP(S) acyclic!! S is V-S T3

28.    0 0 1 0 0 0 1 0 0 do not pick this one of “1” pair LP(Z) acyclic, so Z is V-S (equivalent to Tb T1 T2 T3 Tf) Another example: Z=wb(A) r1(A) w2(A) r3(A) w1(A) w3(A) rf(A) T1 T2 Tf Tb T3

29. Deadlocks • Detection • Wait-for graph • Prevention • Resource ordering • Timeout • Wait-die • Wound-wait

30. Deadlock Detection • Build Wait-For graph • Use lock table structures • Build incrementally or periodically • When cycle found, rollback victim T5 T2 T1 T7 T4 T6 T3

31. The Waits-For Graph • In the waits-for graph there is an arc from node(transactions) T to node U if there is some database element A such that • U holds a lock on A • T is waiting for a lock on A,and • T cannot get a lock on A in its desired mode unless U first releases its lock on A • If there are no cycles in the waits-for graph, then each transaction can eventually complete • If there is a cycle,then no transaction in the cycle can ever make progress,so there is a deadlock

32. T1 T2 T3 T4 • 1)l1(A);r1(A) • l2(C);r2(C) • l3(B);r3(B) • l4(D);r4(D) • l2(A);Denied • l3(C);Denied • l4(A);Denied • l1(B);Denied Beginning of a schedule with a deadlock

33. 4 3 2 2 3 1 Waits-for graph after T1 is rolled back Waits-for graph with a cycle caused by step(8)

34. Deadlock Prevention By Resource Ordering • Order all elements A1, A2, …, An • Every transaction is required to request locks on element in order. Problem : Ordered lock requests not realistic in most cases

35. Timeout • If transaction waits more than L sec., roll it back! • Simple scheme • Hard to select L

36. Wait-die • Transactions given a timestamp when they arrive …. ts(Ti) • Ti can only wait for Tj if ts(Ti)< ts(Tj) ...else die

37. wait? Example: T1 (ts =10) T2 (ts =20) T3 (ts =25) wait wait

38. Wound-wait • Transactions given a timestamp when they arrive … ts(Ti) • Ti wounds Tj if ts(Ti)< ts(Tj) else Ti waits “Wound”: Tj rolls back and gives lock to Ti

39. wait Example: T1 (ts =25) T2 (ts =20) T3 (ts =10) wait wait

40. Comparison of Deadlock Management • Both wound-wait and wait-die are easier to implement than the waits-for graph method. • The waits-for graph method aborts transactions only when there is a deadlock. However, either wound-wait or wait-die will sometimes roll back a transaction when there was no deadlock.

41. DBMS DBMS DBMS DBMS data data data data Distributed Databases Distributed Database System

42. Advantages of a DDBS • Speedy Queries by Parallelism • Fault Tolerance by Data Replication Increasing complexity and communication cost

43. Data Distribution • A bank with many branches • A chain store with many individual stores • A digital library with a consortium of universities

44. Partitioning a relation among many sites • Horizontal Decomposition • Vertical Decomposition

45. Parallelism: Pipelining • Example: • T1 SELECT * FROM A WHERE cond • T2 JOIN T1 and B select join A B (with index)

46. Parallelism: Concurrent Operations • Example: SELECT * FROM A WHERE cond data location is important... merge select select select A where A.x < 10 A where 10  A.x < 20 A where 20  A.x

47. join strategy Join Processing • Example: JOIN A, B over attribute X B1 B2 A1 A2 A.x < 10 A.x  10 B.x < 10 B.x  10

48. Join Processing • Example: JOIN A, B over attribute X B1 B2 A1 A2 A.z < 10 A.z  10 B.z < 10 B.z  10 join strategy

49. Data Replication • Fault Tolerance • Query Speedup Some Problems • How to keep copies identical • How to place copies properly • How to handle communication failure

50. Distributed Transactions • Transaction components at a different site • Each having the local scheduler and logger