Database Systems: Design,
Implementation, and
Management
Eighth Edition
Transaction Management
and Concurrency Control
What is a Transaction?
• Logical unit of work that must be either entirely
completed or aborted
• Successful transaction changes database from
one consistent state to another
– One in which all data integrity constraints are
satisfied
• Most real-world database transactions are
formed by two or more database requests
– Equivalent of a single SQL statement in an
application program or transaction
Database Systems, 8th Edition 2
Database Systems, 8th Edition 3
Evaluating Transaction Results
• Not all transactions update database
• SQL code represents a transaction because
database was accessed
• Improper or incomplete transactions can have
devastating effect on database integrity
– Some DBMSs provide means by which user can
define enforceable constraints
– Other integrity rules are enforced automatically
by the DBMS
Database Systems, 8th Edition 4
Figure 9.2
Database Systems, 8th Edition 5
Transaction Properties
• Atomicity
– All operations of a transaction must be
completed
• Consistency
– Permanence of database’s consistent state
• Isolation
– Data used during transaction cannot be used by
second transaction until the first is completed
Database Systems, 8th Edition 6
Transaction Properties (continued)
• Durability
– Once transactions are committed, they cannot
be undone
• Serializability
– Concurrent execution of several transactions
yields consistent results
• Multiuser databases subject to multiple
concurrent transactions
Database Systems, 8th Edition 7
Transaction Management with SQL
• ANSI has defined standards that govern SQL
database transactions
• Transaction support is provided by two SQL
statements: COMMIT and ROLLBACK
• Transaction sequence must continue until:
– COMMIT statement is reached
– ROLLBACK statement is reached
– End of program is reached
– Program is abnormally terminated
Database Systems, 8th Edition 8
The Transaction Log
• Transaction log stores:
– A record for the beginning of transaction
– For each transaction component:
• Type of operation being performed (update,
delete, insert)
• Names of objects affected by transaction
• “Before” and “after” values for updated fields
• Pointers to previous and next transaction log
entries for the same transaction
– Ending (COMMIT) of the transaction
Database Systems, 8th Edition 9
Database Systems, 8th Edition 10
Concurrency Control
• Coordination of simultaneous transaction
execution in a multiprocessing database
• Objective is to ensure serializability of
transactions in a multiuser environment
Database Systems, 8th Edition 11
Lost Updates
• Lost update problem:
– Two concurrent transactions update same data
element
– One of the updates is lost
• Overwritten by the other transaction
Database Systems, 8th Edition 12
Database Systems, 8th Edition 13
Uncommitted Data
• Uncommitted data phenomenon:
– Two transactions executed concurrently
– First transaction rolled back after second already
accessed uncommitted data
Database Systems, 8th Edition 14
Database Systems, 8th Edition 15
Inconsistent Retrievals
• Inconsistent retrievals:
– First transaction accesses data
– Second transaction alters the data
– First transaction accesses the data again
• Transaction might read some data before they
are changed and other data after changed
• Yields inconsistent results
Database Systems, 8th Edition 16
Database Systems, 8th Edition 17
Database Systems, 8th Edition 18
Lock Granularity
• Indicates level of lock use
• Locking can take place at following levels:
– Database
– Table
– Page
– Row
– Field (attribute)
Database Systems, 8th Edition 19
Lock Granularity (continued)
• Database-level lock
– Entire database is locked
• Table-level lock
– Entire table is locked
• Page-level lock
– Entire diskpage is locked
Database Systems, 8th Edition 20
Lock Granularity (continued)
• Row-level lock
– Allows concurrent transactions to access
different rows of same table
• Even if rows are located on same page
• Field-level lock
– Allows concurrent transactions to access same
row
• Requires use of different fields (attributes) within
the row
Database Systems, 8th Edition 21
Database Systems, 8th Edition 22
Lock Types
• Binary lock
– Two states: locked (1) or unlocked (0)
• Exclusive lock
– Access is specifically reserved for transaction that
locked object
– Must be used when potential for conflict exists
• Shared lock
– Concurrent transactions are granted read access on
basis of a common lock
Database Systems, 8th Edition 23
Deadlocks
• Condition that occurs when two transactions
wait for each other to unlock data
• Possible only if one of the transactions wants
to obtain an exclusive lock on a data item
– No deadlock condition can exist among
shared locks
Database Systems, 8th Edition 24
Deadlocks (continued)
• Three techniques to control deadlock:
– Prevention
– Detection
– Avoidance
• Choice of deadlock control method depends
on database environment
– Low probability of deadlock, detection
recommended
– High probability, prevention recommended
Database Systems, 8th Edition 25
Database Systems, 8th Edition 26
Concurrency Control
with Time Stamping Methods
• Assigns global unique time stamp to each
transaction
• Produces explicit order in which transactions
are submitted to DBMS
• Uniqueness
– Ensures that no equal time stamp values can
exist
• Monotonicity
– Ensures that time stamp values always increase
Database Systems, 8th Edition 27
Wait/Die and Wound/Wait Schemes
• Wait/die
– Older transaction waits and younger is rolled
back and rescheduled
• Wound/wait
– Older transaction rolls back younger transaction
and reschedules it
Database Systems, 8th Edition 28
Database Systems, 8th Edition 29
Concurrency Control
with Optimistic Methods
• Optimistic approach
– Based on assumption that 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: read, validation, and write
Database Systems, 8th Edition 30
Database Recovery Management
• Restores database to previous consistent state
• Based on atomic transaction property
– All portions of transaction treated as single
logical unit of work
– All operations applied and completed to produce
consistent database
• If transaction operation cannot be completed
– Transaction aborted
– Changes to database rolled back
Database Systems, 8th Edition 31
Transaction Recovery
• Write-ahead-log protocol: ensures transaction
logs are written before data is updated
• Redundant transaction logs: ensure physical
disk failure will not impair ability to recover
• Buffers: temporary storage areas in primary
memory
• Checkpoints: operations in which DBMS writes
all its updated buffers to disk
Database Systems, 8th Edition 32
Transaction Recovery (continued)
• Deferred-write technique
– Only transaction log is updated
• Recovery process:
– Identify last checkpoint
– If transaction committed before checkpoint
• Do nothing
– If transaction committed after checkpoint
• Use transaction log to redo the transaction
– If transaction had ROLLBACK operation
• Do nothing
Database Systems, 8th Edition 33
Transaction Recovery (continued)
• Write-through technique
– Database is immediately updated by transaction
operations during transaction’s execution
• Recovery process
– Identify last checkpoint
– If transaction was committed before checkpoint
• Do nothing
– If transaction committed after last checkpoint
• DBMS redoes the transaction using “after” values
– If transaction had ROLLBACK or was left active
• Do nothing because no updates were made
Database Systems, 8th Edition 34
Database Systems, 8th Edition 35
