1 / 105

Lecture 5: Transactions Concurrency Control

Lecture 5: Transactions Concurrency Control. Wednesday October 26 th , 2011. Announcement. HW4: posted, due on Monday. Concurrency Control. Problem: Many transactions execute concurrently Their updates to the database may interfere Scheduler = needs to schedule transactions.

connie
Download Presentation

Lecture 5: Transactions 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. Lecture 5: Transactions Concurrency Control Wednesday October 26th, 2011 Dan Suciu -- CSEP544 Fall 2011

  2. Announcement • HW4: posted, due on Monday Dan Suciu -- CSEP544 Fall 2011

  3. Concurrency Control Problem: • Many transactions execute concurrently • Their updates to the database may interfere Scheduler = needs to schedule transactions Dan Suciu -- CSEP544 Fall 2011

  4. The Problem • Multiple concurrent transactions T1, T2, … • They read/write common elements A1, A2, … • How can we prevent unwanted interference ? The SCHEDULER is responsible for that Dan Suciu -- CSEP544 Fall 2011

  5. Conflicts • Write-Read – WR • Read-Write – RW • Write-Write – WW Note: “conflict” means you can’t swap them

  6. Lost Update T1: READ(A) T1: A := A+5 T1: WRITE(A) T2: READ(A); T2: A := A*2 T2: WRITE(A); RW conflict and WW conflict

  7. Inconsistent Reads T2: READ(A); T2: READ(B); T1: A := 20; B := 20; T1: WRITE(A) T1: WRITE(B) WR conflict and RW conflict

  8. Dirty Read T1: WRITE(A) T1: ABORT T2: READ(A) WR conflict

  9. Unrepeatable Read T2: READ(A); T2: READ(A); T1: WRITE(A) RW conflict and WR conflict

  10. Schedules A schedule is a sequenceof interleaved actions from all transactions Dan Suciu -- CSEP544 Fall 2011

  11. Example Dan Suciu -- CSEP544 Fall 2011

  12. A Serial Schedule Dan Suciu -- CSEP544 Fall 2011

  13. Serializable Schedule A schedule is serializable if it is equivalent to a serial schedule Dan Suciu -- CSEP544 Fall 2011

  14. A Serializable Schedule This is NOT a serial schedule, but is serializable

  15. A Non-Serializable Schedule Dan Suciu -- CSEP544 Fall 2011

  16. ASerializableSchedule Schedule is serializablebecause t=t+100 ands=s+200 commute We don’t expect the scheduler to produce this schedule Dan Suciu -- CSEP544 Fall 2011

  17. Ignoring Details • Assume worst case updates: • We never commute actions done by transactions • As a consequence, we only care about reads and writes • Transaction = sequence of R(A)’s and W(A)’s T1: r1(A); w1(A); r1(B); w1(B) T2: r2(A); w2(A); r2(B); w2(B) Dan Suciu -- CSEP544 Fall 2011

  18. Conflicts Two actions by same transaction Ti: ri(X); wi(Y) Two writes by Ti, Tj to same element wi(X); wj(X) wi(X); rj(X) Read/write by Ti, Tj to same element ri(X); wj(X) A “conflict” means: you can’t swap the two operations Dan Suciu -- CSEP544 Fall 2011

  19. Conflict Serializability • A schedule is conflict serializable if it can be transformed into a serial schedule by a series of swappings of adjacent non-conflicting actions Example: r1(A); w1(A); r2(A); w2(A); r1(B); w1(B); r2(B); w2(B) r1(A); w1(A); r1(B); w1(B); r2(A); w2(A); r2(B); w2(B)

  20. The Precedence Graph Test Is a schedule conflict-serializable ? Simple test: • Build a graph of all transactions Ti • Edge from Ti to Tj if Ti makes an action that conflicts with one of Tj and comes first • The test: if the graph has no cycles, then it is conflict serializable ! Dan Suciu -- CSEP544 Fall 2011

  21. Example 1 r2(A); r1(B); w2(A); r3(A); w1(B); w3(A); r2(B); w2(B) 1 2 3 Dan Suciu -- CSEP544 Fall 2011

  22. Example 1 r2(A); r1(B); w2(A); r3(A); w1(B); w3(A); r2(B); w2(B) B A 1 2 3 This schedule is conflict-serializable Dan Suciu -- CSEP544 Fall 2011

  23. Example 2 r2(A); r1(B); w2(A); r2(B); r3(A); w1(B); w3(A); w2(B) 1 2 3 Dan Suciu -- CSEP544 Fall 2011

  24. Example 2 r2(A); r1(B); w2(A); r2(B); r3(A); w1(B); w3(A); w2(B) B A 1 2 3 B This schedule is NOT conflict-serializable Dan Suciu -- CSEP544 Fall 2011

  25. View Equivalence • A serializable schedule need not be conflict serializable, even under the “worst case update” assumption w1(X); w2(X); w2(Y); w1(Y); w3(Y); Lost write w1(X); w1(Y); w2(X); w2(Y); w3(Y); Equivalent, but can’t swap Dan Suciu -- CSEP544 Fall 2011

  26. View Equivalent Lost Serializable, but not conflict serializable Dan Suciu -- CSEP544 Fall 2011

  27. View Equivalence Two schedules S, S’ are view equivalent if: • If T reads an initial value of A in S, then T also reads the initial value of A in S’ • If T reads a value of A written by T’ in S, then T also reads a value of A written by T’ in S’ • If T writes the final value of A in S, then it writes the final value of A in S’ Dan Suciu -- CSEP544 Fall 2011

  28. View-Serializability A schedule is view serializable if it is view equivalent to a serial schedule Remark: • If a schedule is conflict serializable, then it is also view serializable • But not vice versa Dan Suciu -- CSEP544 Fall 2011

  29. Schedules with Aborted Transactions • When a transaction aborts, the recovery manager undoes its updates • But some of its updates may have affected other transactions ! Dan Suciu -- CSEP544 Fall 2011

  30. Schedules with Aborted Transactions Cannot abort T1 because cannot undo T2 Dan Suciu -- CSEP544 Fall 2011

  31. Recoverable Schedules A schedule is recoverable if: • It is conflict-serializable, and • Whenever a transaction T commits, all transactions who have written elements read by T have already committed Dan Suciu -- CSEP544 Fall 2011

  32. Recoverable Schedules Nonrecoverable Recoverable

  33. Cascading Aborts • If a transaction T aborts, then we need to abort any other transaction T’ that has read an element written by T • A schedule is said to avoid cascading aborts if whenever a transaction read an element, the transaction that has last written it has already committed. Dan Suciu -- CSEP544 Fall 2011

  34. Avoiding Cascading Aborts With cascading aborts Without cascading aborts

  35. Review of Schedules Serializability Recoverability Recoverable Avoiding cascading deletes • Serial • Serializable • Conflict serializable • View serializable Dan Suciu -- CSEP544 Fall 2011

  36. Where We Are (1/2) Transactions: • Recovery: • Have discussed simple UNDO/REDO recovery last lecture • Concurrency control: • Have discussed serializability last lecture • Will discuss lock-based scheduler today Dan Suciu -- CSEP544 Fall 2011

  37. Where We Are (2/2) Also today and next time: • Weak Isolation Levels in SQL • Advanced recovery • ARIES • Advanced concurrency control • Timestamp based algorithms, including snapshot isolation Dan Suciu -- CSEP544 Fall 2011

  38. Review Questions Query Answering Using Views, by Halevy • Q1: define the problem • Q2: how is this used for physical data independence ? • Q3: what is data integration and what is its connection to query answering using views ? Dan Suciu -- CSEP544 Fall 2011

  39. Review Questions • What is a schedule ? • What is a serializableschedule ? • What is a conflict ? • What is a conflict-serializable schedule ? • What is a view-serializable schedule ? • What is a recoverable schedule ? • When does a schedule avoid cascading aborts ? Dan Suciu -- CSEP544 Fall 2011

  40. Scheduler • The scheduler is the module that schedules the transaction’s actions, ensuring serializability • Two main approaches • Pessimistic scheduler: uses locks • Optimistic scheduler: time stamps, validation Dan Suciu -- CSEP544 Fall 2011

  41. Locking Scheduler Simple idea: • Each element has a unique lock • Each transaction must first acquire the lock before reading/writing that element • If the lock is taken by another transaction, then wait • The transaction must release the lock(s) Dan Suciu -- CSEP544 Fall 2011

  42. Notation li(A) = transaction Ti acquires lock for element A ui(A) = transaction Ti releases lock for element A Dan Suciu -- CSEP544 Fall 2011

  43. A Non-Serializable Schedule Dan Suciu -- CSEP544 Fall 2011

  44. Example Scheduler has ensured a conflict-serializable schedule

  45. But… Locks did not enforce conflict-serializability !!! What’s wrong ?

  46. Two Phase Locking (2PL) The 2PL rule: • In every transaction, all lock requests must preceed all unlock requests • This ensures conflict serializability ! (will prove this shortly) Dan Suciu -- CSEP544 Fall 2011

  47. Example: 2PL transactions Now it is conflict-serializable

  48. Two Phase Locking (2PL) Theorem: 2PL ensures conflict serializability Proof. Suppose not: thenthere exists a cyclein the precedence graph. Then there is thefollowing temporalcycle in the schedule: U1(A)L2(A) L2(A)U2(B) U2(B)L3(B) L3(B)U3(C) U3(C)L1(C) L1(C)U1(A) C T1 T3 B A Contradiction T2

  49. A New Problem: Non-recoverable Schedule

  50. What about Aborts? • 2PL enforces conflict-serializable schedules • But does not enforce recoverable schedules Dan Suciu -- CSEP544 Fall 2011

More Related