1 / 24

Advanced Concurrency Control Mechanisms in Database Systems

This document explores two advanced concurrency control techniques used in database systems: tree-based concurrency control and validation-based concurrency control. The tree-based protocol ensures exclusive locks follow a parent-child relationship, facilitating structured access. The validation method employs a three-phase process to ensure transactions are serializable and prevents conflicts. Both methods are critical for maintaining consistency and performance in database operations, particularly when dealing with high contention. The document provides examples and rules for effective implementation.

vernon
Download Presentation

Advanced Concurrency Control Mechanisms in Database Systems

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. CS505: Intermediate Topics in Database Systems Jinze Liu

  2. Next: • Tree-based concurrency control • Validation concurrency control

  3. T1 lock T1 lock T1 lock • all objects accessed through root, following pointers Example A B C D E F  can we release A lock if we no longer need A??

  4. T1 lock T1 lock T1 lock T1 lock Idea: traverse like “Monkey Bars” A B C D E F

  5. Why does this work? • Assume all Ti start at root; exclusive lock • Ti Tj  Ti locks root beforeTj • Actually works if we don’t always start at root Root Q Ti  Tj

  6. Rules: tree protocol (exclusive locks) (1) First lock by Ti may be on any item (2) After that, item Q can be locked by Ti only if parent(Q) locked by Ti (3) Items may be unlocked at any time (4) After Ti unlocks Q, it cannot relock Q

  7. Tree-like protocols are used typically for B-tree concurrency control E.g.,during insert, do not release parent lock, until you are certain child does not have to split Root

  8. Tree Protocol with Shared Locks • Rules for shared & exclusive locks? T1 S lock(released) A T1 X lock (released) B C T1 S lock (held) D E F T1 X lock (will get)

  9. Tree Protocol with Shared Locks • Rules for shared & exclusive locks? T1 S lock(released) A T1 X lock (released) B C T1 S lock (held) T2 reads: • B modified by T1 • F not yet modified by T1 D E F T1 X lock (will get)

  10. Tree Protocol with Shared Locks • Need more restrictive protocol • Will this work?? • Once T1 locks one object in X mode,all further locks down the tree must bein X mode

  11. Validation based concurrency control Transactions have 3 phases: (1) Read • all DB values read • writes to temporary storage • no locking (2) Validate • check if schedule so far is serializable (3) Write • if validate ok, write to DB

  12. Key idea • Make validation atomic • If T1, T2, T3, … is validation order, then resulting schedule will be conflict equivalent to Ss = T1 T2 T3...

  13. To implement validation, system keeps two sets: • FIN = transactions that have finished phase 3 (and are all done) • VAL = transactions that have successfully finished phase 2 (validation)

  14. Example of what validation must prevent: RS(T2)={B} RS(T3)={A,B} WS(T2)={B,D} WS(T3)={C} T2 validated T3 validated T2 start T3 start time

  15. Example of what validation must prevent: allow RS(T2)={B} RS(T3)={A,B} WS(T2)={B,D} WS(T3)={C} T2 validated T3 validated T2 start T3 start T3 start T2 finish phase 3 time

  16. BAD: w3(D) w2(D) Another thing validation must prevent: RS(T2)={A} RS(T3)={A,B} WS(T2)={D,E} WS(T3)={C,D} T2 validated T3 validated finish T2 time

  17. Another thing validation must prevent: allow RS(T2)={A} RS(T3)={A,B} WS(T2)={D,E} WS(T3)={C,D} T2 validated T3 validated finish T2 finish T2 time

  18. Validation rules for Tj: (1) When Tj starts phase 1: ignore(Tj)  FIN (2) at Tj Validation: if check (Tj) then [ VAL  VAL U {Tj}; do write phase; FIN FIN U {Tj} ]

  19. Check (Tj): For Ti  VAL - IGNORE (Tj) DO IF [ WS(Ti)  RS(Tj)   OR Ti  FIN ] THEN RETURN false; RETURN true;

  20. Improving Check(Tj) For Ti  VAL - IGNORE (Tj) DO IF [ WS(Ti)  RS(Tj)   OR (Ti  FIN AND WS(Ti)  WS(Tj)  )] THEN RETURN false; RETURN true;

  21. Exercise: start validate finish U: RS(U)={B} WS(U)={D} W: RS(W)={A,D} WS(W)={A,C} V: RS(V)={B} WS(V)={D,E} T: RS(T)={A,B} WS(T)={A,C}

  22. Validation (also called optimistic concurrency control) is useful in some cases: - Conflicts rare - System resources plentiful - Have real time constraints

  23. Summary Have studied C.C. mechanisms used in practice - 2 PL - Multiple granularity - Tree (index) protocols - Validation

  24. Summary • Concurrency control • Serial schedule: no interleaving • Conflict-serializable schedule: no cycles in the precedence graph; equivalent to a serial schedule • 2PL: guarantees a conflict-serializable schedule • Strict 2PL: also guarantees recoverability • Recovery: undo/redo logging with fuzzy checkpointing • Normal operation: write-ahead logging, no force, steal • Recovery: first redo (forward), and then undo (backword)

More Related