Other Properties of a Transaction Incomplete transactions cannot reveal its results to other transactions before commitment Committed updates are persistent (won't be lost) Once a transaction commits, the system must guarantee that the results of its operations will never be lost(database recovery) Department of Computer Science and Engineering, HKUST 6
Department of Computer Science and Engineering, HKUST 6 Other Properties of a Transaction • Incomplete transactions cannot reveal its results to other transactions before commitment • Committed updates are persistent (won’t be lost) • Once a transaction commits, the system must guarantee that the results of its operations will never be lost (database recovery)
Requirements on the DBMS o maintain database consistency over time(consistency atomicity) To ensure multiple users can simultaneously access and modify the data without creating inconsistency(isolation) To make the changes to data permanent (durability) Consistency atomicity and durability are needed even if transactions are executed serially (i.e. no interleaving) Department of Computer Science and Engineering, HKUST 7
Department of Computer Science and Engineering, HKUST 7 • To maintain database consistency over time (consistency, atomicity) • To ensure multiple users can simultaneously access and modify the data without creating inconsistency (isolation) • To make the changes to data permanent (durability) • Consistency, atomicity and durability are needed even if transactions are executed serially (i.e., no interleaving) Requirements on the DBMS
Why is Concurrency Control Needed? Several problems occur when concurrent transactions execute in an uncontrolled manner A sChedule of concurrent transactions is a particular sequence of interleaving of their read or write operations In general a transaction, has a set of data items it accesses (read set), and a set of data items it modifies( write set Department of Computer Science and Engineering, HKUST 8
Department of Computer Science and Engineering, HKUST 8 • Several problems occur when concurrent transactions execute in an uncontrolled manner • A schedule of concurrent transactions is a particular sequence of interleaving of their read or write operations • In general a transaction, has a set of data items it accesses (read set), and a set of data items it modifies (write set) Why is Concurrency Control Needed?
Problem 1: Lost Update Problem a transaction overwrites a data item modified by other transactions Transaction 1(HK ATM) Transaction 2(KIn ATm) RI(Balance) R2 Balance Balance=Balance +500 Balance=Balance-700 W1(Balance) W2(Balance) Schedule 1 Balance Schedule 2 Balance RI(Balance) 1000 RI(Balance) 1000 R2(Balance) 1000 R2(Balance) 1000 WI(Balance) 1500 W2(Balance) 300 overwrite W2( Balance) 300 WI(Balance) 1500 overwrite The correct(consistent)value of Balance is 800, when initial Balance is 1000 Department of Computer Science and Engineering, HKUST 9
Department of Computer Science and Engineering, HKUST 9 A transaction overwrites a data item modified by other transactions The correct (consistent) value of Balance is 800, when initial Balance is 1000 Transaction 1 (HK ATM) R1(Balance) Balance=Balance + 500 W1(Balance) Transaction 2 (Kln ATM) R2(Balance) Balance=Balance - 700 W2(Balance) Schedule 1 Balance R1(Balance) 1000 R2(Balance) 1000 W1(Balance) 1500 W2(Balance) 300 Schedule 2 Balance R1(Balance) 1000 R2(Balance) 1000 W2(Balance) 300 W1(Balance) 1500 Problem 1: Lost Update Problem overwrite overwrite
Problem 2: Dirty Read a transaction reads uncommitted modified data item values updated by other transactions Transaction 1(HK ATM) Transaction 2(KIn ATM) RI(Balance) R2 Balance) Balance=Balance +500 Balance=Balance-1200 WI(Balance) W2( Balance) abort Commit Schedule RI(Balance) 1000 T2 read a wIBalance) 1500 For a consistent database ction dirty value also be aborted from t1 W2(Balance) 300 Abort tl Commit t2 300 Department of Computer Science and Engineering, HKUST 10
Department of Computer Science and Engineering, HKUST 10 A transaction reads uncommitted modified data item values updated by other transactions. For a consistent database state, Transaction 2 should also be aborted Transaction 1 (HK ATM) R1(Balance) Balance=Balance + 500 W1(Balance) Abort Transaction 2 (Kln ATM) R2(Balance) Balance=Balance -1200 W2(Balance) Commit Schedule R1(Balance) 1000 W1(Balance) 1500 R2(Balance) 1500 W2(Balance) 300 Abort T1 Commit T2 300 Problem 2: Dirty Read T2 read a “dirty value” from T1