1 / 25

CS4432: Database Systems II

CS4432: Database Systems II. Lecture #26 Concurrency Control and Recovery. Professor Elke A. Rundensteiner. Concepts. Serial schedule : no interleaving of transactions S 1 , S 2 are conflict equivalent schedules if S 1 transforms into S 2 by swaps on non-conflicting actions.

mariko
Download Presentation

CS4432: Database Systems II

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. CS4432: Database Systems II Lecture #26 Concurrency Control and Recovery Professor Elke A. Rundensteiner

  2. Concepts Serial schedule: no interleaving of transactions S1, S2 are conflict equivalentschedules if S1 transforms into S2 by swaps on non-conflicting actions. A schedule is conflict serializable if it is conflict equivalent to some serial schedule.

  3. Example: Tj Ti Wj(A) ri(A) Commit Ti Abort Tj Concurrency control & recovery … … … … … …

  4. Example: Tj Ti Wj(A) ri(A) Commit Ti Abort Tj Concurrency control & recovery … … … dirty data … … …  Non-Persistent Commit (Bad!) 5

  5. Example: Tj Ti Wj(A) ri(A) Commit Ti Abort Tj Concurrency control & recovery … … … … … … Can be avoided by recoverable schedules  Problem : Non-Persistent Commit 6

  6. Example: Tj Ti Wj(A) ri(A) wi(B) Abort Tj [Commit Ti] Concurrency control & recovery … … … dirty data … … … 7

  7. Example: Tj Ti wj(A) ri(A) wi(B) Abort Tj [Commit Ti] Concurrency control & recovery … … … … … …  Cascading rollback 8

  8. Example: Tj Ti Wj(A) ri(A) wi(B) Abort Tj [Commit Ti] Concurrency control & recovery … … … … … … avoided by avoids-cascading- rollback (ACR) schedules  Cascading rollback 9

  9. Key Observation: Schedule is conflict serializable, Yet not “recoverable”. 10

  10. Need to make “final’ decision for each transaction: commit decision - system guarantees transaction will or has completed, no matter what (final effect will stick) abort decision - system guarantees transaction will or has been rolled back (has no effect) 11

  11. To model this, two new actions: Ci - transaction Ti commits Ai - transaction Ti aborts 12

  12. Back to example: “dirty read” Tj Ti Wj(A) ri(A) Cican wecommit here? ... ... ... ... 13

  13. Definition Ti reads from Tj in S (denoted as Tj STi) if (1) wj(A)<S ri(A) (2) aj <S ri(A) (< : does not precede) (3) If wj(A)<Swk(A)<Sri(A) then ak <S ri(A) 14

  14. Definition Intuition : A schedule S is recoverable, iftransactions only commit after all transactions they read from have already been committed first. Formal : Schedule S is recoverable if whenever Tj S Ti and j  i and Ci  S then Cj <S Ci

  15. Note: in transactions, reads and writes precede commit or abort  If Ci  Ti, then ri(A) < Ci wi(A) < Ci  If Ai  Ti, then ri(A) < Ai wi(A) < Ai Another rule: at most one Ci or Ai per transaction 16

  16. How to achieve recoverable schedules? 17

  17. With 2PL, hold write locks to commit (strict 2PL) Tj Ti Wj(A) Cj uj(A) ri(A) ... ... ... ... ... ... ... 18

  18. S is recoverable if each transaction commits only after all transactions from which it read have committed. Allows dirty read S avoids cascading rollback if each transaction may read only those values written by committed transactions. Disallows dirty read S is strict if each transaction may read and write only items previously written by committed transactions. 20

  19. Types of schedules: RC Avoids cascading rollback ST SERIAL ACR RC : recoverable ACR: avoids cascading rollback ST: strict 21

  20. Examples Recoverable: w1(A) w1(B) w2(A) r2(B)c1 c2 Avoids Cascading Rollback: w1(A) w1(B) w2(A) c1r2(B) c2 Strict: w1(A) w1(B) c1w2(A) r2(B) c2 Assumes w2(A) is done without reading 22

  21. Where are serializable schedules? RC Avoids cascading rollback ST SERIAL ACR 23

  22. Recoverable vs Serializable ? • Every STRICT schedule is serializable. • Every STRICT schedule is ACR (and thus recoverable).

  23. Recoverable vs Serializable ? • Recoverable and serializable: • w1(A) w1(B) w2(A) r2(B) c1 c2 • Recoverable and not serializable: • w2(A) w1(B) w1(A) r2(B) c1 c2 • Not recoverable yet serializable: • w1(A) w1(B) w2(A) r2(B) c2 c1 • Not recoverable and not serializable: • w2(A) w1(B) w1(A) r2(B) c2 c1

  24. 0. Start T1. • 1. L1(A) • 2. R1(A) • 3. W1(A) • 4. O1(A) • 5. Start T2. • 6. L2(C) • 7. L1(B) • 8. U1(A) • 9. L2(A) • 10. W1(B) • 11. Commit T1 • 12. R2(A) • 13. W2(C) • 14. U1(B) • 15. Commit T2 • 16. U2(A) • 17. U2(C)

  25. Basics of Concurrency Control and Recovery

More Related