Degrees of Isolation – A Theoretical Formulation

1. Degrees of Isolation – A Theoretical Formulation Presented by Balaji Sethuraman

2. Paper Information A Theoretical Formulation of Degrees of Isolation in Databases by Vijayalakshmi Aturi, Elisa Bertino and Sushil Jagodia Link: http://citeseer.nj.nec.com/cachedpage/49154/1 Abstract: Formulation of various degrees of isolation in terms of • Histories • Lock based protocols • Timestamp based protocols

3. Transaction Model • A transaction is a unit of program execution that accesses and possibly updates various data items. • Transaction properties • Atomicity: All or none operations. • Consistency: Preserves consistency of database. • Isolation: Concurrency of transactions. Each transaction is unaware of the existence of the other transactions. • Durability: Changes are persisted even if there are system failures. • Focus on the Isolationproperty of transaction.

4. Isolation • True isolation (serialized execution) of transactions slows down performance of the system. • In a shared database system, a completely Isolated system gives the illusion to each transaction that it is being executed alone all by itself. • Most commercial databases offer different degrees of isolation. • Degrees (levels) of isolation offers compromise between Correctness and Performance of a transaction. • 4 levels of Isolation are defined. Lower level of isolation implies improved response time achieved at the expense of consistency.

5. Degrees of Isolation • Degree 0 – Chaos • Does not allow a more than one transaction to simultaneously to write on a data item. • Least restrictive of all isolations • Degree 1 – Browse • Does not allow data updated by a uncommitted transaction to be overwritten (no lost updates). • Equivalent to READ_UNCOMMITTED isolation level of ANSI/ISO SQL2 standard.

6. Degrees of Isolation • Degree 2 – Cursor Stability • In addition to Degree 1, restricts transactions to read only committed data. • Equivalent to READ_COMMITTED isolation level of ANSI/ISO SQL2 standard • Degree 3 – Repeatable Reads • Provides complete isolation (Serializable). • Most restrictive of all. • Equivalent to REPEATABLE_READ isolation level of ANSI/ISO SQL2 standard.

7. Notations • Ti – Transaction i. • ri[x] – read operation by Ti on data item x. • wi[x] – write operation by Ti on data item x. • oi[x] – any operation by Ti on data item x. • ci – commit operation by Ti. • <H – happens-before relation. • ri[x] <H wj[x] – Ti read on x happens before Tj write on x.

8. Definitions • Two operations oi[x] and oj[x]conflict with each other if i <> j and atleast one of them is write. • A history H over T = {T0,T1…Tn} is a partial order with ordering relation <H where • H = Uni=0 Ti and • <H subset-ofUni=0 <i e.g: T1 = r1[z] < w1[x] < w1[y] < r1[z] < c1 T2 = w2[a] < w2[z] < r2[y] < w2[x] < c2 H1 = r1[z] <H w1[x] <H w1[y] <Hw2[a] <H w2[z] <H r2[y]<H r1[z] <H c1 <Hw2[x] <H c2 or H1 = r1[z] w1[x] w1[y] w2[a] w2[z] r2[y] r1[z] c1 w2[x] c2

9. Degree Definition & Isolation graphs • Each degree is defined in terms of the operations allowed in its history. • The Isolation graphs IG(H) defines how to construct a directed graph from the History H over T = {T0,T1…Tn} • Isolation level Theorem: A History H over T is a degree i (i=0 to 3 inclusive) isolation history if IGi(H) is acyclic. • e.g.: A History is degree 2 isolation history if IG2(H) is acyclic.

10. Degree 0 • A history H is a degree 0 isolation history if whenever wi[x], wj[x] ε H, either wi[x] <H wj[x] or wj[x] <H wi[x]. • Isolation Graph of degree 0: Let H be a history over T. Its degree 0 isolation graph IG0(H) is a directed graph whose nodes are transactions in T and whose edges are Ti -> Tj whenever H contains a pair of operations wi[x] and wj[x], i<>j, such that (wi[x] !<H wj[x]) ^ (wj[x] !<H wi[x]). e.g.:H1 = w1[x] w2[x] c1 c2 IG0(H1) = T1 <-> T2 IG0(H) has a cycle. H1 is NOT degree 0 isolation history. e.g.:H1 = r1[z] w1[x] w1[y] w2[a] w2[z] r2[y] r1[z] c1 w2[x] c2 IG0(H1) = T1 T2 IG0(H) is acyclic. H1 is a degree 0 isolation history.

11. Degree 1 • A history H is a degree 1 isolation history if • whenever wi[x], wj[x] ε H, either wi[x] <H wj[x] or wj[x] <H wi[x] and • whenever wi[x] <H wj[x], ci <H wj[x]. • Isolation Graph of degree 1: Let H be a history over T. Its degree 1 isolation graph IG1(H) is a directed graph whose nodes are transactions in T and whose edges are as follows • Every edge in IG0(H) is an edge in IG1(H). • There is an edge Ti -> Tj whenever there exist operations wi[x] and wj[x], i<>j, such that wi[x] <H wj[x] or cj !<H wi[x]. e.g: H = w1[x] w2[x] c1 w3[x]c3 IG1(H) = Cycle exists. H is not in Isolation Degree 1 T2 T1 T3

12. e.g: T1 = r1[z] w1[x] w1[y] r1[z] c1 T2 = w2[a] w2[z] r2[y] w2[x] c2 H1 = r1[z] w1[x] w1[y] w2[a] w2[z] r2[y]w2[x] r1[z] c1c2 IG1(H1) = T1 <-> T2 IG1(H1) has a cycle. H1 is NOT degree 1 isolation history. H2 = r1[z] w1[x] w1[y] w2[a] w2[z] r2[y] r1[z] c1w2[x]c2 IG1(H2) = T1 -> T2 IG1(H2) is acyclic. H2 is a degree 1 isolation history.

13. Degree 2 • A history H is a degree 2 isolation history if • whenever two conflicting operations oi[x], oj[x] ε H, either oi[x] <H oj[x] or oj[x] <H oi[x] and • whenever wi[x] <H oj[x], ci <H oj[x]. • Isolation Graph of degree 2: Let H be a history over T. Its degree 2 isolation graph IG2(H) is a directed graph whose nodes are transactions in T and whose edges are as follows • Every edge in IG1(H) is an edge in IG2(H). • There is an edge Ti -> Tj whenever H contains two conflicting operations there exist operations oi[x] and oj[x], i<>j, such that (oi[x] !<H oj[x]) ^ (oj[x] !<H oi[x]). • There is an edge Ti -> Tj, i<>jwhenever either (wi[x] <H rj[x]) or (cj[x] !<H oi[x] and wj[x] ε Tj). e.g.: H3 = r1[z] w1[x] w1[y]w2[a] w2[z] r2[y] r1[z] c1w2[x] c2 IG3(H3) has a cycle. H3 is NOT degree 3 isolation history. H4 = r1[z] w1[x] w1[y]w2[a]w2[z] r1[z] c1r2[y]w2[x]c2 IG3(H4) is acyclic. H4 is a degree 3 isolation history.

14. Degree 3 • A history H is a degree 3 isolation history if • H is serializable and • whenever wi[x] <H oj[x], ci <H oj[x]. • Isolation Graph of degree 3: Let H be a history over T. Its degree 3 isolation graph IG3(H) is a directed graph whose nodes are transactions in T and whose edges are as follows • Every edge in IG2(H) is an edge in IG3(H). • There is an edge Ti -> Tj,, i<>j whenever either (ri[x] <H wj[x]) or (cj[x] !<H oi[x] and wj[x] ε Tj). e.g.: H4 = r1[z] w1[x] w1[y] w2[a] w2[z]r1[z]c1r2[y]w2[x]c2 IG3(H4) has a cycle. H4 is NOT degree 3 isolation history. H5 = r1[z] w1[x] w1[y] w2[a]r1[z]w2[z]c1r2[y]w2[x]c2 IG3(H5) is acyclic. H5 is a degree 3 isolation history.

15. Transaction Isolation Levels • In a History, different transactions can have different isolation levels. • A transaction Ti is history H is given • Degree 0 isolation if whenever there exists two write operations wi[x] and wj[x] in H, either wi[x] <H wj[x] or wj[x] <H wi[x]. • Degree 1 isolation if • Whenever there exists two operations wi[x] and wj[x] in H such that wj[x] <H wi[x], cj <H wi[x] and • Whenever there exists two operations wi[x] and wj[x] in H such that wi[x] <H wj[x], ci <H wj[x].

16. Transaction Isolation Levels • degree 2 isolation if • Ti is given degree 1 isolation and • Whenever there exist two operations wj[x] and ri[x] in H such that whenever wj[x] <H ri[x], cj <H ri[x]. • degree 3 isolation if • Ti is given degree 2 isolation and • Whenever oi[x] and oj[x] are conflicting operations such that oi[x] <H oj[x], then !Э oi[y] such that oj[y] <H oi[y] where oi[y], oj[y] are two conflicting operations in H.

17. Transaction Isolation Levels Example : T1 = r1[y] w1[z] w1[y] c1 T2 = r2[x] w2[x] c2 T3 = w3[x] w3[y] c3 H9 = r2[x]w3[x] w3[y]r1[y] c3 w2[x] c2w1[z] w1[y] c1 T1 is in Degree 1 isolation (not Degree 2) T2 is in Degree 2 isolation (not Degree 3) T3 is in Degree 3 isolation

18. Protocols • Two protocols to achieve different degrees of isolation • Lock-based protocols. • Timestamp based protocols. • Most database systems use lock based protocol to implement concurrency control for transactions.

19. Lock based Protocols • Two types of locks – a shared lock (read) and an exclusive-lock (write). • Notations: • sli[x] = shared lock acquisition of x by Ti • xli[x] = exclusive lock acquisition of x by Ti • uli[x] = release of shared or exclusive lock on x by Ti • Two locks li[x] and lj[x] are compatible if i=j or neither of them is an exclusive lock. • A transaction is well-formed if it acquires a shared-lock before reading data item and acquires a exclusive-lock before writing a data item. • Paper defines theorems for achieving various degrees of isolation using lock based protocols and proves them.

20. Timestamp based Protocols • Every data item is associated with read, write, commit timestamps • use boolean values to order the operations from scheduler to DM. • Notations : • rts[x] = Read timestamp of x • wts[x] = Write timestamp of x • ts[Ti] = Unique timestamp for Transaction Ti • cts[Ti] = Commit timestamp of Transaction Ti • r-in-transit[x] = Read operation in transit from Scheduler to DM on data item x. • w-in-transit[x] = Write operation in transit from Scheduler to DM on data item x. • Paper defines theorems for achieving various degrees of isolation using timestamp based protocols and proves them.

21. Conclusion • Contributions of the paper • Formalization of notions of various degrees of isolation in terms of histories. • Timestamp-based protocols for achieving different degree of isolation.