1 / 25

Concurrency control

Concurrency control . Introduction. concurrency more than one transaction have access to data simultaneously part of transaction processing. Outline. motivation three concurrency problems criterion for correctness of concurrent transactions serialisability two-phase locking protocol

arne
Download Presentation

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. Concurrency control

  2. Introduction • concurrency • more than one transaction have access to data simultaneously • part of transaction processing

  3. Outline • motivation • three concurrency problems • criterion for correctness of concurrent transactions • serialisability • two-phase locking protocol • locking • problems • deadlocks

  4. Three concurrency problems • the lost update • the uncommitted dependency • the inconsistent analysis

  5. The lost update problem Transaction A time Transaction B RETRIEVE p t1 t2 RETRIEVE p UPDATE p t3 t4 UPDATE p

  6. The uncommitted dependency problem Transaction A time Transaction B t1 UPDATE p RETRIEVE p t2 t3 ROLLBACK

  7. The uncommitted dependency problem Transaction A time Transaction B t1 UPDATE p UPDATE p t2 t3 ROLLBACK

  8. The inconsistent analysis problem

  9. Issue • all these problems may lead to an inconsistent (incorrect) database • is there a criterion based on which to decide weather a certain set of transaction, if executed concurrently, leads to an incorrect database or not?

  10. Serialisability • criterion for correctness for concurrent execution of transactions: • the interleaved execution of a set of transactions is guaranteed to be correct if it is serialisable • correct  the DB is not in an inconsistent state • serialisability: an interleaved execution has the same result as some serial execution

  11. Terminology • schedule • any execution of a set of transactions • serial / interleaved schedule • equivalent schedules • produce the same result independent of the initial state of the DB

  12. Serialisable schedule

  13. Notes • the schedules described in the problem examples were not serialisable • neither A-then-B nor B-then-A • two different interleaved transactions might produce different results, yet both can be considered correct • example

  14. Two phase locking theorem • if all transactions obey the two phase locking protocolthen all possible interleaved schedules are serialisable

  15. Two phase locking protocol 1.before operating on an object a transaction must acquire a lock on that object 2.after releasing a lock a transaction must not go on to acquire any more locks • phase1 (growing): acquire locks (not simultaneously) • phase2 (shrinking): release locks (no further acquisitions allowed) • usually locks are released by the COMMIT or ROLLBACK operation • in practice • trade-off between “release lock early and acquire more locks” and the “two phase locking protocol”

  16. Locking • applicable to tuples • types • X, exclusive - write • S, shared - read • rules • compatibility matrix

  17. Compatibility matrix

  18. Data access protocol • retrieve tuple  acquire S lock (on that tuple) • update tuple  acquire X lock (on that tuple), or promote the S lock it holds (if it holds one) • implicit request • if request for lock is denied  transaction goes in wait state until the lock is released • livelock - first come first served • X locks are held until end of transaction (COMMIT or ROLLBACK) (two phase locking protocol)

  19. The uncommitted dependency problem : OK Transaction A time Transaction B t1 UPDATE p (X lock on p) RETRIEVE p t2 (request X lock on p) wait t3 COMMIT / ROLL.. wait (release X lock on p) resume RETRIEVE p t4 (acquire S lock on p)

  20. The lost update problem : dead-lock Transaction A time Transaction B RETRIEVE p t1 (acquire S lock on p) t2 RETRIEVE p (acquire S lock on p) UPDATE p t3 (request X lock on p denied) t4 UPDATE p wait (request X lock on p wait denied) wait wait

  21. Exercises • check what happens in • the other uncommitted problem • the inconsistent analysis problem • the inconsistent analysis problem - example from Date p. 399 (!there is a little mistake!)

  22. Locking • solves the three basic problems of concurrency • theorem • if all the transactions of a set S of transactions comply with the two phase locking protocol, then all their possible interleaved executions (schedules) are serialisable • however, not all schedules produce the same result • think of examples • introduces another problem: deadlock

  23. Deadlock Transaction A time Transaction B acquire LOCK p1 EXCLUSIVE t1 t2 acquire LOCK p2 EXCLUSIVE request LOCK p2 EXCLUSIVE t3 wait wait t4 request LOCK p1 EXCLUSIVE wait wait

  24. Deadlock • two or more transaction are waiting for the other to release a lock • in practice: usually two transactions • detect a deadlock • cycle in the wait-for graph, or • timing mechanism • break a deadlock • rollback a victim transaction • what happens to the victim?

  25. Further topics • two phase locking protocol - not feasible in practice (not efficient) • levels of isolation • degree of interference • intent locking • locking granularity • SQL support • no explicit locking facilities • it supports different isolation levels (with “locking behind the scenes”)

More Related