1 / 32

Advanced Concurrency Control Techniques in Database Management Systems

Explore protocols, lock types, two-phase locking, deadlock prevention, resolving schemes, and their impact on concurrency control in DBMS. Learn about binary locks, multiple-mode locks, and rigorous 2PL for serializability.

imckee
Download Presentation

Advanced Concurrency Control Techniques in Database Management 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. 4. Concurrency control techniques Protocol = Set of rules that guarantee serializability of concurrent transactions,without having to check it. Example protocols: • Locking • Timestamping • Multiversioning (skipped here) • Optimistic concurrency control AdvDB-4 J. Teuhola 2015

  2. 4.1. Concurrency control based on locking Lock = variable associated with a database unit (‘item’, ‘granule’, e.g. page) Data for lock implementation: • Identifier of data unit to be locked • Identifier of holding transaction • Queue of waiting transactions Lock manager: DBMS module that keeps track of locks and maintains queues AdvDB-4 J. Teuhola 2015

  3. Binary locks Operation sequence:lock(X) ... read(X) … write(X) ... unlock(X) • X is reserved for exclusive use of the locking transaction. • If X is locked by transaction T1, then T2(which also needs X) is forced to wait. AdvDB-4 J. Teuhola 2015

  4. Rules for binary locks (1) Lock before the first read(X) / write(X). (2) Unlock after the last read(X) / write(X). (3) No lock(X) if X is already locked. (4) Unlock(X) allowed only for the holder oflock(X) Drawback of binary locks: • They prevent concurrent reading. AdvDB-4 J. Teuhola 2015

  5. Multiple-mode locks • Read-lock = shared lockAny number of transactions may hold. • Write-lock = exclusive lockOnly a single write-lock(X) is allowed to holdat a time; even a simultaneous read_lock(X) is forbidden. AdvDB-4 J. Teuhola 2015

  6. Order of lock/unlock attempts within a given transaction: (1) Read-lock(X) or write-lock(X) before read(X). (2) Write-lock(X) before write(X). (3) Unlock(X) after the last read(X) / write(X). (4) No read-lock(X) if the transaction already holds a read-lock(X) or write-lock(X). (5) No write-lock(X) if the transaction already holds a read-lock(X) or write-lock(X). (6) Unlock(X) only if the transaction holds a read- lock(X) or write-lock(X). AdvDB-4 J. Teuhola 2015

  7. Relaxations for locking rules Upgrade: • Read-lock(X) raised to write-lock(X)when updating starts. Downgrade: • Write-lock(X) lowered to read-lock(X)after all the updates. AdvDB-4 J. Teuhola 2015

  8. Locking vs. serializability • Locking does not guarantee serializabilityas such; it must be applied properly. • An additional protocol is needed. AdvDB-4 J. Teuhola 2015

  9. Two-phase locking (2PL) protocol • The most common in practice • Basic 2PL: All locks before the first unlock • Upgrading in the growing phase • Downgrading in the shrinking phase But: 2PL reduces the degree of concurrency Theorem: 2PL guarantees serializability AdvDB-4 J. Teuhola 2015

  10. Conservative (static) 2PL • Lock all required items at the start,i.e. pre-declare the read/write sets. • If any lock attempt fails, then all of them fail, and the transaction waits. • Difficulty: How to know, before execution, the data units to be read/written? • Advantage:Deadlock-free, because it makes no gradual reservation of locks. AdvDB-4 J. Teuhola 2015

  11. Strict 2PL • Releases all write locks only at commit time. • Guarantees strict schedules, i.e. no cascading rollbacks. • Not deadlock-free. Strict & conservative: • Deadlock-free, no cascading rollback • But: low concurrency Rigorous 2PL: Release all locks at commit time AdvDB-4 J. Teuhola 2015

  12. Deadlock prevention/resolving schemes • Conservative: not very practical. • Abort some transaction: • Lock-waiter • Lock-holder • Abortion can be based on timestamps of transaction starting times. AdvDB-4 J. Teuhola 2015

  13. Resolving deadlocks (1) Wait-die: • An older transaction is allowed to wait. • If a younger transaction is blocked by an older, abort the younger and restart it with the same timestamp as before. Effect: The younger transaction gets older and older, until it is not aborted, anymore. AdvDB-4 J. Teuhola 2015

  14. Resolving deadlocks (cont.) (2) Wound-wait: • A younger transaction is allowed to wait for a lock to be released. • If an older transaction is blocked by a younger, the younger is aborted and re-started with the same timestamp as before. Drawbacks of (1), (2): unnecessary aborts. AdvDB-4 J. Teuhola 2015

  15. Resolving deadlocks (cont.) (3) No waiting: • No timestamps • Abort when blocked and restart • Too many abortions (even more than in (1), (2)) (4) Cautious waiting: • If Ti tries to lock X, locked by Tj, and Tj is not blocked, then Ti is allowed to wait; otherwise abort and restart Ti. • No cycles of blockings can be created AdvDB-4 J. Teuhola 2015

  16. Resolving deadlocks (cont.) (5) Timeout: • Abort the transaction if the waiting time exceeds a threshold. • Too many aborts may happen when the load is high. (Waiting times may grow exponentially with respect to the number of concurrent transactions.) AdvDB-4 J. Teuhola 2015

  17. Resolving deadlocks (cont.) (6) Deadlock detection: • Maintain a wait-for graph of transactions. • A cycle in the graph means deadlock. • Cyclicity tests at certain intervals.(Method: depth-first search in the wait-for graph and check for back-edges) • Victim selection for abortion:E.g. the youngest, but prevent cyclic abort & restart (by increasing priority,or keeping the original starting time). AdvDB-4 J. Teuhola 2015

  18. Livelock • Indefinite waiting time for some transaction while others execute. • Not deadlock. • The reason is an unfair waiting scheme. • Fair: FCFS = First-Come-First-Served. • Priority system: Increase priority for waiting transactions (typical in operating systems). AdvDB-4 J. Teuhola 2015

  19. Starvation • Same victim repeatedly in deadlock detection. • Wait-die and wound-wait avoid starvation because the starting time of the aborted transaction is not changed, and thereforethe old transactions are favoured. AdvDB-4 J. Teuhola 2015

  20. 4.2. Concurrency control based on timestamp ordering • Timestamp: • Starting time of a transaction • Identifies the transaction uniquely • Timestamp order determines the corres-ponding serial schedule. • For each active database unit X maintain: • Read_TS(X) = Largest timestamp of readers • Write_TS(X) = Largest timestamp of writers • Conflicting operations must be in timestamp order of the related transactions. AdvDB-4 J. Teuhola 2015

  21. Rules for timestamp ordering (1) Write(X) by T: If read_TS(X)>TS(T) or write_TS(X)>TS(T) then abort and roll back T,else write(X) and set write_TS(X)  TS(T) (2) Read(X) by T: If write_TS(X) > TS(T)then abort and roll back T, else read(X) and set read_TS(X)  max(read_TS(X),TS(T)) AdvDB-4 J. Teuhola 2015

  22. Notes on timestamp ordering Interpretation: • If two conflicting operations are in the wrong order, then reject the transaction related tothe latter. Drawbacks: • Does not prevent cascading rollback. • Danger of starvation AdvDB-4 J. Teuhola 2015

  23. Thomas’s write rule Write(X) by T: • If read_TS(X) > TS(T) then abort T • If write_TS(X) > TS(T)then do not write but continue,else write(X) and set write_TS(X) TS(T) Idea: Value X written by T would be overwritten, anyway, without T reading it inbetween.(We know that T did not read X; otherwise T would have been aborted by the first rule) AdvDB-4 J. Teuhola 2015

  24. Timestamp ordering (TO)compared to 2PL • Neither is always better than the other. • Neither allows all possible serializable schedules. • No deadlock for TO, but starvation (cyclic restart) is possible. • TO does not ensure recoverable schedules as such; additional control of commit order is required. • TO allows dirty reads. AdvDB-4 J. Teuhola 2015

  25. Strict timestamp ordering • If T wants to read/write X andTS(T) > write_TS(X) then delayT until transaction T’ that wrote X has committed. • Recoverable, no dirty reads, no cascading rollback. • Resembles locking but does not cause deadlocks, because waitings occur in timestamp order. AdvDB-4 J. Teuhola 2015

  26. 4.3. Optimistic (= validation) concurrency control (OCC) During execution: • No locks • Transactions update their local copies ofdata items (in main memory or disk). After execution: • Validation phase: Check if updates violate serializability. If not, update the databasefrom local copies, otherwise abort & restart. AdvDB-4 J. Teuhola 2015

  27. Phases of OCC • Read phase: Read from the database,update local copies of items. • Validation phase: Check serializability. • Write phase: Apply updates to the database. Idea: All checks at once (in validation) Assumption: Little interference among transactions AdvDB-4 J. Teuhola 2015

  28. Rules of OCC • Maintain read_set and write_set of transactions. • Maintain start and end times of phases. Validation for Ti : • No interference with committed transactions • No interference with transactions currently in validation phase. AdvDB-4 J. Teuhola 2015

  29. Correctness in OCC When checking the correctness of transaction Ti , one of the following should hold for any Tj which is committed or being validated: (a) Tj completes its write phase beforeTi starts its read phase. (b) Ti starts its write phase after Tj completes itswrite phase andread_set(Ti)  wr_set(Tj) =  (c) Tj completes its read phase before Ti completesits read phase and read_set(Ti)  wr_set(Tj) = and wr_set(Ti)  wr_set(Tj) =  AdvDB-4 J. Teuhola 2015

  30. 4.4. Concurrency control for indexes Problem with 2-phase locking on B+-trees: • An exclusive lock on the path from root to leaf blocks out all other users of the index. Conservative approach for insert: • Lock a node exclusively at visit, but release itafter noticing that its child is non-full. Optimistic approach for insert: • Exclusive lock only at split propagation. AdvDB-4 J. Teuhola 2015

  31. 4.5. Other concurrency issues Phantom problem: • A record to be inserted can create a conflictwith the read/write set of another transaction,but it cannot be detected before the insert. Solutions: (a) Index locking: Lock all entries accessible from the index. (b) Predicate locking: Lock all records satisfying a given predicate. AdvDB-4 J. Teuhola 2015

  32. Other concurrency issues (cont.) Interactivity problem: • The dependence between two items may only exist in the head of the user: • Uncommitted transaction writes upon the terminal • The user types his input based on that value. Solution: Postpone output to the screen until commit; not very practical! AdvDB-4 J. Teuhola 2015

More Related