1 / 41

CS4432: Database Systems II

CS4432: Database Systems II. Concurrency Control with Recovery. Concurrency control & recovery. Example: T i T o W i (A) r j (A) Commit T o Abort T i. …. …. …. …. …. …. Cascading rollback ( But already committed!). Note : Schedule is conflict serializable,

elaine-wade
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 with Recovery concurrency control

  2. Concurrency control & recovery Example: Ti To Wi(A) rj(A) Commit To Abort Ti … … … … … … • Cascading rollback ( But already committed!) concurrency control

  3. Note : Schedule is conflict serializable, But schedule is not recoverable. concurrency control

  4. Need to make “final’ decision for each transaction: • commit decision - system guarantees transaction will or has completed, no matter what (Ci) • abort decision - system guarantees transaction will or has been rolled back (has no effect) (Ai) concurrency control

  5. Back to example: Ti Tj Wi(A) rj(A) Cjcan wecommit here? ... ... ... ... concurrency control

  6. Definition Ti read from Tj in S (Tj STj) if • wj(A)<S ri(A) • aj <S ri(A) (< : does not precede) (3) If wj(A)<Swk(A)<Sri(A) then ak <S ri(A) concurrency control

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

  8. How to achieve recoverable schedules? concurrency control

  9. Strict 2PL : With 2PL, hold write locks until commit Ti Tj Wi(A) Ci ui(A) rj(A) ... ... ... ... ... ... ... concurrency control

  10. S is recoverable if each transaction commits only after all transactions from which it read have committed. • S avoids cascading rollback if each transaction may read only those values written by committed transactions. concurrency control

  11. S is strict if each transaction may read and write only items previously written by committed transactions. RC Avoids cascading rollback ST SERIAL ACR concurrency control

  12. Examples • Recoverable: • w1(A) w1(B) w2(A) r2(B) c1 c2 • Avoids Cascading Rollback: • w1(A) w1(B) w2(A) c1 r2(B) c2 • Strict: • w1(A) w1(B) c1 w2(A) r2(B) c2 Assumes w2(A) is done without reading

  13. Schedule with Deadlock T1 T2 l1(A); Read(A) l2(B);Read(B) A A+100;Write(A) B Bx2;Write(B) l1(B) l2(A) delayed delayed transaction management

  14. Deadlocks • Detection • Wait-for graph • Prevention • Resource ordering • Timeout • Wait-die • Wound-wait transaction management

  15. Deadlock Detection • Build Wait-For graph • Use lock table structures • Build incrementally or periodically • When cycle found, rollback victim T5 T2 T1 T7 T4 T6 T3 transaction management

  16. Resource Ordering • Order all elements A1, A2, …, An • A transaction T can lock Ai after Aj only if i > j Problem : Ordered lock requests not realistic in most cases transaction management

  17. Timeout • If transaction waits more than L sec., roll it back! • Simple scheme • Hard to select L transaction management

  18. Wait-die • Transactions given a timestamp when they arrive …. ts(Ti) • Ti can only wait for Tj if ts(Ti)< ts(Tj) ...else die transaction management

  19. wait? Example: T1 (ts =10) T2 (ts =20) T3 (ts =25) wait wait transaction management

  20. Wound-wait • Transactions given a timestamp when they arrive … ts(Ti) • Ti wounds Tj if ts(Ti)< ts(Tj) else Ti waits “Wound”: Tj rolls back and gives lock to Ti transaction management

  21. Summary • Cascading rollback Recoverable schedule • Deadlock • Prevention • Detection transaction management

  22. Overall Summary concurrency control

  23. Recovery: DB Dump + Log backup database active database log • If active database is lost, • restore active database from backup • bring up-to-date using redo entries in log

  24. When can log be discarded? last needed undo check- point db dump log time not needed for media recovery not needed for undo after system failure not needed for redo after system failure

  25. Multiple Mode Locks: Shared locks So far: S = ...l1(A) r1(A) u1(A) … l2(A) r2(A) u2(A) … Do not conflict Instead: S=... ls1(A) r1(A) ls2(A) r2(A) …. us1(A) us2(A) Also, we have exclusive (X)/write locks

  26. Locks in Practice Ti Read(A),Write(B) l(A),Read(A),l(B),Write(B)… Read(A),Write(B) Scheduler, part I lock table Scheduler, part II DB

  27. Lock table Conceptually If null, object is unlocked A  B Lock info for B C Lock info for C  Every possible object ...

  28. But use hash table: ... A If object not found in hash table, it is unlocked A Lock info for A H ...

  29. Lock info for A - example tran mode wait? Nxt T_link Object:A Group mode:U Waiting:yes List: T1 S no T2 U no T3 X yes  To other T3 records

  30. What are the objects we lock? Relation A Tuple A Disk block A ? Tuple B Relation B Tuple C Disk block B ... ... ... DB DB DB

  31. Locking works in any case, but should we choose small or large objects? • If we lock large objects (e.g., Relations) • Need few locks • Low concurrency • If we lock small objects (e.g., tuples,fields) • Need more locks • More concurrency

  32. We can have it both ways!! Ask any janitor to give you the solution... Stall 1 Stall 2 Stall 3 Stall 4 restroom hall

  33. T1(IS) T1(S) Example , T2(S) R1 t1 t4 t2 t3

  34. T1(IS) , T2(IX) T2(IX) T1(S) Example R1 t1 t4 t2 t3

  35. Multiple granularity Comp Requestor IS IX S SIX X IS Holder IX S SIX X

  36. Multiple granularity Comp Requestor IS IX S SIX X IS Holder IX S SIX X T T T T F T T F F F T F T F F T F F F F F F F F F

  37. Inserting Records: Phantoms Example: relation R (E#,name,…) constraint: E# is key use tuple locking R E# Name …. o1 55 Smith o2 75 Jones

  38. T1: Insert <04,Kerry,…> into RT2: Insert <04,Bush,…> into R T1 T2 S1(o1)S2(o1) S1(o2)S2(o2) Check Constraint Check Constraint Insert o3[04,Kerry,..] Insert o4[04,Bush,..] ... ...

  39. Solution • Use multiple granularity tree • Before insert of node Q, lock parent(Q) in X mode R1 t1 t2 t3

  40. Back to example T1: Insert<04,Kerry> T2: Insert<04,Bush> T1 T2 X1(R) Check constraint Insert<04,Kerry> U(R) X2(R) Check constraint Oops! e# = 04 already in R! X2(R) delayed

  41. Instead of using R, can use index on R: R Example: Index 100<E#<200 Index 0<E#<100 ... E#=2 E#=5 E#=109 ... E#=107 ...

More Related