1 / 147

CS 245: Database System Principles Notes 09: Concurrency Control

CS 245: Database System Principles Notes 09: Concurrency Control. Hector Garcia-Molina. Chapter 18 [18] Concurrency Control. T1 T2 … Tn. DB (consistency constraints).

meris
Download Presentation

CS 245: Database System Principles Notes 09: Concurrency Control

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS 245: Database System PrinciplesNotes 09: Concurrency Control Hector Garcia-Molina Notes 09

  2. Chapter 18 [18] Concurrency Control T1 T2 … Tn DB (consistency constraints) Notes 09

  3. Interactions among concurrently executing transactions can causethe databasestate to become inconsistent, even when the transactions individually preservecorrectnessof the state, and there is no system failure. Thus, the timing ofindividual steps of different transactions needs to be regulated in some manner. This regulation is the job of the scheduler component of the DBMS, and thegeneral process of assuring that transactions preserve consistency when executingsimultaneously is called concurrency control. Notes 09

  4. In most situations, the scheduler will execute the reads and writes directly, first calling on the buffer manager if the desireddatabase element is not in a buffer. However, in some situations, it is notsafe for the request to be executed immediately. The scheduler must delay therequest. In some concurrency-control techniques, the scheduler may even abortthe transaction that issued the request Notes 09

  5. Example: T1: Read(A) T2: Read(A) A  A+100 A  A2 Write(A) Write(A) Read(B) Read(B) B  B+100 B  B2 Write(B) Write(B) Constraint: A=B Notes 09

  6. A schedule is a sequence of the important actions taken by one or more transactions. When studying concurrency control, the important read and write actionstake place in the main-memory buffers, not the disk. That is, a databaseelement A that is brought to a buffer by some transaction T may be read orwritten in that buffer not only by T but by other transactions that access A. A schedule is serial if its actions consist of all the actions of one transaction,then all the actions of another transaction, and so on. No mixing of the actions is allowed. Notes 09

  7. A B 25 25 125 125 250 250 250250 Schedule A(serial) T1 T2 Read(A); A  A+100 Write(A); Read(B); B  B+100; Write(B); Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Notes 09

  8. A B 25 25 50 50 150 150 150150 Schedule B(serial) T1 T2 Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Read(A); A  A+100 Write(A); Read(B); B  B+100; Write(B); Notes 09

  9. A schedule S is serializable if there is a serial schedule S' such that for every initial database state, the effects of S and S‘ are the same. Notes 09

  10. A B 25 25 125 250 125 250 250250 Schedule C (serializable) T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B); B  B+100; Write(B); Read(B);B  B2; Write(B); Notes 09

  11. A B 25 25 125 250 50 150 250150 Schedule D (not serializable) T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Read(B); B  B+100; Write(B); Notes 09

  12. A B 25 25 125 125 25 125 125 125 Same as Schedule D but with new T2’ Schedule E T1 T2’ Read(A); A  A+100 Write(A); Read(A);A  A1; Write(A); Read(B);B  B1; Write(B); Read(B); B  B+100; Write(B); Notes 09

  13. Want schedules that are “good”, regardless of • initial state and • transaction semantics • Only look at order of read and writes It is not realistic for the scheduler to concern itself with thedetails of computation undertaken by transactions. Any database element A that a transaction T writes is given a valuethat depends on the database state in such a way that no arithmeticcoincidences occur. Notes 09

  14. To make the notation precise: 1. An action is an expression of the form ri(X ) or Wi(X), meaning thattransaction T, reads or writes, respectively, the database element X . 2. A transaction Ti is a sequence of actions with subscript i. 3. A schedule S of a set of transactions T is a sequence of actions, in whichfor each transaction Ti in T , the actions of Ti appear in S in the sameorder that they appear in the definition of Tj itself. We say that S is aninterleaving of the actions of the transactions of which it is composed. Notes 09

  15. Example: Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B) Sc’=r1(A)w1(A) r1(B)w1(B)r2(A)w2(A)r2(B)w2(B) T1 T2 Notes 09

  16. However, for Sd: Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B) • as a matter of fact, • T2 must precede T1 • in any equivalent schedule, • i.e., T2 T1 Notes 09

  17. T2 T1 • Also, T1 T2 T1 T2 Sd cannot be rearranged into a serial schedule Sd is not “equivalent” to any serial schedule Sd is “bad” Notes 09

  18. Returning to Sc Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B) T1 T2 T1 T2  no cycles  Sc is “equivalent” to a serial schedule (in this case T1,T2) Notes 09

  19. Concepts Transaction: sequence of ri(x), wi(x) actions Conflicting actions: r1(A) w2(A) w1(A) w2(A) r1(A) w2(A) Schedule: represents chronological order in which actions are executed Serial schedule: no interleaving of actions or transactions Notes 09

  20. T2 issues write(B,S) input(B) completes System issues output(B) System issues input(B) output(B) completes B  S What about concurrent actions? Ti issues System Input(X) t  x read(x,t) issues completes input(x) time Notes 09

  21. So net effect is either • S=…r1(x)…w2(b)… or • S=…w2(B)…r1(x)… Notes 09

  22. What about conflicting, concurrent actions on same object? start r1(A)end r1(A) start w2(A)end w2(A) time • Assume equivalent to either r1(A) w2(A) • or w2(A) r1(A) •  low level synchronization mechanism • Assumption called “atomic actions” Notes 09

  23. Definition S1, S2 are conflict equivalentschedules if S1 can be transformed into S2 by a series of non-conflicting swaps of adjacent actions. A schedule is conflict serializable if it is conflict equivalent to some serial schedule. Notes 09

  24. Example: r1(A); w1(A); r2(A); w2(A); r1(B); w1(B); r2(B); w2(B); We claim this schedule is conflict-serializable. • r1(A); w1(A); r2(A); w2(A); r1(B);w1(B); r2(B); w2(B); • r1(A); w1(A); r2(A); r1(B);w2(A); w1(B); r2(B); w2(B); • r1(A); w1(A); r1(B); r2(A); w2(A); w1(B);r2(B); w2(B); • r1(A); w1(A); r1(B); r2(A); w1(B);w2(A); r2(B); w2(B); • r1(A); w1(A); r1(B); w1(B); r2(A); w2(A); r2(B); w2(B); Notes 09

  25. Conflict-serializability is a sufficient condition for serializabilityi.e., a conflict-serializable schedule is a serializable schedule. • Conflict-serializability is not required for a schedule to be serializable. • Schedulers in commercial systems generally use conflict-serializability when they need to guarantee serializability. Notes 09

  26. Precedence graph P(S) (S is schedule) Nodes: transactions in S Arcs: Ti  Tj whenever - pi(A), qj(A) are actions in S - pi(A) <S qj(A) - at least one of pi, qj is a write Notes 09

  27. Exercise: • What is P(S) forS = w3(A)w2(C)r1(A) w1(B) r1(C)w2(A) r4(A) w4(D) • Is S serializable? Notes 09

  28. Another Exercise: • What is P(S) forS = w1(A) r2(A) r3(A) w4(A) ? Notes 09

  29. Lemma S1, S2 conflict equivalent  P(S1)=P(S2) Notes 09

  30. Proof: Assume P(S1)  P(S2)  Ti: Ti  Tj in S1 and not in S2  S1 = …pi(A)... qj(A)… pi, qj S2 = …qj(A)…pi(A)... conflict  S1, S2 not conflict equivalent Lemma S1, S2 conflict equivalent  P(S1)=P(S2) Notes 09

  31. Counter example: S1=w1(A) r2(A) w2(B) r1(B)(cannot swap) S2=r2(A) w1(A) r1(B) w2(B) Note: P(S1)=P(S2)  S1, S2 conflict equivalent Notes 09

  32. Theorem P(S1) acyclic S1conflict serializable () Assume S1 is conflict serializable  Ss: Ss, S1 conflict equivalent  P(Ss)=P(S1)  P(S1) acyclic since P(Ss) is acyclic Notes 09

  33. Theorem P(S1) acyclic  S1 conflict serializable T1 T2 T3 T4 () Assume P(S1) is acyclic Transform S1 as follows: (1) Take T1 to be transaction with no incident arcs (2) Move all T1 actions to the front S1 = ……. qj(A)…….p1(A)….. (3) we now have S1 = < T1 actions ><... rest ...> (4) repeat above steps to serialize rest! Notes 09

  34. How to enforce serializable schedules? Option 1: run system, recording P(S); at end of day, check for P(S) cycles and declare if execution was good Notes 09

  35. How to enforce serializable schedules? Option 2:prevent P(S) cycles from occurring T1 T2 ….. Tn Scheduler DB Notes 09

  36. A locking protocol Two new actions: lock (exclusive): li (A) unlock: ui (A) T1 T2 lock table scheduler Notes 09

  37. Rule #1:Consistencyof transactions Ti: … li(A) … pi(A) … ui(A) ... 1. A transaction can only read or write an element if it previously wasgranted a lock on that element and hasn’t yet released the lock. 2. If a transaction locks an element, it must later unlock that element. Notes 09

  38. Rule #2Legality of schedules S = …….. li(A) ………... ui(A) ……... Locks must have their intended meaning: no twotransactions may have locked the same element without one having firstreleased the lock. no lj(A) Notes 09

  39. Exercise: • What schedules are legal?What transactions are consistent? S1 = l1(A)l1(B)r1(A)w1(B)l2(B)u1(A)u1(B) r2(B)w2(B)u2(B)l3(B)r3(B)u3(B) S2 = l1(A)r1(A)w1(B)u1(A)u1(B) l2(B)r2(B)w2(B)l3(B)r3(B)u3(B) S3 = l1(A)r1(A)u1(A)l1(B)w1(B)u1(B) l2(B)r2(B)w2(B)u2(B)l3(B)r3(B)u3(B) Notes 09

  40. Exercise: • What schedules are legal?What transactions are consistent? S1 = l1(A)l1(B)r1(A)w1(B)l2(B)u1(A)u1(B) r2(B)w2(B)u2(B)l3(B)r3(B)u3(B) S2 = l1(A)r1(A)w1(B)u1(A)u1(B) l2(B)r2(B)w2(B)l3(B)r3(B)u3(B) S3 = l1(A)r1(A)u1(A)l1(B)w1(B)u1(B) l2(B)r2(B)w2(B)u2(B)l3(B)r3(B)u3(B) Notes 09

  41. Schedule F(legal schedule of consistent transactions) A B T1 T2 25 25 l1(A);Read(A) A A+100;Write(A);u1(A) 125 l2(A);Read(A) A Ax2;Write(A);u2(A) 250 l2(B);Read(B) B Bx2;Write(B);u2(B) 50 l1(B);Read(B) B B+100;Write(B);u1(B) 150 250 150 Notes 09

  42. Rule #3Two phase locking (2PL)for transactions Ti = ……. li(A) ………... ui(A) ……... no unlocks no locks Notes 09

  43. # locks held by Ti Time Growing Shrinking Phase Phase Notes 09

  44. Schedule G T1 T2 l1(A);Read(A) A A+100;Write(A) l1(B);u1(A) l2(A);Read(A) A Ax2;Write(A);l2(B) delayed Notes 09

  45. Schedule G T1 T2 l1(A);Read(A) A A+100;Write(A) l1(B); u1(A) l2(A);Read(A) A Ax2;Write(A);l2(B) Read(B);B B+100 Write(B); u1(B) delayed Notes 09

  46. Schedule G T1 T2 l1(A);Read(A) A A+100;Write(A) l1(B); u1(A) l2(A);Read(A) A Ax2;Write(A);l2(B) Read(B);B B+100 Write(B); u1(B) l2(B); u2(A);Read(B) B Bx2;Write(B);u2(B); delayed Notes 09

  47. Schedule G A B T1 T2 25 25 l1(A);Read(A); A A+100 Write(A);l1(B);u1(A) 125 l2(A);Read(A) A Ax2;Write(A);u2(A) 250 l2(B);delayed Read(B); B B+100;Write(B);u1(B) 125 l2(B);Read(B) B Bx2;Write(B);u2(B) 250 250 Notes 09

  48. Schedule H (T2 reversed) T1 T2 l1(A); Read(A) l2(B);Read(B) A A+100;Write(A) B Bx2;Write(B) l1(B) l2(A) Deadlock !!! delayed delayed Notes 09

  49. Assume deadlocked transactions are rolled back • They have no effect • They do not appear in schedule E.g., Schedule H = This space intentionally left blank! Notes 09

  50. Deadlocks • Detection • Wait-for graph • Prevention • Resource ordering • Timeout • Wait-die • Wound-wait Notes 10

More Related