chapter 15
DESCRIPTION
Chapter 15. Transaction Management. Outline. Transaction basics Concurrency control Recovery management Transaction design issues Workflow management. Transaction Definition. Supports daily operations of an organization Collection of database operations - PowerPoint PPT PresentationTRANSCRIPT
McGraw-Hill/Irwin Copyright © 2007 by The McGraw-Hill Companies, Inc. All rights reserved.
Chapter 15
Transaction Management
15-2
Outline
Transaction basics Concurrency control Recovery management Transaction design issues Workflow management
15-3
Transaction Definition Supports daily operations of an
organization Collection of database operations Reliably and efficiently processed as one
unit of work No lost data
Interference among multiple users Failures
15-4
Airline Transaction ExampleSTART TRANSACTION
Display greetingGet reservation preferences from userSELECT departure and return flight recordsIf reservation is acceptable then
UPDATE seats remaining of departure flight recordUPDATE seats remaining of return flight recordINSERT reservation recordPrint ticket if requested
End IfOn Error: ROLLBACK
COMMIT
15-5
ATM Transaction ExampleSTART TRANSACTION
Display greetingGet account number, pin, type, and amountSELECT account number, type, and balanceIf balance is sufficient then
UPDATE account by posting debitUPDATE account by posting debitINSERT history recordDisplay message and dispense cashPrint receipt if requested
End IfOn Error: ROLLBACK
COMMIT
15-6
Transaction Properties Atomic: all or nothing Consistent: database must be consistent
before and after a transaction Isolated: no unwanted interference from
other users Durable: database changes are
permanent after the transaction completes
15-7
Transaction Processing Services Concurrency control Recovery management Service characteristics
Transparent Consume significant resources Significant cost component Transaction design important
15-9
Concurrency Control Problem Objective:
Maximize work performed Throughput: number of transactions
processed per unit time
Constraint: No interference: serial effect Interference occurs on commonly manipulated
data known as hot spots
15-10
Lost Update Problem
Transaction A Time Transaction B
Read SR (10) T1 T2 Read SR (10) If SR > 0 then SR = SR -1
T3
T4 If SR > 0 then SR = SR -1
Write SR (9) T5 T6 Write SR (9)
15-11
Uncommitted Dependency Problem
Transaction A Time Transaction B
Read SR (10) T1 SR = SR - 1 T2 Write SR (9) T3 T4 Read SR (9) ROLLBACK T5
15-12
Inconsistent Retrieval Problems Interference causes inconsistency among
multiple retrievals of a subset of data Incorrect summary Phantom read Non repeatable read
15-13
Incorrect Summary Problem
Transaction A Time Transaction B Read SR1 (10) T1 SR1 = SR1 - 1 T2 Write SR1 (9) T3 T4 Read SR1 (9) T5 Sum = Sum + SR1 T6 Read SR2 (5) T7 Sum = Sum + SR2 Read SR2 (5) T8 SR2 = SR2 - 1 T9 Write SR2 (4) T10
15-14
Locking Fundamentals Fundamental tool of concurrency control Obtain lock before accessing an item Wait if a conflicting lock is held
Shared lock: conflicts with exclusive locks Exclusive lock: conflicts with all other kinds of
locks
Concurrency control manager maintains the lock table
15-16
Deadlock (Mutual Waiting)
Transaction A Time Transaction B
XLock SR1 T1
T2 XLock SR2
XLock SR2 (wait) T3
T4 XLock SR1 (wait)
15-17
Deadlock Resolution Detection
Overhead is reasonable for deadlocks among 2 or 3 transactions
Used by enterprise DBMSs
Timeout Waiting limit Can abort transactions that are not
deadlocked Timeout interval is difficult to determine
15-18
Two Phase Locking (2PL) Protocol to prevent lost update problems All transactions must follow Conditions
Obtain lock before accessing item Wait if a conflicting lock is held Cannot obtain new locks after releasing locks
15-20
Optimistic Approaches Assumes conflicts are rare No locks Check for conflicts
After each read and write At end of transaction
Evaluation Less overhead More variability
15-21
Recovery Management Device characteristics and failure types Recovery tools Recovery processes
15-22
Storage Device Basics Volatile: loses state after a shutdown Nonvolatile: retains state after a shutdown Nonvolatile is more reliable than volatile
but failures can cause loss of data Use multiple levels and redundant levels
of nonvolatile storage for valuable data
15-23
Failure Types
Local Detected and abnormal termination Limited to a single transaction
Operating System Affects all active transactions Less common than local failures
Device Affects all active and past transactions Least common
15-24
Transaction Log History of database changes Large storage overhead Operations
Undo: revert to previous state Redo: reestablish a new state
Fundamental tool of recovery management
15-25
Transaction Log Example
LSN TransNo Action Time Table Row Column Old New
1 101001 START 10:29 2 101001 UPDATE 10:30 Acct 10001 AcctBal 100 200 3 101001 UPDATE 10:30 Acct 15147 AcctBal 500 400 4 101001 INSERT 10:32 Hist 25045 * <1002,
500, …>
5 101001 COMMIT 10:33
15-26
Checkpoints Reduces restart work but adds overhead
Checkpoint log record Write log buffers and database buffers
Checkpoint interval: time between checkpoints
Types of checkpoints Cache consistent Fuzzy Incremental
15-27
Other Recovery Tools Force writing
Checkpoint time End of transaction
Database backup Complete Incremental
15-28
Recovery from a Media Failure Restore database from the most recent
backup Redo all committed transactions since the
most recent backup Restart active transactions
15-30
Recovery Processes Depend on timing of database writes Immediate update approach:
Before commit Log records written first (write-ahead log
protocol)
Deferred update approach After commit Undo operations not needed
15-31
Immediate Update Recovery
Class Description Restart Work
T1 Finished before CP None
T2 Started before CP; finished before failure
Redo forward from checkpoint
T3 Started after CP; finished before failure
Redo forward from checkpoint
T4 Started before CP; not yet finished
Undo backwards from most recent log record
T5 Started after CP; not yet finished
Undo backwards from most recent log record
15-32
Deferred Update Recovery
Class Description Restart Work
T1 Finished before CP None
T2 Started before CP; finished before failure
Redo forward from first log record
T3 Started after CP; finished before failure
Redo forward from first log record
T4 Started before CP; not yet finished
None
T5 Started after CP; not yet finished
None
15-33
Oracle Recovery Features Incremental checkpoints Immediate update approach Mean Time to Recover (MTTR) parameter MTTR advisor Dynamic dictionary views to monitor
recovery state
15-34
Transaction Design Issues Transaction boundary Isolation levels Deferred constraint checking Savepoints
15-35
Transaction Boundary Decisions Division of work into transactions Objective: minimize transaction duration Constraint: enforcement of important
integrity constraints Transaction boundary decision can affect
hot spots
15-37
Transaction Boundary Choices One transaction for the entire form One transaction for the main form and one
transaction for all subform records One transaction for the main form and
separate transactions for each subform record
15-38
Avoiding User Interaction Time Avoid to increase throughput Possible side effects: user confusion due
to database changes Balance increase in throughput with
occurrences of side effects Most situations increase in throughput
more important than possible user confuusion
15-39
Isolation Levels Degree to which a transaction is separated
from the actions of other transactions Balance concurrency control overhead
with interference problems Some transactions can tolerate
uncommitted dependency and inconsistent retrieval problems
Specify using the SET TRANSACTION statement
15-40
SQL Isolation Levels
Level XLocks SLocks PLocks Interference
Read uncommitted
None None None Uncommitted dependency
Read committed
Long Short None All except uncommitted dependency
Repeatable read
Long Long Short (S), Long (X)
Phantom reads
Serializable Long Long Long None
15-41
Scholar’s Lost Update
Transaction A Time Transaction B
Obtain S lock on SR T1
Read SR (10) T2
Release S lock on SR T3
If SR > 0 then SR = SR -1 T4
T5 Obtain S lock on SR
T6 Read SR (10)
T7 Release S lock on SR
T8 If SR > 0 then SR = SR -1
Obtain X lock on SR T9
Write SR (9) T10
Commit T11
T12 Obtain X lock on SR
T13 Write SR (9)
15-42
Integrity Constraint Timing Most constraints checked immediately Can defer constraint checking to EOT SQL
Constraint timing clause for constraints in a CREATE TABLE statement
SET CONSTRAINTS statement
15-43
Save Points Some transactions have tentative actions SAVEPOINT statement determines
intermediate points ROLLBACK to specified save points
CommitSafe
operationsSave point
Rollback tosave point
Tentativeoperations
15-44
Workflow Management Workflow description Enabling technologies Advanced transaction management
15-45
Workflow Basics Set of tasks to accomplish a business
process Human-oriented vs. computer-oriented
Amount of judgment Amount of automation
Task structure vs. task complexity Relationships among tasks Difficulty of performing individual tasks
15-46
Workflow Classification
Human-oriented Computer-oriented
Communicationsupport
Transactionsupport
Task structure
Simple
Complex
Low High
Meetingnotification
Travelrequest
Bid proposal
Insuranceclaim
Utility service
Property sale
Task
com
plex
ity
15-47
Enabling Technologies Distributed object management
Many kinds of non traditional data Data often dispersed in location
Workflow modeling Specification Simulation Optimization
15-48
Advanced Transaction Management
Conversational transactions Transactions with complex structure Transactions involving legacy systems Compensating transactions More flexible transaction processing