1 / 19

Allocating Isolation Levels to Transactions by Alan Fekete

Allocating Isolation Levels to Transactions by Alan Fekete. Presentation by Dr. Greg Speegle November 10, 2005. Introduction. Strict 2PL ensures serializability Snapshot isolation Increases concurrency Allows inconsistencies Both supported by DBMS What happens when both used?.

noreen
Download Presentation

Allocating Isolation Levels to Transactions by Alan Fekete

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. Allocating Isolation Levels to Transactions by Alan Fekete Presentation by Dr. Greg Speegle November 10, 2005

  2. Introduction • Strict 2PL ensures serializability • Snapshot isolation • Increases concurrency • Allows inconsistencies • Both supported by DBMS • What happens when both used?

  3. Review of Strict 2PL • Transactions must request locks before access • All locks released at commit time • Shared (read) locks compatible • All other combinations incompatible

  4. Intro to Snapshot Isolation • T.start timestamp when T starts • Read last committed value before start timestamp • T.commit timestamp assigned at commit • T commits unless • Ti commit time between T.start and T.commit • writeset(t) overlaps writeset(Ti) • “First committer wins”

  5. Example Snapshot Isolation • Ti.start = 0; Tj.start = 1; • Ri[x] Wi[x] Rj[x] Cj Ri[y] Ci • CSR • Not allowed S2PL • Not recoverable

  6. Example Snapshot Isolation • Ti.start = 0; Tj.start = 1 • Ri[x] Wi[x] Rj[x] Wj[x] Cj Ri[y] Ci • Ci fails (Tj.commit = 2; Ti.commit = 3) • CSR • Not allowed S2PL • Not strict (or recoverable)

  7. Example Snapshot Isolation • Ti.start = 0; Tj.start = 1 • Ri[x] Rj[x] Wi[x] Ri[y] Rj[y] Wj[y] Ci Cj • Not allowed by S2PL • Not CSR • Recoverable, Cascadeless & Strict

  8. Snapshot Isolation • No waiting for reads • Requires multiversion support • Possible non-serializable executions • Allows non-strict executions • Conclusion: Useful for very high read-to-write ratio transactions (read only?)

  9. Problem Statement • Can Snapshot Isolation and Strict 2PL be used at the same time and allow only serializable schedules? • Trivial answer – Yes, if all transactions use S2PL!

  10. Allocations • T is the set of transactions • Allocation – the set of transactions executing under SI (denoted S) • T – S is the set of transactions executing under S2PL. • T is acceptable if every allowed execution is CSR

  11. Interference Graph (IG) • Nodes are transactions • Edges are labeled • Ti -> Tj is exposed if • rset(Ti) overlaps wset(Tj) AND wset(Ti) does not overlap wset(Tj) • Ti -> Tj is protected if • wset(Ti) overlaps wset(Tj) OR • rset(Ti) does not overlap wset(Tj) AND wset(Ti) overlaps rset(Tj)

  12. Interference Graph (IG) • Edges are independent of schedule • Edges always in pairs • T1 = R[x]R[y]W[y] • T2 = R[x]W[x] • T3 = W[y] • T1 -> T2 (exposed) • T2 -> T1, T1->T3, T3->T1 (protected) • No edge between T2 and T3

  13. Exposed Edges • Key Point: • Exposed edges represent exactly the possibility of conflicts which are permitted under snapshot isolation which may lead to non-CSR schedules

  14. Example Snapshot Isolation • Ti.start = 0; Tj.start = 1 • Ri[x] Rj[x] Wi[x] Ri[y] Rj[y] Wj[y] Ci Cj • Not allowed by S2PL • Not CSR • Recoverable, Cascadeless & Strict • Ti -> Tj & Tj -> Ti are both exposed

  15. Pivot • Tb is a pivot if there exists Ta and Tc • Ta -> Tb is exposed • Tb -> Tc is exposed • Ta, Tb, and Tc are consecutive nodes in a cycle in IG

  16. Example Snapshot Isolation • Ti.start = 0; Tj.start = 1 • Ri[x] Rj[x] Wi[x] Ri[y] Rj[y] Wj[y] Ci Cj • Not allowed by S2PL • Not CSR • Ti -> Tj & Tj -> Ti are both exposed • Ti and Tj are both pivots

  17. Acceptable Allocations • An allocation is acceptable iff all pivots are under S2PL. • Example: Ti.start = 0; Tj.start = 1 • Ri[x] Rj[x] Wi[x] Ri[y] Rj[y] Wj[y] Ci Cj • Ti and Tj both pivots • Ti and Tj both under S2PL to be acceptable

  18. Conclusion • Given set of transactions T • Given allocation S • Build IG(T) • Find pivots, P • If P does not overlap S, then ok

  19. Future Work • Performance issues • Maximal S best? • Build tools for DBMS use • Consider additional SQL isolation levels

More Related