TAILIEUCHUNG - Principles of Database Management Systems - Notes 8: More on Transaction Processing

Principles of Database Management Systems - Notes 8: More on Transaction Processing is Topics to discuss, Problem of Uncommitted Data, How to avoid cascading rollbacks? Recovery with group commit, Avoiding cascading rollbacks with validation, Transaction Isolation in Practice. | Principles of Database Management Systems Notes 8: More on Transaction Processing Based on lecture notes by Hector Garcia-Molina 1 Topics to discuss: "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 2 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 3 Constraint: A=B Example (“dirty read”) A B Assume 2PL with X-locks T1 l1(A); r1(A); A:=A+100; w1(A); l1(B); u1(A) r1(B); Abort; u1(B); T2 25 25 125 l2(A); r2(A) A:=A*2; w2(A); l2(B); [Denied] l2(B); u2(A); r2(B) B:=B*2; w2(B);u2(B) Constraint violation! 250 50 250 50 4 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 .

• Cơ sở dữ liệu
• Problem of Uncommitted Data
• Recovery with group commit
• Avoiding cascading rollbacks with validation
• Transaction Isolation in Practice
• Principles of Database Management Systems
• SQL isolation levels