Database Isolation Levels Reading n Farkas Database Isolation

Database Isolation Levels

Reading n Farkas Database Isolation Levels, lecture notes by Dr. A. Fekete, http: //www. authorstream. com/Presen tation/Australian. Computer. So-256648 -ACS-Frontiers-ICT-Research-13 October-2009 -i-Education-pptpowerpoint/ CSCE 824 - Spring 2013 2

Serializability Serial execution n Conflict serializable n 1 -Copy Serializable n n Farkas Cost of properties! CSCE 824 - Spring 2013 3

Problems n n n Farkas Dirty read: read a data item that might subsequently be rolled back (e. g. , aborted transaction) Unrepeatable read: read an item twice during a transaction and get different results, even though the transaction did not change the value of the data item Phantoms: retrieve a collection of objects twice in a transaction and get different results even though the transaction did not change any of the data CSCE 824 - Spring 2013 4

Database Isolation Levels Serializable n Repeatable read n Read committed n Read un-commited n Farkas CSCE 824 - Spring 2013 5

Properties of isolation levels Isolation level Dirty read Non-repeatable read phantoms Serializable Not allowed Repeatable read Not allowed Allowed Read Committed Not allowed Allowed Read Uncommitted Allowed Farkas CSCE 824 - Spring 2013 6

Timestamp Ordering Each transaction is assigned a unique timestamp n Ordering rule: if pi[x] and qj[x] are conflicting than pi[x] is processed before qj[x] iff TS(Ti) < TS(Tj) n Timestamp ordering produces serializable histories. n Farkas CSCE 824 - Spring 2013 7

Timestamp ordering n n Farkas Scheduler: maintains for every item the max. TS of reads and writes If an operation pi[x] arrives after the scheduler submitted qj[x] conflicting operation, Ti is aborted. Note: Ti may be resubmitted with a new, larger TS Problem: Not strict (not recoverable and does not avoid cascading aborts) CSCE 824 - Spring 2013 8

Timestamp Ordering r 2[x] w 2[x] r 1[x] n T 1 is aborted n Why? n n Farkas Could be just give a pre-T 2 value for T 1? r 1[x] r 2[x] w 2[x] is OK CSCE 824 - Spring 2013 9

Multiversion Timestamp Ordering n n n Farkas Multiversion schemes keep old versions of data item to increase concurrency – Multiversion Timestamp Ordering – Multiversion Two-Phase Locking Write: a new version of the data item written (timestamped) Read: select an appropriate version based on the timestamp of the transaction and return the value of the selected CSCE 824 - Spring 2013 10

Multiversion Timestamp Ordering n n Farkas Each data item d has a sequence of versions <d 1, d 2, . . , dm>. Each version dk contains: – Content: the value of version dk – W-timestamp(dk): timestamp of the transaction that wrote dk – R-timestamp(dk): largest timestamp of a transaction that read dk Ti creates a new version dk : – W-timestamp and R-timestamp are Ti-timestamp Tj reads dk : R-timestamp of dk is updated to max(Tjtimestamps, R-timestamp(dk)) CSCE 824 - Spring 2013 11

MV Timestamp ordering n n Farkas Suppose : Ti issues a read(d) or write(d) Let dk denote the version of d whose write timestamp is the largest write timestamp less than or equal to TS(Ti). 1. If transaction Ti issues a read(d), then the value returned is the content of version dk 2. If transaction Ti issues a write(d) 1. if TS(Ti) < R-timestamp(dk), then transaction Ti is aborted 2. if TS(Ti) = W-timestamp(dk), the contents of dk are overwritten 3. else a new version of d is created CSCE 824 - Spring 2013 12

MV Timestamp ordering n n Farkas Reads always succeed A write by Ti is rejected if some other transaction Tj that (in the serialization order defined by the timestamp values) should read Ti's write, has already read a version created by a transaction older than Ti Protocol guarantees serializability For strictness: Processor delays ci until all Tj that wrote versions read by Ti are committed CSCE 824 - Spring 2013 13

Multiversion Two-Phase Locking n n Farkas Differentiates between read-only transactions and update transactions Read-only transactions: – Assigned a timestamp by reading the current value of ts-counter before they start execution – Follow the multiversion timestamp-ordering protocol for performing reads CSCE 824 - Spring 2013 14

MV 2 PL n Update transactions – acquire read and write locks, and hold all locks up to the end of the transaction (strict 2 PL) – Write: create a new version of the data item – Each version of a data item: n n Farkas has a single timestamp whose value is obtained from a counter ts-counter is incremented during commit processing. CSCE 824 - Spring 2013 15

MV 2 PL n n n Farkas Read by an update transaction: sl, read latest value Write an item by an update transaction: xl, sets this version's timestamp to . When update transaction commits: – Sets timestamp on the versions it has created to ts-counter + 1 – Increments ts-counter by 1 CSCE 824 - Spring 2013 16

MV 2 PL n n Farkas Read-only transactions: – started after Ti increments ts-counter: will see the values updated by Ti – started before Ti increments the ts-counter: will see the value before the updates by Ti. Only serializable schedules are produced. CSCE 824 - Spring 2013 17

Snapshot Isolation n n Motivation: Decision support queries that read large amounts of data have concurrency conflicts with OLTP transactions that update a few rows – Poor performance results Solution 1: Give logical “snapshot” of database state to read only transactions, read-write transactions use normal locking – Multiversion 2 -phase locking – Works well, but how does system know a transaction is read only? Farkas CSCE 824 - Spring 2013 18

Snapshot Isolation n Solution 2: Give snapshot of database state to every transaction, updates alone use 2 -phase locking to guard against concurrent updates – Problem: variety of anomalies such as lost update can result – Partial solution: snapshot isolation level (next slide) n Proposed by Berenson et al, SIGMOD 1995 n Implemented in many database systems, Oracle, Postgre. SQL, SQL Server 2005 Farkas CSCE 824 - Spring 2013 19

Snapshot Isolation n A transaction T 1 executing with Snapshot Isolation – takes snapshot of committed data at start – always reads/modifies data in its own snapshot – updates of concurrent transactions are not visible to T 1 – writes of T 1 complete when it commits – First-committer-wins rule: n Commits only if no other concurrent transaction has already written data that T 1 intends to write. T 1 T 2 T 3 W(Y : = 1) Commit Start R(X) 0 R(Y) 1 W(X: =2) W(Z: =3) Commit R(Z) 0 R(Y) 1 Farkas Concurrent updates not visible Own updates are visible Not first-committer of X Serialization error, T 2 is rolled back CSCE 824 - Spring 2013 W(X: =3) Commit-Req Abort 20

Overview of SI n n Farkas Reading is never blocked Performance similar to Read Committed Avoids the usual anomalies – No dirty read – No lost update – No non-repeatable read Problems with SI – SI does not always give serializable executions (transactions don’t see concurrent writes) CSCE 824 - Spring 2013 21

Example Problem T 1 A B C 1 2 3 5 4 3 T 2 Farkas A B C 1 2 6 5 4 3 A B C 1 2 3 5 4 6 CSCE 824 - Spring 2013 22

How to Avoid SI inconsistencies? Static conflict graph: detect cycles n Fekete et al. : modify SI n – Detect w/r conflicts at runtime – Abort conflicting transactions – Don’t wait for full cycle Farkas CSCE 824 - Spring 2013 23

Serializable SI n Timestamps: – Begin transaction (snapshot read) – Commit transaction (conflict with committed) Problem: T 1 wrote x and Committed before c 0 Farkas c 0 r 0(x) w 0(y) b 0 CSCE 824 - Spring 2013 Problem: T 2 read y and Committed before c 0 24

Solution n Two flags for each transaction. L – In – Out Set T. out if r/w-conflict T & T’ n Set T. in if r/w-conflict T’& T n Abort T if both T. in and T. out are set n Farkas CSCE 824 - Spring 2013 25

Example 1 n T 1 writes x before T 0 commits Lock y SI read Lock x write b 1 b 0 w 1(x) c 1 r 2(y) r 0(x) w 0(y) Lock x SI read Lock y write c 2 c 0 T 2. out: True T 0. in: True T 1. in: True T 0. out: True Farkas b 2 CSCE 824 - Spring 2013 26

Next Class Data Integration Ch. 4 Farkas 27