Transcript 9 - CSUB Home Page

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?
Locking Schemes
9
• 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
Two-Phase Locking
9
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.)
Slide #16
DBMS updates
database with
Elena’s change
DBMS unlocks
record
9
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
Different than
backward
What would be the
answer of the question
on page 239 if the
catastrophe occurred
after 9:00pm?
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