Db help
needhelpqm
Coronel_DatabaseSystems_13e_ch10.pptxChapter 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 for classroom use.
‹#›
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
© 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 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 for classroom use.
‹#›
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 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 for classroom use.
‹#›
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 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 can create several data integrity and consistency problems
Three main problems are lost updates, uncommitted data, and 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 for classroom use.
‹#›
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 for classroom use.
‹#›
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 for classroom use.
‹#›
Concurrency Control with Locking Methods
Locking methods facilitate isolation of data items used in concurrently executing 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
Lock manager: responsible for assigning and policing the locks used by 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 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 for classroom use.
‹#›
Lock Granularity (2 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 for classroom use.
‹#›
Lock Granularity (3 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 for classroom use.
‹#›
Lock Granularity (4 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 for classroom use.
‹#›
Lock Granularity (5 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 for classroom use.
‹#›
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 for classroom use.
‹#›
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 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 for classroom use.
‹#›
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 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 for classroom use.
‹#›
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 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 for classroom use.
‹#›
Concurrency Control with Time Stamping Methods (2 of 2)
Disadvantages
Each value stored in the database requires two additional stamp fields
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 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 for classroom use.
‹#›
Wait/Die and Wound/Wait Schemes (2 of 2)
| Wait/Die and Wound/Wait Concurrency Control Schemes | |||
| Transaction Requesting Lock | Transaction Owning Lock | Wait/Die Scheme | Wound/Wait Scheme |
| T1 (11548789) | T2 (19562545) | T1 waits until T2 is completed and T2 releases its locks. | T1 preempts (rolls back) T2. T2 is rescheduled using the same time stamp. |
| T2 (19562545) | T1 (11548789) | T2 dies (rolls back). T2 is rescheduled using the same time stamp. | T2 waits until T1 is completed and T1 releases 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 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 for classroom use.
‹#›
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
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 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 for classroom use.
‹#›
ANSI Levels of Transaction Isolation (2 of 2)
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 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 for classroom use.
‹#›
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
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 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 for classroom use.
‹#›
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 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 for classroom use.
‹#›
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 for classroom use.
‹#›
