Introduction to Database Systems CSE 444 Lecture 15 Transactions: Isolation Levels Magda Balazinska - CSE 444, Fall 2010 12 READ-ONLY Transactions Client 1: START TRANSACTION INSERT INTO SmallProduct(name, price) SELECT pname, price FROM Product WHERE price <= 0.99 DELETE FROM Product WHERE price <=0.99 COMMIT Client 2: SET TRANSACTION READ ONLY START TRANSACTION SELECT count(*) FROM Product SELECT count(*) FROM SmallProduct COMMIT Can help DBMS improve performance3 Isolation Levels in SQL 1. “Dirty reads” SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 2. “Committed reads” SET TRANSACTION ISOLATION LEVEL READ COMMITTED 3. “Repeatable reads” SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 4. Serializable transactions SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ACID Magda Balazinska - CSE 444, Fall 2010Choosing Isolation Level • Trade-off: efficiency vs correctness • DBMSs give user choice of level Magda Balazinska - CSE 444, Fall 2010 4 Beware!! • Default level is often NOT serializable • Default level differs between DBMSs • Some engines support subset of levels! • Serializable may not be exactly ACID Always read DBMS docs!1. Isolation Level: Dirty Reads Implementation using locks: • “Long duration” WRITE locks – A.k.a Strict Two Phase Locking (you knew that !) • Do not use READ locks – Read-only transactions are never delayed Possible pbs: dirty and inconsistent reads 5 Magda Balazinska - CSE 444, Fall 20102. Isolation Level: Read Committed Implementation using locks: • “Long duration” WRITE locks • “Short duration” READ locks – Only acquire lock while reading (not 2PL) • Possible pbs: unrepeatable reads – When reading same element twice, – may get two different values 6 Magda Balazinska - CSE 444, Fall 20102. Read Committed in Java Magda Balazinska - CSE 444, Fall 2010 7 In the handout: Lecture15.java - Transaction 1: db.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); db.setAutoCommit(false); readAccount(); Thread.sleep(5000); readAccount(); db.commit(); In the handout: Lecture15.java – Transaction 2: db.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); db.setAutoCommit(false); writeAccount(); db.commit(); Can see a different value3. Isolation Level: Repeatable Read Implementation using locks: • “Long duration” READ and WRITE locks – Full Strict Two Phase Locking • This is not serializable yet !!! 8 Magda Balazinska - CSE 444, Fall 20103. Repeatable Read in Java Magda Balazinska - CSE 444, Fall 2010 9 In the handout: Lecture15.java - Transaction 1: db.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); db.setAutoCommit(false); readAccount(); Thread.sleep(5000); readAccount(); db.commit(); In the handout: Lecture15.java – Transaction 2: db.setTransactionIsolation(Connection. TRANSACTION_REPEATABLE_READ); db.setAutoCommit(false); writeAccount(); db.commit(); Now sees the same value3. Repeatable Read in Java Magda Balazinska - CSE 444, Fall 2010 10 In the handout: Lecture15.java – Transaction 3: db.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); db.setAutoCommit(false); countAccounts(); Thread.sleep(5000); countAccounts(); db.commit(); In the handout: Lecture15.java – Transaction 4: db.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); db.setAutoCommit(false); insertAccount(); db.commit(); Can see a different count Note: In PostgreSQL will still see the same count.The Phantom Problem 11 T1: select count(*) from R where price>20 . . . . . . . . . . . . . . . . select count(*) from R where price>20 T2: . . . . . . . . insert into R(name,price) values(‘Gizmo’, 50) . . . . R1(X), R1(Y), R1(Z), W2(New), R1(X), R1(Y), R1(Z), R1(New) The schedule is conflict-serializable, yet we get different counts ! “Phantom” = tuple visible only during some part of the transaction Magda Balazinska - CSE 444, Fall 2010The Phantom Problem • The problem is in the way we model transactions: – Fixed set of elements • This model fails to capture insertions, because these create new elements • No easy solutions: – Need “predicate locking” but how to implement it? – Sol1: Lock on the entire relation R (or chunks) – Sol2: If there is an index on ‘price’, lock the index nodes 12 Magda Balazinska - CSE 444, Fall 20104. Serializable in Java Magda Balazinska - CSE 444, Fall 2010 13 In the handout: Lecture13.java – Transaction 3: db.setTransactionIsolation(Connection. TRANSACTION_SERIALIZABLE); db.setAutoCommit(false); countAccounts(); Thread.sleep(5000); countAccounts(); db.commit(); In the handout: Lecture13.java – Transaction 4: db.setTransactionIsolation(Connection. TRANSACTION_SERIALIZABLE); db.setAutoCommit(false); insertAccount(); db.commit(); Now should see same countMagda Balazinska - CSE 444, Fall 2010 14 Commercial Systems • DB2: Strict 2PL • SQL Server: – Strict 2PL for standard 4 levels of isolation – Multiversion concurrency control for snapshot isolation • PostgreSQL: – Multiversion concurrency control • Oracle – Multiversion concurrency controlSnapshot Isolation • Reading: M. J. Franklin. “Concurrency Control and Recovery”. Posted on class website 15 Magda Balazinska - CSE 444, Fall 2010Snapshot Isolation • A type of multiversion concurrency control algorithm • Provides yet another level of isolation • Very efficient, and very popular – Oracle, PostgreSQL, SQL Server 2005 • Prevents many classical anomalies BUT… • Not serializable (!), yet ORACLE and PostgreSQL use it even for SERIALIZABLE transactions! 16 Magda Balazinska - CSE 444, Fall 2010Snapshot Isolation Rules • Each transactions receives a timestamp TS(T) • Transaction T sees snapshot at time TS(T) of the database • When T commits, updated pages are written to disk • Write/write conflicts resolved by “first committer wins” rule • Read/write conflicts are ignored 17 Magda Balazinska - CSE 444, Fall 2010Snapshot Isolation (Details) • Multiversion concurrency control: – Versions of X: Xt1, Xt2, Xt3, . . . • When T reads X, return XTS(T). • When T writes X: if other transaction updated X, abort – Not faithful to “first committer”
View Full Document
Unlocking...