RU CS 445 - Transaction Management and Concurrency Control

Unformatted text preview:

Chapter 9In this chapter, you will learn:What is a Transaction?Example TransactionSlide 5Transaction PropertiesTransaction Management with SQLTransaction LogTransaction Log ExampleConcurrency ControlLost UpdatesUncommitted DataInconsistent RetrievalsInconsistent Retrievals (con’t.)The SchedulerRead/Write Conflict Scenarios: Conflicting Database Operations MatrixConcurrency Control with Locking MethodsDatabase-Level Locking SequenceTable-Level Lock ExamplePage-Level Lock ExampleRow-Level Lock ExampleBinary LocksExample of Binary Lock TableShared/Exclusive LocksProblems with LockingTwo-Phase LockingTwo-Phase Locking ProtocolDeadlocksHow Deadlock Conditions CreatedConcurrency Control with Time Stamping MethodsConcurrency Control with Optimistic MethodsDatabase Recovery ManagementCauses of Database FailureTransaction Recovery9Chapter 9Transaction Management and Concurrency ControlDatabase Systems: Design, Implementation, and Management, Fifth Edition, Rob and CoronelDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel29In 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 integrityDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel39•Logical unit of work •Must be either entirely completed or aborted•No intermediate states are acceptableWhat is a Transaction?Figure 9.1Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel49•Examine current account balance•Consistent state after transaction•No changes made to DatabaseExample TransactionSELECT ACC_NUM, ACC_BALANCEFROM CHECKACCWHERE ACC_NUM = ‘0908110638’;Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel59•Register credit sale of 100 units of product X to customer Y for $500•Consistent state only if both transactions are fully completed•DBMS doesn’t guarantee transaction represents real-world event Example TransactionUPDATE PRODUCTSET PROD_QOH = PROD_QOH - 100WHERE PROD_CODE = ‘X’;UPDATE ACCT_RECEIVABLESET ACCT_BALANCE = ACCT_BALANCE + 500WHERE ACCT_NUM = ‘Y’;Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel69•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 completeTransaction PropertiesDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel79•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 Transaction Management with SQLDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel89•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 StatementTransaction LogDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel99Transaction Log ExampleTable 9.1Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel109•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 retrievalsConcurrency ControlDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel119Lost UpdatesTable 9.2Table 9.3Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel129Uncommitted DataTable 9.5Table 9.4Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel139Inconsistent RetrievalsTable 9.6Table 9.7Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel149Inconsistent Retrievals (con’t.)Table 9.8Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel159•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 CPUThe SchedulerDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel169Read/Write Conflict Scenarios:Conflicting Database Operations MatrixTable 9.9Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel179Concurrency 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 useDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel189Database-Level Locking SequenceFigure 9.2Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel199Table-Level Lock ExampleFigure 9.3Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel209Page-Level Lock ExampleFigure 9.4Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel219Row-Level Lock ExampleFigure 9.5Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel229•Two states–Locked (1) –Unlocked (0) •Locked objects unavailable to other objects–Unlocked objects open to any transaction–Transaction unlocks object when completeBinary LocksDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel239Example of Binary Lock TableTable 9.10Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel249Shared/Exclusive Locks•Shared–Exists when concurrent


View Full Document

RU CS 445 - Transaction Management and Concurrency Control

Download Transaction Management and Concurrency Control
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Transaction Management and Concurrency Control and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Transaction Management and Concurrency Control 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?