Chp10.pdf

9/2/2023 Chapter 10 Transaction Management and Concurrency Control Learning Objectives After completing this chapter, you will be abl...

9/2/2023 Chapter 10 Transaction Management and Concurrency Control Learning Objectives After completing this chapter, you will be able to: Describe the database transaction management process Identify the four properties of a database transaction Explain concurrency control and its role in maintaining database integrity Describe how locking methods are used for concurrency control Describe how stamping methods are used for concurrency control Describe how optimistic methods are used for concurrency control List and explain the ANSI levels of transaction isolation Describe the role of database recovery management in maintaining database integrity © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 2 for classroom use. 1  9/2/2023 What is a Transaction? A logical unit of work that must be entirely completed or aborted Consists of: - SELECT statement - Series of related UPDATE statements - Series of INSERT statements - Combination of SELECT, UPDATE, and INSERT statements Consistent database state All data integrity constraints are satisfied Must begin with the database in a known consistent state to ensure consistency Most are formed by two or more database requests - Database requests: equivalent of a single SQL statement in an application program or transaction SELECT ….. FROM …. ORDER BY…. UPDATE …. SET …… WHERE..... INSERT INTO …. VALUES …. DELETE FROM …. WHERE …… © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 3 for classroom use. Evaluating Transaction Results Not all transactions update the database SQL code represents a transaction because it accesses the database Improper or incomplete transactions can have devastating effect on database integrity Users can define enforceable constraints based on business rules Other integrity rules are automatically enforced by the DBMS © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 4 for classroom use. 2  9/2/2023 Transaction Properties Atomicity All operations of a transaction must be completed; if not the transaction is aborted Consistency Permanence of database’s consistent state Isolation Data used during transaction cannot be used by second transaction until the first is completed Durability Ensures that once transactions are committed they cannot be undone or lost Serializability Ensures that the schedule for the concurrent execution of several transactions should yield consistent results © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 5 for classroom use. Transaction Management with SQL SQL statements that provide transaction support: COMMIT ROLLBACK Transaction sequence must continue until one of four events occur: COMMIT statement is reached ROLLBACK statement is reached End of program is reached Program is abnormally terminated © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 6 for classroom use. 3  9/2/2023 The Transaction Log Keeps track of all transactions that update the database DBMS uses the information stored in a log for (a): - Recovery requirement triggered by a ROLLBACK statement - Program’s abnormal termination - System failure © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 7 for classroom use. Concurrency Control Coordination of the simultaneous transactions execution in a multiuser database system Objective: ensures serializability of transactions in a multiuser database environment Important because the simultaneous execution of transactions over a shared database (multi-user) can create several data integrity and consistency problems Three main problems are -lost updates -Uncommitted data, -Inconsistent retrievals © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 8 for classroom use. 4  9/2/2023 Problems in Concurrency Control Lost update Occurs in two concurrent transactions when: -Same data element is updated -One of the updates is lost Uncommitted data Occurs when: -Two transactions are executed concurrently -First transaction is rolled back after the second transaction has already accessed uncommitted data Inconsistent retrievals Occurs when: -A transaction accesses data before and after one or more other transactions finish working with such data © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 9 for classroom use. The Scheduler The Scheduler - Establishes the order in which the operations are executed within concurrent transactions Interleaves the execution of database operations to ensure serializability and isolation of transactions Bases actions on concurrent control algorithms Determines appropriate order Creates serialization schedule Serializable schedule: interleaved execution of transactions yields the same results as the serial execution of the transactions © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 10 for classroom use. 5  9/2/2023 Concurrency Control with Locking Methods Locking methods facilitate isolation of data items used in concurrently executing transactions Lock manager: responsible for assigning and policing the locks used by the transactions Lock: guarantees exclusive use of a data item to a current transaction Pessimistic locking: use of locks based on the assumption that conflict between transactions is likely © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 11 for classroom use. Lock Granularity (1 of 5) Indicates the level of lock use Database-level lock Table-level lock Page-level lock -Page or diskpage: directly addressable section of a disk Row-level lock Field-level lock © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 12 for classroom use. 6  9/2/2023 Lock Granularity (1 of 5) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 13 for classroom use. Lock Granularity (1 of 5) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 14 for classroom use. 7  9/2/2023 Lock Granularity (1 of 5) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 15 for classroom use. Lock Granularity (1 of 5) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 16 for classroom use. 8  9/2/2023 Lock Types (1 of 2) Binary lock Two states: locked (1) and unlocked (0) -If an object is locked by a transaction, no other transaction can use that object -If an object is unlocked, any transaction can lock the object for its use Exclusive lock Access is reserved for the transaction that locked the object Shared lock Concurrent transactions are granted read access on the basis of a common lock © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 17 for classroom use. Lock operations READ_LOCK : check the type of lock or existence of a lock WRITE_LOCK : issue the requested lock UNLOCK : release the lock © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 18 for classroom use. 9  9/2/2023 Lock Types (2 of 2) Problems using locks Resulting transaction schedule might not be serializable Schedule might create deadlocks © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 19 for classroom use. Two-Phase Locking to Ensure Serializability (1 of 3) Defines how transactions acquire and relinquish locks Guarantees serializability but does not prevent deadlocks Phases Growing phase: transaction acquires all required locks without unlocking any data Shrinking phase: transaction releases all locks and cannot obtain any new lock © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 20 for classroom use. 10  9/2/2023 Two-Phase Locking to Ensure Serializability (2 of 3) 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 © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 21 for classroom use. Two-Phase Locking to Ensure Serializability (3 of 3) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 22 for classroom use. 11  9/2/2023 Deadlocks Occur when two transactions wait indefinitely for each other to unlock data Also known as deadly embrace Control techniques Deadlock prevention Deadlock detection Deadlock avoidance Choice of deadlock control method depends on database environment © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 23 for classroom use. Concurrency Control with Time Stamping Methods (1 of 2) Time stamping assigns global, unique time stamp to each transaction Produces explicit order in which transactions are submitted to DBMS Properties Uniqueness: ensures no equal time stamp values exist Monotonicity: ensures time stamp values always increases © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 24 for classroom use. 12  9/2/2023 Concurrency Control with Time Stamping Methods (2 of 2) Disadvantages Each value stored in the database requires two additional stamp fields - Last read and last updated time stamps Increases memory needs Increases the database’s processing overhead Demands a lot of system resources © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 25 for classroom use. Wait/Die and Wound/Wait Schemes (1 of 2) Wait/die A concurrency control scheme in which an older transaction must wait for the younger transaction to complete and release the locks before requesting the locks itself -Otherwise, the newer transaction dies and is rescheduled Wound/wait A concurrency control scheme in which an older transaction can request the lock, preempt the younger transaction, and reschedule it -Otherwise, the newer transaction waits until the older transaction finishes © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 26 for classroom use. 13  9/2/2023 Wait/Die and Wound/Wait Schemes (2 of 2) Wait/Die and Wound/Wait Concurrency Control Schemes Transaction Transaction Wait/Die Scheme Wound/Wait Scheme Requesting Lock Owning Lock T1 (11548789) T2 (19562545) T1 waits until T2 is T1 preempts (rolls back) completed and T2 T2. releases its locks. T2 is rescheduled using the same time stamp. T2 (19562545) T1 (11548789) T2 dies (rolls back). T2 waits until T1 is T2 is rescheduled using completed and T1 releases the same time stamp. its locks. © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 27 for classroom use. Concurrency Control with Optimistic Methods (1 of 2) Optimistic approach: based on the assumption that the majority of database operations do not conflict Does not require locking or time stamping techniques Transaction is executed without restrictions until it is committed Phases of optimistic approach Read Validation Write © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 28 for classroom use. 14  9/2/2023 Concurrency Control with Optimistic Methods (2 of 2) Read phase Transaction: -Reads the database -Executes the needed computations -Makes the updates to a private copy of the database values A temporary copy Validation phase Transaction is validated to ensure that the changes made will not affect the integrity and consistency of the database Write phase Changes are permanently applied to the database © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 29 for classroom use. ANSI Levels of Transaction Isolation (1 of 2) The ANSI SQL standard (1992) defines transaction management based on transaction isolation levels Transaction isolation levels refer to the degree to which transaction data is “protected or isolated” from other concurrent transactions Transaction isolation levels are described by the type of “reads” that a transaction allows or not Dirty read: transaction can read data that is not yet committed Nonrepeatable read: transaction reads a given row at time t1, and then it reads the same row at time t2, yielding different results -The original row may have been updated or deleted Phantom read: transaction executes a query at time t1, and then it runs the same query at time t2, yielding additional rows that satisfy the query © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 30 for classroom use. 15  9/2/2023 ANSI Levels of Transaction Isolation (2 of 2) Based on the TYPES of READs – ANSI 4 Levels of Transaction Isolation Read Uncommitted will read uncommitted data from other transactions Increases transaction performance but at the cost of data consistency Read Committed forces transactions to read only committed data Default mode of operation for most databases Repeatable Read isolation level ensures that queries return consistent results Uses shared locks to ensure other transactions do not update a row after the original query reads it Serializable isolation level is the most restrictive level defined by the ANSI SQL standard Deadlocks are still always possible © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 31 for classroom use. 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 All portions of a transaction must be treated as a single logical unit of work -If transaction operation cannot be completed: Transaction must be aborted Changes to database must be rolled back © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 32 for classroom use. 16  9/2/2023 Transaction Recovery (1 of 3) Concepts that affect the recovery process Write-ahead log protocol -Ensures that transaction logs are always written before the data are updated Redundant transaction logs -Ensure that a physical disk failure will not impair the DBMS’s ability to recover data Buffers -Temporary storage areas in a primary memory used to speed up disk operations RAM, Cache, etc. Checkpoints -Allows DBMS to write all its updated buffers in memory to disk © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 33 for classroom use. Transaction Recovery (2 of 3) Techniques used in transaction recovery procedures Deferred-write technique or deferred update -Transaction operations do not immediately update the physical database -Only transaction log is updated Write-through technique or immediate update -Database is immediately updated by transaction operations during transaction’s execution © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 34 for classroom use. 17  9/2/2023 Transaction Recovery (3 of 3) Recovery process steps Identify the last check point in the transaction log -If transaction was committed before the last check point nothing needs to be done -If transaction was committed after the last check point the transaction log is used to redo the transaction -If transaction had a ROLLBACK operation after the last check point the DBMS uses the transaction log records to ROLLBACK or undo the operations, using the “before” values in the transaction log © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 35 for classroom use. Summary (1 of 2) A transaction is a sequence of database operations that access the database Transactions have four main properties: atomicity, consistency, isolation, and durability SQL provides support for transactions through the use of two statements: COMMIT, which saves changes to disk, and ROLLBACK, which restores the previous database state Concurrency control coordinates the simultaneous execution of transactions A lock guarantees unique access to a data item by a transaction Serializability of schedules is guaranteed through the use of two- phase locking © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 36 for classroom use. 18  9/2/2023 Summary (2 of 2) Concurrency control with time stamping methods assigns a unique time stamp to each transaction and schedules the execution of conflicting transactions in time stamp order Concurrency control with optimistic methods assumes that the majority of database transactions do not conflict and that transactions are executed concurrently, using private, temporary copies of the data Database recovery restores the database from a given state to a previous consistent state © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website 37 for classroom use. 19