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
What is a Transaction?
• 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 combination of SELECT, UPDATE, and
INSERT statements
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
3
9
What is a Transaction? (continued)
• 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
– The equivalent of a single SQL statement in
an application program or transaction
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
4
9
The Relational Schema for the
Ch09_SaleCo Database
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
Tracing the Transaction in the
Ch09_SaleCo Database
Figure 9.2
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
7
9
Transaction Properties
• Atomicity
– Requires that all operations (SQL requests) of
a transaction be completed
• Durability
– Indicates permanence of database’s
consistent state
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
8
9
Transaction Properties (continued)
• Serializability
– Ensures that the concurrent execution of
several transactions yields consistent results
• Isolation
– Data used during execution of a transaction
cannot be used by second transaction until
first one is completed
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
9
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,
– it must continue through all succeeding SQL
statements until one of four events occurs
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
10
9
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
A Transaction Log
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
12
9
Concurrency Control
• Coordination of simultaneous transaction
execution in a multiprocessing database
system
• Objective is to ensure transaction
serializability in a multiuser 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
• lost updates
• uncommitted data
• inconsistent retrievals
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
14
9
Normal Execution of Two Transactions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
15
9
Lost Updates
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
16
9
Correct Execution of Two Transactions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
17
9
An Uncommitted Data Problem
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
18
9
Retrieval During Update
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
19
9
Transaction Results:
Data Entry Correction
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
20
9
Inconsistent Retrievals
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
21
9
The Scheduler
• Special DBMS program: establishes order of
operations within which concurrent
transactions are executed
• 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)
• Bases its actions on concurrency control
algorithms
• 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
Read/Write Conflict Scenarios:
Conflicting Database Operations Matrix
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
24
9
Concurrency Control
with Locking Methods
• Lock
– Guarantees exclusive use of a data item to a
current transaction
– 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
• Table-level lock
– Entire table is locked
• Page-level lock
– Entire diskpage is locked
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
• 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
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
28
9
A Database-Level Locking Sequence
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
29
9
An Example of a Table-Level Lock
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
30
9
Example of a Page-Level Lock
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
31
9
An Example of a Row-Level Lock
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
32
9
Lock Types
• Binary lock
– Has only two states: locked (1) or unlocked (0)
• Exclusive lock
– Access is specifically reserved for the
transaction that locked the object
– Must be used when the potential for conflict
exists
• 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
An Example of a Binary Lock
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
34
9
Two-Phase Locking
to Ensure Serializability
• Defines how transactions acquire and
relinquish locks
• Guarantees serializability, but it does not
prevent deadlocks
– 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
35
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
36
9
Two-Phase Locking Protocol
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
37
9
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
– Detection
– Avoidance
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
38
9
How a Deadlock Condition Is Created
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
39
9
Concurrency Control
with Time Stamping Methods
• Assigns a global unique time stamp to each
transaction
• Produces an explicit order in which
transactions are submitted to the DBMS
• 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
40
9
Wait/Die and Wound/Wait Schemes
• Wait/die
– Older transaction waits and the younger is
rolled back and rescheduled
• Wound/wait
– Older transaction rolls back the younger
transaction and reschedules it
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
41
9
Wait/Die and Wound/Wait
Concurrency Control Schemes
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
42
9
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
43
9
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
44
9
Transaction Recovery
• Makes use of deferred-write and writethrough
• 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
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
45
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
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
46
9
A Transaction Log for Transaction
Recovery Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
47
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
48
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
49
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
50