Transaction Management

1
Chapter 15

• Transaction Management

2
Outline

• Transaction basics
• Concurrency control
• Recovery management
• Transaction design issues
• Workflow management

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

4
Airline Transaction Example
START TRANSACTION Display greeting Get
reservation preferences from user SELECT
departure and return flight records If
reservation is acceptable then UPDATE seats
remaining of departure flight record UPDATE
seats remaining of return flight record INSERT
reservation record Print ticket if
requested End If On Error ROLLBACK COMMIT
5
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

6
Transaction Processing Services

• Concurrency control
• Recovery management
• Service characteristics
• Transparent
• Consume significant resources
• Significant cost component

7
Concurrency Control

• Problem definition
• Concurrency control problems
• Concurrency control tools

8
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

9
Lost Update Problem
10
Uncommitted Dependency Problem
11
Inconsistent Retrieval Problems

• Interference causes inconsistency among multiple
  retrievals of a subset of data
• Incorrect summary
• Phantom read
• Non repeatable read

12
Incorrect Summary Problem
13
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

14
Locking Granularity
15
Deadlock (Mutual Waiting)
16
Deadlock Resolution

• Detection
• Can involve significant overhead
• Not widely used
• Timeout
• Waiting limit
• Can abort transactions that are not deadlocked
• Widely used although timeout interval is
  difficult to determine

17
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

18
2PL Implementation
19
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

20
Recovery Management

• Device characteristics and failure types
• Recovery tools
• Recovery processes

21
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

22
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

23
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

24
Transaction Log Example
25
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

26
Other Recovery Tools

• Force writing
• Checkpoint time
• End of transaction
• Database backup
• Complete
• Incremental

27
Recovery from a Media Failure

• Restore database from the most recent backup
• Redo all committed transactions since the most
  recent backup
• Restart active transactions

28
Recovery Timeline
29
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

30
Immediate Update Recovery
31
Deferred Update Recovery
32
Transaction Design Issues

• Transaction boundary
• Isolation levels
• Deferred constraint checking
• Savepoints

33
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

34
Registration Form Example
35
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

36
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

37
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
38
Scholars 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 gt 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 gt 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)
39
Integrity Constraint Timing

• Most constraints checked immediately
• Can defer constraint checking to EOT
• SQL SET CONSTRAINTS statement

40
Save Points

• Some transactions have tentative actions
• SAVEPOINT statement determines intermediate
  points
• ROLLBACK to specified save points

41
Workflow Management

• Workflow description
• Enabling technologies
• Advanced transaction management

42
Workflows

• 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

43
Enabling Technologies

• Distributed object management
• Many kinds of non traditional data
• Data often dispersed in location
• Workflow modeling
• Specification
• Simulation
• Optimization

44
Advanced Transaction Management

• Conversational transactions
• Transactions with complex structure
• Transactions involving legacy systems
• Compensating transactions
• More flexible transaction processing

45
Summary

• Transaction user-defined collection of work
• DBMSs support ACID properties
• Knowledge of concurrency control and recovery
  important for managing databases
• Transaction design issues are important
• Transaction processing is an important part of
  workflow management