Recall: logging methods provide reconstructing the DB to reflect the result of committed transactions locking (or validation) methods provide serializability of transactions Problem yet uncovered: "dirty data" data written by an uncommitted transaction (to buffers or to disk) may lead to inconsistency, if read by others | Principles of Database Management Systems 9: More on Transaction Processing Pekka Kilpeläinen (Partially based on Stanford CS245 slide originals by Hector Garcia-Molina, Jeff Ullman and Jennifer Widom) DBMS2001 Topics to discuss: [from Chapter 10] "Dirty reads" and cascading rollback not solved by recovery (logging) or serializability (locking) techniques alone How to avoid Transaction isolation in practice Deadlocks Detection Prevention DBMS2001 Problem of Uncommitted Data Recall: logging methods provide reconstructing the DB to reflect the result of committed transactions locking (or validation) methods provide serializability of transactions Problem yet uncovered: "dirty data" data written by an uncommitted transaction (to buffers or to disk) may lead to inconsistency, if read by others DBMS2001 Example (“dirty read”) T1 T2 25 25 l1(A); r1(A); A:=A+100; w1(A); l1(B); u1(A) l2(A); r2(A) A:=A*2; w2(A); l2(B); [Denied] r1(B); Abort; u1(B); l2(B); u2(A); r2(B) B:=B*2; . | Principles of Database Management Systems 9: More on Transaction Processing Pekka Kilpeläinen (Partially based on Stanford CS245 slide originals by Hector Garcia-Molina, Jeff Ullman and Jennifer Widom) DBMS2001 Topics to discuss: [from Chapter 10] "Dirty reads" and cascading rollback not solved by recovery (logging) or serializability (locking) techniques alone How to avoid Transaction isolation in practice Deadlocks Detection Prevention DBMS2001 Problem of Uncommitted Data Recall: logging methods provide reconstructing the DB to reflect the result of committed transactions locking (or validation) methods provide serializability of transactions Problem yet uncovered: "dirty data" data written by an uncommitted transaction (to buffers or to disk) may lead to inconsistency, if read by others DBMS2001 Example (“dirty read”) T1 T2 25 25 l1(A); r1(A); A:=A+100; w1(A); l1(B); u1(A) l2(A); r2(A) A:=A*2; w2(A); l2(B); [Denied] r1(B); Abort; u1(B); l2(B); u2(A); r2(B) B:=B*2; w2(B);u2(B) A B Constraint: A=B 125 250 50 Constraint violation! 250 50 Assume 2PL with X-locks DBMS2001 What's the Problem? 2PL (as above) ensures correctness if transactions complete - But data written by an incomplete (and later aborted) transaction T may correspond to an inconsistent DB May require cascading rollback: Transactions U that have read data written by an aborted T are rolled back (cancelling their effects on DB using log) Transactions V that have read data written by U are rolled back Transactions W that have are rolled back Expensive, and should be avoided DBMS2001 release write locks only after commit/abort Ti Tj _ . . . . . . Wi(A) . . . . . . Commit ui(A) rj(A) Clearly prevents Tj form reading dirty data, but How to avoid cascading rollbacks? DBMS2001 If not found on disk, recovery from a system failure cancels updates by Ti -> data read by Tj becomes dirty Two solutions: Strict locking release T's write locks only after its .
