Problem 3: Incorrect Summary a transactionreads partially updated data item values from other transactions Transaction 1(HK ATM) Transaction 2(HK ATM) RI(Balance 2) R2( Balance2) Balance2=Balance2-500 R2 Balance1) WI(Balance2) Sum=Balance1+Balance2 RI( Balanced) Balance l=Balance 1+500 Schedule Bal2 Bal1 W1(Balance1) RI(Balance2) 2000 1000 WI(Balance2) 1500 R2(Balance2) 1500 The correct sum calculated by t2 R2(Balance1) 1000 should be 3000 RI(Balance1) 1000 WI(Balance1) 1500 Department of Computer Science and Engineering, HKUST 11
Department of Computer Science and Engineering, HKUST 11 A transaction reads partially updated data item values from other transactions The correct sum calculated by T2 should be 3000 Transaction 1 (HK ATM) R1(Balance2) Balance2=Balance2 - 500 W1(Balance2) R1(Balance1) Balance1=Balance1+500 W1(Balance1) Transaction 2 (HK ATM) R2(Balance2) R2(Balance1) Sum=Balance1+Balance2 Schedule Bal2 Bal1 R1(Balance2) 2000 1000 W1(Balance2) 1500 R2(Balance2) 1500 R2(Balance1) 1000 R1(Balance1) 1000 W1(Balance1) 1500 Problem 3: Incorrect Summary
Why is Recovery Needed? When a transaction is submitted to a dbms for execution, the system is responsible for making sure that either all the operations in the transaction are completed successfully and their effect is recorded permanently in the database, OR the transaction has no effect whatsoever on the database or on any other transactions The DbMS must not permit some operations of a transaction to be applied to the database while other operations of the transaction are not even when a transaction fails after executing some of its operations but before executing all of them Department of Computer Science and Engineering, HKUST 12
Department of Computer Science and Engineering, HKUST 12 • When a transaction is submitted to a DBMS for execution, the system is responsible for making sure that either • all the operations in the transaction are completed successfully and their effect is recorded permanently in the database, OR • the transaction has no effect whatsoever on the database or on any other transactions • The DBMS must not permit some operations of a transaction to be applied to the database while other operations of the transaction are not, even when a transaction fails after executing some of its operations but before executing all of them. Why is Recovery Needed?
Types of Failures A computer failure system crash) o a transaction error due to system or the transaction itself Concurrency control enforcement Disk failure Physical problems and catastrophes The first three types are more common types of failures and the system must keep sufficient information to recover from the failure Department of Computer Science and Engineering, HKUST 13
Department of Computer Science and Engineering, HKUST 13 • A computer failure (system crash) • A transaction error (due to system or the transaction itself) • Concurrency control enforcement • Disk failure • Physical problems and catastrophes The first three types are more common types of failures, and the system must keep sufficient information to recover from the failure. Types of Failures
Serializability Theory and Concurrency Control Department of Computer Science and Engineering, HKUST 14
Department of Computer Science and Engineering, HKUST 14 Serializability Theory and Concurrency Control
Introduction Since we allow multiple transactions to execute concurrently by interleaving their operations, we need a way to maintain the properties of the transactions Serializability theory concept of schedules forms the basis of transaction processing Department of Computer Science and Engineering, HKUST 15
Department of Computer Science and Engineering, HKUST 15 • Since we allow multiple transactions to execute concurrently by interleaving their operations, we need a way to maintain the properties of the transactions • Serializability theory, concept of schedules forms the basis of transaction processing Introduction