Transcription
11eDatabase SystemsDesign, Implementation, and ManagementCoronel MorrisChapter 10Transaction Management and ConcurrencyControl 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Learning Objectives In this chapter, students will learn: About database transactions and their properties What concurrency control is and what role it plays inmaintaining the database’s integrity What locking methods are and how they work 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.2
Learning Objectives In this chapter, students will learn: How stamping methods are used for concurrencycontrol How optimistic methods are used for concurrencycontrol How database recovery management is used tomaintain database integrity 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.3
Transaction Logical unit of work that must be entirely completedor aborted Consists of: SELECT statement Series of related UPDATE statements Series of INSERT statements Combination of SELECT, UPDATE, and INSERTstatements 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.4
Transaction Consistent database state: All data integrityconstraints are satisfied Must begin with the database in a known consistentstate to ensure consistency Formed by two or more database requests Database requests: Equivalent of a single SQLstatement in an application program or transaction Consists of a single SQL statement or a collection ofrelated SQL statements 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.5
Evaluating Transaction Results Not all transactions update database SQL code represents a transaction because it accesses adatabase Improper or incomplete transactions can havedevastating effect on database integrity Users can define enforceable constraints based onbusiness rules Other integrity rules are automatically enforced by theDBMS 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.6
Transaction PropertiesAtomicity All operations of a transaction must be completed If not, the transaction is abortedConsistency Permanence of database’s consistent stateIsolation Data used during transaction cannot be used by second transactionuntil the first is completedDurability Ensures that once transactions are committed, they cannot beundone or lostSerializability Ensures that the schedule for the concurrent execution of severaltransactions should yield consistent results 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.7
Transaction Management with SQL SQL statements that provide transaction support COMMIT ROLLBACK Transaction sequence must continue until: COMMIT statement is reached ROLLBACK statement is reached End of program is reached Program is abnormally terminated 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.8
Transaction Log Keeps track of all transactions that update thedatabase DBMS uses the information stored in a log for: Recovery requirement triggered by a ROLLBACKstatement A program’s abnormal termination A system failure 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.9
Concurrency Control Coordination of the simultaneous transactionsexecution in a multiuser database system Objective - Ensures serializability of transactions in amultiuser database environment 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.10
Problems in Concurrency ControlLost update Occurs in two concurrent transactions when: Same data element is updated One of the updates is lostUncommitted data Occurs when: Two transactions are executed concurrently First transaction is rolled back after the second transaction hasalready accessed uncommitted dataInconsistent retrievals Occurs when a transaction accesses data before and after oneor more other transactions finish working with such data 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.11
The Scheduler Establishes the order in which the operations areexecuted within concurrent transactions Interleaves the execution of database operations toensure serializability and isolation of transactions Based on concurrent control algorithms to determinethe appropriate order Creates serialization schedule Serializable schedule: Interleaved execution oftransactions yields the same results as the serialexecution of the transactions 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.12
Concurrency Control with LockingMethods Locking methods - Facilitate isolation of data itemsused in concurrently executing transactions Lock: Guarantees exclusive use of a data item to acurrent transaction Pessimistic locking: Use of locks based on theassumption that conflict between transactions islikely Lock manager: Responsible for assigning andpolicing the locks used by the transactions 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.13
Lock Granularity Indicates the level of lock use Levels of locking Database-level lock Table-level lock Page-level lock Page or diskpage: Directly addressable section of a disk Row-level lock Field-level lock 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.14
Figure 10.3 - Database-Level LockingSequence 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.15
Figure 10.4 - An Example of a TableLevel Lock 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.16
Figure 10.5 - An Example of a PageLevel Lock 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.17
Figure 10.6 - An Example of a RowLevel Lock 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.18
Lock TypesBinary lock Has two states, locked (1) and unlocked (0) If an object is locked by a transaction, no other transaction canuse that object If an object is unlocked, any transaction can lock the object forits useExclusive lock Exists when access is reserved for the transaction thatlocked the objectShared lock Exists when concurrent transactions are granted readaccess on the basis of a common lock 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.19
Problems in Using Locks Resulting transaction schedule might not beserializable Schedule might create deadlocks 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.20
Two-Phase Locking (2PL) Defines how transactions acquire and relinquish locks Guarantees serializability but does not preventdeadlocks Phases Growing phase - Transaction acquires all required lockswithout unlocking any data Shrinking phase - Transaction releases all locks andcannot obtain any new lock 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.21
Two-Phase Locking (2PL) Governing rules Two transactions cannot have conflicting locks No unlock operation can precede a lock operation inthe same transaction No data are affected until all locks are obtained 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.22
Figure 10.7 - Two-Phase LockingProtocol 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.23
Deadlocks Occurs when two transactions wait indefinitely foreach other to unlock data Known as deadly embrace Control techniques Deadlock prevention Deadlock detection Deadlock avoidance Choice of deadlock control method depends ondatabase environment 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.24
Table 10.13 - How a DeadlockCondition is Created 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.25
Time Stamping Assigns global, unique time stamp to each transaction Produces explicit order in which transactions aresubmitted to DBMS Properties Uniqueness: Ensures no equal time stamp values exist Monotonicity: Ensures time stamp values alwaysincreases 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.26
Time Stamping Disadvantages Each value stored in the database requires twoadditional stamp fields Increases memory needs Increases the database’s processing overhead Demands a lot of system resources 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.27
Table 10.14 - Wait/Die and Wound/WaitConcurrency Control Schemes 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.28
Concurrency Control with OptimisticMethods Optimistic approach: Based on the assumption thatthe majority of database operations do not conflict Does not require locking or time stamping techniques Transaction is executed without restrictions until it iscommitted 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.29
Phases of Optimistic ApproachRead Transaction: Reads the database Executes the needed computations Makes the updates to a private copy of the database valuesValidation Transaction is validated to ensure that the changes madewill not affect the integrity and consistency of the databaseWrite Changes are permanently applied to the database 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.30
Table 10.15 - Transaction IsolationLevels 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.31
Database Recovery Management Database recovery: Restores database from a givenstate to a previously consistent state Recovery transactions are based on the atomictransaction property Atomic transaction property: All portions of atransaction must be treated as a single logical unit ofwork If transaction operation cannot be completed: Transaction must be aborted Changes to database must be rolled back 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.32
Concepts that Affect Transaction RecoveryDeferred-write technique or deferred update Ensures that transaction logs are always written before thedata are updatedRedundant transaction logs Ensure that a physical disk failure will not impair theDBMS’s ability to recover dataBuffers Temporary storage areas in a primary memoryCheckpoints Allows DBMS to write all its updated buffers in memory todisk 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.33
Techniques used in Transaction RecoveryProceduresDeferred-write technique or deferred update Only transaction log is updatedWrite-through technique or immediate update Database is immediately updated by transactionoperations during transaction’s execution 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.34
Recovery Process in Deferred-WriteTechnique Identify the last check point in the transaction log If transaction was committed before the last checkpoint Nothing needs to be done If transaction was committed after the last checkpoint Transaction log is used to redo the transaction If transaction had a ROLLBACK operation after thelast check point Nothing needs to be done 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.35
Recovery Process in Write-ThroughTechnique Identify the last checkpoint in the transaction log If transaction was committed before the last checkpoint Nothing needs to be done If transaction was committed after the last checkpoint Transaction must be redone If transaction had a ROLLBACK operation after thelast check point Transaction log is used to ROLLBACK the operations 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.36
Database Recovery Management Database recovery: Restores database from a given state to a previously consistent state Recovery transactions are based on the atomic transaction property Atomic transaction property: All portions of a transaction must be treated as a single logical unit of work If transaction operation cannot be completed:
