Download
cs216 advanced database systems n.
Skip this Video
Loading SlideShow in 5 Seconds..
CS216 Advanced Database Systems PowerPoint Presentation
Download Presentation
CS216 Advanced Database Systems

CS216 Advanced Database Systems

155 Vues Download Presentation
Télécharger la présentation

CS216 Advanced Database Systems

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. CS216 Advanced Database Systems Notes 11: Concurrency Control Shivnath Babu

  2. Transaction • Programming abstraction • Implement real-world transactions • Banking transaction • Airline reservation

  3. Consistent State Consistent State Transaction: Programmer’s Role Transaction

  4. Transaction: System’s Role • Atomicity • All changes of the transaction recorded or none at all • Durability • All future transactions see the changes made by this transaction if it completes • Isolation • Net effect as if the transaction executed in isolation

  5. Transaction: States Abort Begin Run Commit

  6. Transactions • Historical note: • Turing Award for Transaction concept • Jim Gray (1998) • Interesting reading: Transaction Concept: Virtues and Limitationsby Jim Gray http://www.hpl.hp.com/techreports/tandem/TR-81.3.pdf

  7. Transaction: Programmer’s View See Section 8.6 of the textbook

  8. Context • Last class: • Ensure atomicity in presence of failures • Next few classes: • Ensure Isolation during concurrency

  9. Issues with Concurrency: Example Bank database: 3 Accounts A = 500 AccountBalances B = 500 C = 500 Property: A + B + C = 1500 Money does not leave the system

  10. Issues with Concurrency: Example Transaction T1: Transfer 100 from A to B A = 500, B = 500, C = 500 Read (A, t) t = t - 100 Write (A, t) Read (B, t) t = t + 100 Write (B, t) A = 400, B = 600, C = 500

  11. Issues with Concurrency: Example Transaction T2: Transfer 100 from A to C Read (A, s) s = s - 100 Write (A, s) Read (C, s) s = s + 100 Write (C, s)

  12. Transaction T1 Transaction T2 A B C 500 500 500 Read (A, t) t = t - 100 Read (A, s) s = s - 100 Write (A, s) 400 500 500 Write (A, t) 400 500 500 Read (B, t) t = t + 100 400 600 500 Write (B, t) Read (C, s) s = s + 100 Write (C, s) 400 600 600 400 + 600 + 600 = 1600

  13. Transaction T1 Transaction T2 A B C 500 500 500 Read (A, t) t = t - 100 400 500 500 Write (A, t) Read (A, s) s = s - 100 Write (A, s) 300 500 500 Read (B, t) t = t + 100 300 600 500 Write (B, t) Read (C, s) s = s + 100 Write (C, s) 300 600 600 300 + 600 + 600 = 1500

  14. Terminology • Schedule: • The exact sequence of (relevant) actions of one or more transactions

  15. Problems • Which schedules are “correct”? • Mathematical characterization • How to build a system that allows only “correct” schedules? • Efficient procedure to enforce correctness

  16. Correct Schedules: Serializability • Initial database state is consistent • Transaction: • consistent state  consistent state • Serial execution of transactions: • Initial state  consistent state • Serializable schedule: • A schedule equivalent to a serial schedule • Always “correct”

  17. Serial Schedule A B C 500 500 500 Read (A, t) t = t - 100 T1 Write (A, t) Read (B, t) t = t + 100 Write (B, t) 400 600 500 Read (A, s) s = s - 100 Write (A, s) Read (C, s) T2 s = s + 100 Write (C, s) 300 600 600 300 + 600 + 600 = 1500

  18. Serial Schedule A B C Read (A, s) 500 500 500 s = s - 100 Write (A, s) T2 Read (C, s) s = s + 100 Write (C, s) 400 500 600 Read (A, t) t = t - 100 Write (A, t) T1 Read (B, t) t = t + 100 Write (B, t) 300 600 600 300 + 600 + 600 = 1500

  19. Sn S0 S1 S2 Serial Schedule T1 Tn T2 Consistent States

  20. Is this Serializable? Read (A, t) t = t - 100 Write (A, t) Read (A, s) s = s - 100 Write (A, s) Read (B, t) t = t + 100 Write (B, t) Read (C, s) s = s + 100 Write (C, s) Transaction T1 Transaction T2

  21. Equivalent Serial Schedule Read (A, t) t = t - 100 Write (A, t) Read (B, t) t = t + 100 Write (B, t) Read (A, s) s = s - 100 Write (A, s) Read (C, s) s = s + 100 Write (C, s) Transaction T1 Transaction T2

  22. Is this Serializable? Read (A, t) t = t - 100 Read (A, s) s = s - 100 Write (A, s) Write (A, t) Read (B, t) No. In fact, it leadsto inconsistent state t = t + 100 Write (B, t) Read (C, s) s = s + 100 Write (C, s) Transaction T1 Transaction T2

  23. Is this Serializable? Read (A, t) t = t - 100 Read (A, s) 0 s = s - 100 Write (A, s) Write (A, t) Read (B, t) t = t + 100 Write (B, t) Read (C, s) s = s + 100 0 Write (C, s) Transaction T1 Transaction T2

  24. Is this Serializable? Read (A, t) t = t - 100 Read (A, s) s = s - 0 Write (A, s) Write (A, t) Yes, T2 is no-op Read (B, t) t = t + 100 Write (B, t) Read (C, s) s = s + 0 Write (C, s) Transaction T1 Transaction T2

  25. Serializable Schedule Read (A, t) t = t - 100 Read (A, s) s = s - 0 Write (A, s) Write (A, t) Serializability depends on code details Read (B, t) t = t + 100 Write (B, t) Read (C, s) s = s + 0 Write (C, s) Transaction T1 Transaction T2

  26. Serializable Schedule Read (A, t) t = t - 100 Write (A, t) Read (A, s) s = s - 100 Write (A, s) Read (B, t) Still Serializable! t = t + 100 Write (B, t) Read (C, s) s = s + 100 Write (C, s) Transaction T1 Transaction T2

  27. Serializability • General Serializability: • Hard to determine • Goal: weaker serializability • Determined from database operations alone • Database Operations: • Reads, Writes, Inserts, …

  28. Simpler Notation r (X) Transaction T reads X T w (X) Transaction T writes X T

  29. What is X in r (X)? • X could be any component of a database: • Attribute of a tuple • Tuple • Block in which a tuple resides • A relation • …

  30. New Notation: Example Schedule r1(A) w1(A) r2(A) w2(A) r1(B) w1(B) r2(B) w2(B) time

  31. Conflict Serializability • Weaker notion of serializability • Depends only on reads and writes

  32. Conflict Serializability Serializable Schedules ConflictSerializableSchedules

  33. Conflict Serializable Schedule Transformations: swap non-conflicting actions S S1 S2 Sn Conflict SerializableSchedule Serial Schedule

  34. Transformation: Example 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)

  35. Non-Conflicting Actions Two actions are non-conflicting if whenever theyoccur consecutively in a schedule, swapping themdoes not affect the final state produced by theschedule. Otherwise, they are conflicting.

  36. Conflicting or Non-Conflicting? (Work on paper: Example 1)

  37. Conflicting Actions: General Rules • Two actions of the same transaction conflict: • r1(A) w1(B) • r1(A) r1(B) • Two actions over the same database element conflict, if one of them is a write • r1(A) w2(A) • w1(A) w2(A)

  38. Conflict Serializability Examples (Work on paper: Example 2 and 3)

  39. Testing Conflict Serializability • Construct precedence graph G for given schedule S • S is conflict-serializable iff G is acyclic

  40. Graph Theory 101 Directed Graph: Nodes

  41. Graph Theory 101 Directed Graph: Edges

  42. Graph Theory 101 Directed Graph: Cycle

  43. Graph Theory 101 Directed Graph: Not a cycle

  44. Graph Theory 101 Acyclic Graph: A graph with no cycles

  45. Graph Theory 101 Acyclic Graph:

  46. Testing Conflict Serializability • Construct precedence graph G for given schedule S • S is conflict-serializable iff G is acyclic

  47. Precedence Graph • Precedence graph for schedule S: • Nodes: Transactions in S • Edges: Ti → Tj whenever • S: … ri (X) … wj (X) … • S: … wi (X) … rj (X) … • S: … wi(X) … wj (X) … Note: not necessarily consecutive

  48. Precedence Graph • Ti → Tj whenever: • There is an action of Ti that occurs before a conflicting action of Tj.

  49. Precedence Graph Example (Work on paper: Example 4)

  50. Testing Conflict Serializability • Construct precedence graph G for given schedule S • S is conflict-serializable iff G is acyclic