9 Chapter 9 Transaction Management and Concurrency Control Database Systems Design Implementation and Management Fifth Edition Rob and Coronel 9 In this chapter you will learn What a database transaction is and what its properties are How database transactions are managed What concurrency control is and what role it plays in maintaining the database s integrity What locking methods are and how they work How database recovery management is used to maintain database integrity Database Systems Design Implementation Management 5th Edition Rob Coronel 2 9 What is a Transaction Logical unit of work Must be either entirely completed or aborted No intermediate states are acceptable Figure 9 1 Database Systems Design Implementation Management 5th Edition Rob Coronel 3 9 Example Transaction Examine current account balance SELECT ACC NUM ACC BALANCE FROM CHECKACC WHERE ACC NUM 0908110638 Consistent state after transaction No changes made to Database Database Systems Design Implementation Management 5th Edition Rob Coronel 4 9 Example Transaction Register credit sale of 100 units of product X to customer Y for 500 UPDATE PRODUCT SET PROD QOH PROD QOH 100 WHERE PROD CODE X UPDATE ACCT RECEIVABLE SET ACCT BALANCE ACCT BALANCE 500 Consistent state only if both transactions are fully WHERE ACCT NUM Y completed DBMS doesn t guarantee transaction represents real world event Database Systems Design Implementation Management 5th Edition Rob Coronel 5 9 Transaction Properties Atomicity All transaction operations must be completed Incomplete transactions aborted Durability Permanence of consistent database state Serializability Conducts transactions in serial order Important in multi user and distributed databases Isolation Transaction data cannot be reused until its execution complete Database Systems Design Implementation Management 5th Edition Rob Coronel 6 9 Transaction Management with SQL Transaction support COMMIT ROLLBACK User initiated transaction sequence must continue until COMMIT statement is reached ROLLBACK statement is reached End of a program reached Program reaches abnormal termination Database Systems Design Implementation Management 5th Edition Rob Coronel 7 9 Transaction Log Tracks all transactions that update database May be used by ROLLBACK command May be used to recover from system failure Log stores Record for beginning of transaction Each SQL statement Operation Names of objects Before and after values for updated fields Pointers to previous and next entries Commit Statement Database Systems Design Implementation Management 5th Edition Rob Coronel 8 9 Transaction Log Example Table 9 1 Database Systems Design Implementation Management 5th Edition Rob Coronel 9 9 Concurrency Control Coordinates simultaneous transaction execution in multiprocessing database Ensure serializability of transactions in multiuser database environment Potential problems in multiuser environments Lost updates Uncommitted data Inconsistent retrievals Database Systems Design Implementation Management 5th Edition Rob Coronel 10 9 Lost Updates Table 9 2 Table 9 3 Database Systems Design Implementation Management 5th Edition Rob Coronel 11 9 Uncommitted Data Table 9 4 Table 9 5 Database Systems Design Implementation Management 5th Edition Rob Coronel 12 9 Inconsistent Retrievals Table 9 6 Table 9 7 Database Systems Design Implementation Management 5th Edition Rob Coronel 13 9 Inconsistent Retrievals con t Table 9 8 Database Systems Design Implementation Management 5th Edition Rob Coronel 14 9 The Scheduler Establishes order of concurrent transaction execution Interleaves execution of database operations to ensure serializability Bases actions on concurrency control algorithms Locking Time stamping Ensures efficient use of computer s CPU Database Systems Design Implementation Management 5th Edition Rob Coronel 15 9 Read Write Conflict Scenarios Conflicting Database Operations Matrix Table 9 9 Database Systems Design Implementation Management 5th Edition Rob Coronel 16 9 Concurrency Control with Locking Methods Lock guarantees current transaction exclusive use of data item Acquires lock prior to access Lock released when transaction is completed DBMS automatically initiates and enforces locking procedures Managed by lock manager Lock granularity indicates level of lock use Database Systems Design Implementation Management 5th Edition Rob Coronel 17 9 Database Level Locking Sequence Figure 9 2 Database Systems Design Implementation Management 5th Edition Rob Coronel 18 9 Table Level Lock Example Figure 9 3 Database Systems Design Implementation Management 5th Edition Rob Coronel 19 9 Page Level Lock Example Figure 9 4 Database Systems Design Implementation Management 5th Edition Rob Coronel 20 9 Row Level Lock Example Figure 9 5 Database Systems Design Implementation Management 5th Edition Rob Coronel 21 9 Binary Locks Two states Locked 1 Unlocked 0 Locked objects unavailable to other objects Unlocked objects open to any transaction Transaction unlocks object when complete Database Systems Design Implementation Management 5th Edition Rob Coronel 22 9 Example of Binary Lock Table Table 9 10 Database Systems Design Implementation Management 5th Edition Rob Coronel 23 9 Shared Exclusive Locks Shared Exists when concurrent transactions granted READ access Produces no conflict for read only transactions Issued when transaction wants to read and exclusive lock not held on item Exclusive Exists when access reserved for locking transaction Used when potential for conflict exists Issued when transaction wants to update unlocked data Database Systems Design Implementation Management 5th Edition Rob Coronel 24 9 Problems with Locking Transaction schedule may not be serializable Managed through two phase locking Schedule may create deadlocks Managed by using deadlock detection and prevention techniques Database Systems Design Implementation Management 5th Edition Rob Coronel 25 9 Two Phase Locking Growing phase Shrinking phase Governing rules Two transactions cannot have conflicting locks No unlock operation can precede a lock operation in the same transaction No data are affected until all locks are obtained Database Systems Design Implementation Management 5th Edition Rob Coronel 26 9 Two Phase Locking Protocol Figure 9 6 Database Systems Design Implementation Management 5th Edition Rob Coronel 27 9 Deadlocks Occurs when two transactions wait for each other to unlock data Called deadly embrace Control techniques