Transcript Document

9
Chapter 9
Transaction Management and
Concurrency Control
Database Systems:
Design, Implementation, and Management,
Sixth Edition, Rob and Coronel
1
9
In this chapter, you will learn:
• What a database transaction is and what its
properties are
• How database transactions are managed
• What concurrency control is and what role it
plays in maintaining the database’s integrity
• What locking methods are and how they work
• How database recovery management is used to
maintain database integrity
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
2
9
9.1 What is a Transaction?
• A transaction is any action that reads from and/or
writes to a database may consist of
– Simple SELECT statement to generate a list of table
contents
– A series of related UPDATE statements to change the
values of attributes in various tables
– A series of INSERT statements to add rows to one or
more tables
– A series of DELETE statements to add rows to one or
more tables
– A combination of SELECT, UPDATE, INSERT, and
DELETE statements
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
3
9
What is a Transaction? (continued)
• A transaction is a logical unit of work that must be
either entirely completed or aborted
• Successful transaction changes the 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
– A request is the equivalent of a single SQL statement
in an application program or transaction
– Each request generate several I/O operations that read
from or write to physical storage media
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
4
9
Assume the
transactions
are
semantically
correct
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
5
9
Evaluating Transaction Results
• Not all transactions update the database
• SQL code represents a transaction because
database was accessed
• Improper or incomplete transactions can have
a devastating effect on database integrity
– Some DBMSs provide means by which user
can define enforceable constraints based on
business rules
– Other integrity rules are enforced
automatically by the DBMS when table
structures are properly defined, thereby letting
the DBMS validate some transactions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
6
9
Figure 9.2
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
7
9
9.1.2 Transaction Properties
• ACID: Atomicity, Consistency, Isolation, Durability
• Atomicity
– Requires that all operations (SQL requests) of a
transaction be completed; if not, the transaction is
aborted
• Consistency (called Serializability in this textbook)
– Ensures that the concurrent execution of several
transactions yields consistent results
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
8
9
Transaction Properties (continued)
• Isolation
– Data used during execution of a transaction
cannot be used by a second transaction until
the first one is completed
• Durability
– Indicates permanence of database’s
consistent state
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
9
Transaction Management with SQL
•
ANSI has defined standards that govern SQL
database transactions
•
Transaction support is provided by two SQL
statements: COMMIT and ROLLBACK
•
ANSI standards require that, when a transaction
sequence is initiated by a user or an application
program,
–
9
it must continue through all succeeding SQL statements until
one of four events occurs
1. A COMMIT is reached
2. A ROLLBACK is reached
3. The end of program is successfully reached
4. The program is abnormally terminated
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
10
9.1.4 The Transaction Log
9
• It is for recovery triggered by a ROLLBACK, a
program’s abnormal termination, or a system failure
– After a server failure, Oracle automatically rolls back
uncommitted transactions and rolls forward
transactions committed but not written to storage
• The transaction log stores
– A record for the beginning of transaction
– For each transaction component (SQL statement)
• Type of operation being performed (update, delete,
insert)
• Names of objects affected by the transaction (the name
of the table)
• “Before” and “after” values for updated fields
• Pointers to previous and next transaction log entries for
the same transaction
– The ending (COMMIT) of the transaction
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
11
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
12
9
9.2 Concurrency Control
• Coordination of simultaneous transaction
execution in a multiprocessing database
system
• Objective is to ensure transaction
serializability in a multi-user database
environment
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
13
9
Concurrency Control
– Important : simultaneous execution of
transactions over a shared database can
create several data integrity and consistency
problems when no concurrency control is
imposed
• lost updates
• uncommitted data
• inconsistent retrievals
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
14
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
15
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
16
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
17
9
Violation
of
isolation
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
18
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
19
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
20
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
21
9
9.2.4 The Scheduler
• The scheduler is a special DBMS program:
establishes order of operations within which
concurrent transactions are executed
• It interleaves the execution of database
operations to ensure serializability and
isolation of transactions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
22
9
The Scheduler (continued)
• It bases its actions on concurrency control
algorithms, like locking or time stamping
• Ensures computer’s central processing unit
(CPU) is used efficiently
• Facilitates data isolation to ensure that two
transactions do not update the same data
element at the same time
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
23
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
24
9
9.3 Concurrency Control
with Locking Methods
• Lock
– Guarantees exclusive use of a data item to a current
transaction
• A transaction acquires lock prior to data access; the lock
is released (unlocked) when the transaction is completed
– Required to prevent another transaction from reading
inconsistent data
• Lock manager
– Responsible for assigning and policing the locks used
by the transactions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
25
9
Lock Granularity
• Indicates the level of lock use
• Locking can take place at the following levels:
– Database
– Table
– Page
– Row
– Field (attribute)
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
26
9
Lock Granularity (continued)
• Database-level lock
– Entire database is locked, good for batch processes
• Table-level lock
– Entire table is locked, not suitable for mutiuser DBMSs
• Page-level lock
– Entire diskpage is locked, most frequently used
– A table may span several pages, and a page may
contain several rows of one or more tables
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
27
9
Lock Granularity (continued)
• Row-level lock
– Allows concurrent transactions to access different rows
of the same table, even if the rows are located on the
same page
– Its management requires high overhead cost
• Field-level lock
– Allows concurrent transactions to access the same row,
as long as they require the use of different fields
(attributes) within that row
– Rarely done because of extremely high overhead
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
28
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
29
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
30
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
31
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
32
9
9.3.2 Lock Types
• Binary lock
– Has only two states: locked (1) or unlocked (0)
– Too restrictive
• Exclusive lock
– Access is specifically reserved for the transaction that
locked the object
– Must be used when the potential for conflict exists
– Mutual exclusive rule: only one transaction at a time
can own an exclusive lock on the same object
• Shared lock
– Concurrent transactions are granted Read access on
the basis of a common lock
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
33
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
34
9
Shared / Exclusive Locks
• A shared/exclusive lock schema increases the lock
manager’s overhead in
– The type of lock held must be known before a lock can
be granted
– Three lock operations exist: READ_LOCK,
WRITE_LOCK, and UNLOCK
– The schema has been enhanced to allow a lock
upgrade or a lock downgrade
• Major problems of locks
– The resulting schedule may not be serializable
(example: inconsistent retrieval)
– The schedule may create deadlocks
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
35
9.3.3 Two-Phase Locking
to Ensure Serializability
9
• Defines how transactions acquire and
relinquish locks
• Guarantees serializability, but it does not
prevent deadlocks.
• The two phases are
– Growing phase, in which a transaction
acquires all the required locks without
unlocking any data
– Shrinking phase, in which a transaction
releases all locks and cannot obtain any new
lock
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
36
9
Two-Phase Locking
to Ensure Serializability (continued)
• Governed by the following 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—that is, until the transaction is in its
locked point
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
37
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
38
9
9.3.4 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
• Control through
– Prevention: recommended if the deadlock probability is high
– Detection: recommended if the deadlock probability is low
– Avoidance: obtains all locks before it can be executed. Might
be employed If response time is not high on the system
priority list.
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
39
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
40
9.4 Concurrency Control
with Time Stamping Methods
9
• Assigns a global unique time stamp to each
transaction
• Produces an explicit order in which transactions are
submitted to the DBMS
• Must have two properties
– Uniqueness : Ensures that no equal time stamp values
can exist
– Monotonicity : Ensures that time stamp values always
increase
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
41
9
Concurrency Control
with Time Stamping Methods
• All database operations within the same transaction
must have the same time stamp
• DBMS executes conflicting operations in time stamp
order
• If two transactions conflict, one is stopped, rolled
back, rescheduled, and assigned a new time stamp
• Each value stored in the database requires two
additional time stamp fields:
– One for the last the value was read, and one for the
last update
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
42
9
Wait/Die and Wound/Wait Schemes
• Wait/die
– If the transaction requesting the lock (operation) is the older
one, the older transaction waits until the other is completed
– If the transaction requesting the lock is the younger one, the
younger transaction dies (is rolled back) and rescheduled
using the same time stamp
• Wound/wait
– If the transaction requesting the lock is the older one, the
older transaction will preempt (wound) the younger one. The
younger one is rescheduled with the same transaction.
– If the transaction requesting the lock is the younger one, the
younger transaction waits until th eolder one is completed
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
43
9
訂正: If the lock is not granted before the time-out
expires, the transaction is rolled back.
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
44
9
9.5 Concurrency Control
with Optimistic Methods
• Optimistic approach
– Based on the assumption that the 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 are read, validation, and write
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
45
9
Concurrency Control
with Optimistic Methods
– During the read phase, the transaction reads the
database, executes the needed computations, and
makes the update to a private copy of the database
value
– During the validation phase, the transaction is
validated to ensure that the changes made will not
affect the integrity and consistency of the database
– During the write phase, the changes are permanently
applied to the database
• Useful for mostly read, very few update transactions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
46
9
9.6 Database Recovery Management
• Database recovery
– Restores database from a given state, usually
inconsistent, to a previously consistent state
– Based on the atomic transaction property
• All portions of the transaction must be treated
as a single logical unit of work, in which all
operations must be applied and completed to
produce a consistent database
– If transaction operation cannot be completed,
transaction must be aborted, and any changes
to the database must be rolled back (undone)
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
47
9
Database Recovery Management
• The level of backup
– Full backup
– Differential backup
– Transaction log backup
• Database backup is stored in a secure, protected
different building
• Failures that plague databases and systems are
induced by
– Software, Hardware, Programming exemption,
Transaction, or External factors
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
48
9
Database Recovery Management
• Recovery scenario
– Determine the type and the extent of the
required recovery
– If the entire database needs to be recovered,
use the most recent backup copy of the
database in a known consistent state
– The backup copy is rolled forward to restore
all subsequent transactions by using
transaction log. Use the transaction log to
“undo” all the un-committed transactions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
49
9
9.6.1 Transaction Recovery
• Four important concepts that affect the recovery
process
– The write-ahead-log protocol
• Transaction logs are always written before any database
data are actually updated
– Redundant transaction logs
– Database buffers
• When a transaction updates data, it actually updates the
copy of the data in the memory buffer
– Database checkpoints
• An operation in which the DBMS writes all of its updated
buffers to disk
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
50
9
Transaction Recovery
• Makes use of deferred-write and write-through
• Deferred write
– Transaction operations do not immediately
update the physical database
– Only the transaction log is updated
– Database is physically updated only after the
transaction reaches its commit point using the
transaction log information
– If the transaction aborts before it reaches its
commit point, no change need to be made to
the database
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
51
9
Deferred Write
• Its recovery process
– Identify the last checkpoint in the transaction log
– For a transaction that started and committed before the
last checkpoint, nothing needs to be done
– For a transaction that performed a commit operation
after the last checkpoint, redo the transaction and
update the database, applied from the oldest to the
newest transactions.
– For any transaction with a ROLLBACK operation after
the last checkpoint or that was left active (with neither
a COMMIT nor a ROLLBACK) before the failure
occurred, nothing needs to be done
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
52
9
Transaction Recovery (continued)
• Write-through
– Database is immediately updated by
transaction operations during the transaction’s
execution, even before the transaction
reaches its commit point
– If a transaction aborts before it reaches its
commit point, a ROLLBACK or undo operation
needs to be done
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
53
9
Write-through
• Its recovery process
– Identify the last checkpoint in the transaction log
– For a transaction that started and committed before the
last checkpoint, nothing needs to be done
– For a transaction that performed a commit operation
after the last checkpoint, redo the transaction and
update the database, applied from the oldest to the
newest transactions.
– For any transaction with a ROLLBACK operation after
the last checkpoint or that was left active (with neither
a COMMIT nor a ROLLBACK) before the failure
occurred, ROLLBACK or undo the operations, applied
from the newest to the oldest transactions.
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
54
9
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
55
9
Summary
• Transaction
– Sequence of database operations that access
the database
– Represents real-world events
– Must be a logical unit of work
• No portion of the transaction can exist by itself
– Takes a database from one consistent state to
another
• One in which all data integrity constraints are
satisfied
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
56
9
Summary (continued)
• SQL provides support for transactions
through the use of two statements: COMMIT
and ROLLBACK
• Concurrency control coordinates the
simultaneous execution of transactions
• Scheduler is responsible for establishing
order in which concurrent transaction
operations are executed
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
57
9
Summary (continued)
• Lock guarantees unique access to a data
item by a transaction
• Database recovery restores the database
from a given state to a previous consistent
state
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
58