1 / 19

Temple University – CIS Dept. CIS661 – Principles of Data Management

Temple University – CIS Dept. CIS661 – Principles of Data Management. V. Megalooikonomou Concurrency control- Deadlocks (based on slides by C. Faloutsos at CMU and on notes by Silberchatz,Korth, and Sudarshan). General Overview. Relational model - SQL Functional Dependencies & Normalization

kitra-wells
Télécharger la présentation

Temple University – CIS Dept. CIS661 – Principles of Data Management

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. Temple University – CIS Dept.CIS661 – Principles of Data Management V. Megalooikonomou Concurrency control- Deadlocks (based on slides by C. Faloutsos at CMU and on notes by Silberchatz,Korth, and Sudarshan)

  2. General Overview • Relational model - SQL • Functional Dependencies & Normalization • Physical Design &Indexing • Query optimization • Transaction processing • concurrency control • recovery

  3. Transactions - dfn = unit of work, eg. move $10 from savings to checking Atomicity (all or none) Consistency Isolation (as if alone) Durability recovery concurrency control

  4. Isolation - concurrency control • serializability <-> correctness • precedence graph • automatically correct interleavings: • locks + protocol (2PL, 2PLC) • but: deadlocks!

  5. Deadlocks • detection • handling • (prevention)

  6. Deadlock detection T1 T2 lock manager L(A) Yes L(B) Yes ... L(B) No <waits> L(A) No time DEADLOCK

  7. Algo for deadlock detection? • wait-for graph: • nodes -> transactions • arcs -> Tsource waits for Tsink • if cycle, then deadlock! • Must invoke a deadlock-detection algorithm periodically to look for cycles.

  8. Eg: T1 T2 lock manager L(A) Yes L(B) Yes ... L(B) No <waits> L(A) No time for ‘B’ T1 T2 ‘A’ ‘B’ for ‘A’

  9. T1 T2 T4 T3 Another example • is there a deadlock? • if yes, which xacts are involved?

  10. T1 T2 T4 T3 Another example • now, is there a deadlock? • if yes, which xacts are involved?

  11. Deadlock detection • how often should we run the algo? • how many transactions are typically involved?

  12. T1 T2 T4 T3 Deadlock handling • Q: what to do?

  13. T1 T2 T4 T3 Deadlock handling • Q0: what to do? • A: select a ‘victim’ & ‘rollback’ • Q1: which/how to choose?

  14. T1 T2 T4 T3 Deadlock handling • Q1: which/how to choose? • A1.1: by age • A1.2: by progress • A1.3: by # items locked already... • A1.4: by # xacts to rollback • Q2: How far to rollback?

  15. T1 T2 T4 T3 Deadlock handling • Q2: How far to rollback? • A2.1: completely • A2.2: minimally • Q3: Starvation??

  16. T1 T2 T4 T3 Deadlock handling • Q3: Starvation?? • A3.1: include #rollbacks in victim selection criterion.

  17. SQL statement • usually, conc. control is transparent to the user, but • LOCK <table-name> [EXCLUSIVE|SHARED]

  18. Concurrency control - conclusions • serializability <-> correctness • automatically correct interleavings: • locks + protocol (2PL, 2PLC, ...) • deadlock detection + handling

  19. Conclusions 2PL schedules serializable schedules serializable schedules 2PLC serial sch’s

More Related