1 / 22

CS4432: Database Systems II

CS4432: Database Systems II. Concurrency Control Enforcing Serializability: Locking. Enforcing Serializable Schedules. DBMSs use a “ Scheduler ” that schedules the actions of transactions Transactions send their requests (R or W) to Scheduler The scheduler prevents the formation of cycles

thora
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 Concurrency Control Enforcing Serializability: Locking

  2. Enforcing Serializable Schedules • DBMSs use a “Scheduler” that schedules the actions of transactions • Transactions send their requests (R or W) to Scheduler • The scheduler prevents the formation of cycles • It grants permission to R or W only if no cycle will be formed

  3. Locking Protocol • “Scheduler” uses a locking protocol to enforce serializability • Two New actions • Lock (exclusive): li(A)  Transaction Ti locks item A • Unlock: Ui(A)  Transaction Ti unlocks (releases) item A lock table

  4. Rule #1: Well-Formed Transactions Ti: … li(A) … pi(A) … ui(A) ... Any action (R/W) must be after the lock (l) and before the unlock (u) Rule 1 is at the level of each transaction independent of the others

  5. Rule #2 Legal Scheduler S = …….. li(A) ………... ui(A) ……... no lj(A) No transaction Tj can lock item A that is already locked by another transaction Ti (Transaction Tj must wait until Ti releases its lock) Rule 2 is at the level of the complete schedule (Set of interleaving transactions)

  6. Schedule F: Let’s Add Some Locking! Does the locking mechanism working? Does it guarantee serializable schedule??

  7. Still Something is Missing… Still by applying the locks….results is not consistent !!!

  8. Rule #3 Two Phase Locking (2PL) Ti = ……. li(A) ………... ui(A) ……... No locks No unlocks • A transaction never start unlocks until it takes all its locks. • In other words: Take all locks before the first unlock Rule 3 is for each transaction…

  9. 2 Phase Locking Protocol # of locks held by a transaction Collect locks Release locks Growing Phase Shrinking Phase

  10. Schedule F : Does it follow 2PL ?  Both transactions violate 2PL

  11. Example 2PL: Schedule G Scheduler will deny this request (T2 will wait…) l2(B)

  12. Example 2PL: Schedule G Scheduler will deny this request (T2 will wait…) l2(B) T2 Continue processing At this point the scheduler can resume T2

  13. Scheduler Forced Schedule G to be Serializable

  14. Theorem • Any legal schedule S following the 2PL  Is a Conflict Serializable schedule Most DBMSs use two-phase locking (2PL) to enforce concurrency There is a room for improvement

  15. Improvement 1: Handling Deadlocks • 2PL protocol does not prevent deadlocks • Transactions waiting for each other indefinitely Deadlock situation l1(B) l2(A) T1 has to wait for T2 T2 has to wait for T1

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

  17. Deadlock Detection • Build Wait-For graph • Ti  Tj: means Ti is waiting for Tj • Use lock table structures • Built incrementally by the scheduler • When cycle found, rollback victim Select one among T1, T2, and T3 to kill (rollback)  A good choice is “the most recent one” Cycle (Deadlock)

  18. Deadlock Prevention: Timeout • If transaction waits more than L sec., roll it back! • Pros: Simple scheme • Cons: Hard to select L Check the other techniques in Textbook for your knowledge

  19. Improvement 2: Avoiding Cascade Rollback • 2PL protocol does not prevent dirty reads To avoid this situation, we use Strict Two-Phase Locking (S2PL) Now T2 is based on deleted transaction commit Rollback

  20. Strict 2PL (S2PL) • A transaction releases its lock only at commit or rollback time Commit or rollback event # of locks held by a transaction Collect locks Release locks Growing Phase Shrinking Phase

  21. S2PL: Example Unlock A has to wait until the end (even if T1 is done processing A) U1(A)

  22. Summary of Concurrency Control • How to manage multiple transactions at the same time  and still ensure consistency • Schedules (order of actions) • Serial, Conflict Serializable, Legal • Precedence Graphs for Conflict Serializable • Locking Protocol • 2PL , S2PL, Handling of deadlocks

More Related