9

Chapter 7

Transaction Management
and Concurrency Control
 9

Study Objectives
• Understand what a database transaction is and what
its properties are
• Identify how database transactions are managed
• Learn what concurrency control is and what role it
plays in maintaining the database’s integrity
• Learn what locking methods are and how they work
• Understand how database recovery management is
used to maintain database integrity
 9

Fundamental capability of a DBMS

• Users don’t need to know how data is stored

or manipulated
• Users should be able to add, change, and
delete records during updates
• Users should be able to view and manipulate
data during retrieval
 9

What is a Transaction?
• A set of steps completed by a DBMS to accomplish a
single user task.
• Must be either entirely completed or aborted
• No intermediate states are acceptable
 9

Transaction Properties
• Atomicity
– All transaction operations must be completed
– Incomplete transactions must be aborted
• Durability
– Once a transaction is completed, its changes are made is
permanent.
• Serializability
– Conducts transactions in serial order
– Important in multi-user and distributed databases
• Isolation
– Transaction data cannot be used until its execution is
completed
 9

Transaction Management with SQL

• Transaction support
– COMMIT
– ROLLBACK
• User initiated transaction sequence must
continue until:
– COMMIT statement is reached
– ROLLBACK statement is reached
– End of a program reached
– Program reaches abnormal termination
 9

Concurrency Control
• Coordinates simultaneous transaction
execution in multiprocessing database
– Ensure serializability of transactions in multiuser
database environment
– Solve potential problems in multiuser
environments
• Lost updates
• Uncommitted data
• Inconsistent retrievals
Best scenario of updating data 9
Read from this slide to slide #11
Before update

DBMS reads
data from
database into
RAM for Ryan

Ryan
changes data
in RAM

DBMS updates
database with
Ryan’s change
Best scenario of updating data 9

After Ryan’s
update and
before Elena’s

DBMS reads
database data into
RAM for Elena

Elena changes
data in RAM

DBMS updates
database with
Elena’s change
Concurrency Control problem 9

Database before
updates

DBMS reads
database data into
RAM for Ryan

DBMS reads
database data into
RAM for Elena

Ryan changes
data in RAM
Concurrency Control problem 9
Slide #11

Elena updates
data in RAM

DBMS updates
database with
Ryan’s change

DBMS updates
database with
Elena’s change;
Ryan’s update is
lost!
 9

How to avoid previous slide problem??

• Simply use of batch processing concept

– Update all data once a day
• Considered as one phase locking

• What is a problem with this method?

 9
Locking Schemes

• Two-Phase Locking
– Locks are held until required updates completed
• Deadlock
– Occurs when two users hold more than one lock at a
time
– DBMS chooses method to break deadlock
– One user becomes ‘victim’
• Locking on PC-Based DBMSs
– Table or row locked, not both
– Usually more limited than locking facilities on
mainframe DBMSs
 9
Two-Phase Locking
Read from this slide to slide #16

Database before
updates

DBMS reads database

data into RAM for Ryan
and locks record

Elena requests same

record and request
fails

Ryan changes data

in RAM; Elena’s
request for same
record again fails
Two-Phase Locking (con’t.) 9

DBMS updates
database with Ryan’s
change; Elena’s
request for same
record again fails

DBMS unlocks record;

DBMS reads database
data into RAM for
Elena and locks record

Elena changes data

in RAM
Two-Phase Locking (con’t.) 9
Slide #16

DBMS updates
database with
Elena’s change

DBMS unlocks
record
 9

Two-Phase Locking Protocol

• Growing phase
• Shrinking phase
• 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
 9

Two-Phase Locking Protocol

 9

Database-Level Locking Sequence

 9

Table-Level Lock Example

 9

Page-Level Lock Example

 9

Row-Level Lock Example

Lock row 2 request

 9

Deadlocks
• Occurs when two transactions wait for
each other to unlock data
• Called deadly embrace
• Control techniques
– Abort entire transactions..
– Abort most recent transaction
– Abort transactions that require least
changes
 9

Deadlock
 9
How Deadlock Conditions Created
 9

Time Stamping Methods

• Helps detect and resolve deadlocks
• DBMS assigns a unique time when the
update started
– Last time field read
– Last update time
• DBMS executes conflicting operations in time
stamp order
• Eliminates processing time needed to apply
and release locks
 9
Recovery
• Mechanism for recovering damaged database
• The return of database to correct state is called
recovery
• Simplest recovery involves using backups
• Other recovery methods
– Journaling
– Forward recovery
– Backward recovery
– PC-based
 9
Forward Recovery

What would be the

answer of the question
on page 239 if the
catastrophe occurred
Different than after 9:00pm?
backward
 9

Forward Recovery

• Useful when a catastrophe destroys the

database.
– Since the Db is no longer current, the DBA
executes a DBMS recovery program that applies
the after committed transactions from the log to
bring the database up to date.
– Figure in next slide
 9
Backward Recovery
 9

Backward recovery

• Useful when the DB has not actually been

destroyed.
– The DB is still valid (unlike forward recovery case)
– Only transactions are either incorrect or in the
midstream
– Figure in next slide
– Still possible to use “roll back – see SQL note for
more details” as a recovery mechanism.
– Roll back: go back to the previous state
 9

Data Replication

• Manage multiple copies of same data in

multiple locations

• Maintained for performance or other

reasons

• Ease of access and portability

 9
Replicas From Master Database
 9
DBMS Synchronizes Databases