Chapter 9
TRANSACTION MANAGEMENT AND
CONCURRENCY CONTROL
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
2
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
3
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
4
The Relational Schema for the
Ch09_SaleCo Database
5
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
6
Tracing the Transaction in the
Ch09_SaleCo Database
Figure 9.2
7
Transaction Properties
Atomicity
Requires that all operations (SQL requests) of a
transaction be completed
Transaction-Level Atomicity: The entire goal of a transaction, a set of SQL statements executed together as
a unit of work, is to take the database from one consistent state to another consistent state. To accomplish
this goal, transactions are atomic as well—the entire set of successful work performed by a transaction is
either entirely committed and made permanent or rolled back and undone. Just like a statement, the
transaction is an atomic unit of work. Upon receipt of “success” from the database after committing a
transaction, you know that all of the work performed by the transaction has been made persistent.
Durability
Indicates permanence of database’s consistent state
8
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
9
Integrity Constraints and Transactions
Integrity Constraints and Transactions It is interesting to note exactly when integrity constraints are checked. By default, integrity
constraints are checked after the entire SQL statement has been processed. There are also deferrable constraints that permit
the validation of integrity constraints to be postponed until either the application requests they be validated by issuing a SET
CONSTRAINTS ALL IMMEDIATE command or upon issuing a COMMIT.
IMMEDIATE Constraints For the first part of this discussion, we’ll assume that constraints are in IMMEDIATE mode, which is the
norm. In this case, the integrity constraints are checked immediately after the entire SQL statement has been processed. Note
that I used the term “SQL statement,” not just “statement.” If I have many SQL statements in a PL/SQL stored procedure, each
SQL statement will have its integrity constraints validated immediately after its individual execution, not after the stored
procedure completes. So, why are constraints validated after the SQL statement executes? Why not during? This is because it is
very natural for a single statement to make individual rows in a table momentarily inconsistent. Taking a look at the partial work
by a statement would result in Oracle rejecting the results, even if the end result would be OK. For example, suppose we have a
table like this:
EODA@ORA12CR1> create table t ( x int unique );
Table created.
EODA@ORA12CR1> insert into t values ( 1 );
1 row created.
EODA@ORA12CR1> insert into t values ( 2 );
1 row created.
EODA@ORA12CR1> commit;
Commit complete.
And we want to execute a multiple-row UPDATE:
EODA@ORA12CR1> update t set x=x-1;
2 rows updated.
10
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
11
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
12
A Transaction Log
13
Concurrency Control
Coordination of simultaneous transaction execution
in a multiprocessing database system
Objective is to ensure transaction serializability in a
multiuser database environment
14
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
15
Normal Execution of Two Transactions
16
Lost Updates
17
Correct Execution of Two Transactions
18
An Uncommitted Data Problem
19
Retrieval During Update
20
Transaction Results:
Data Entry Correction
21
Inconsistent Retrievals
22
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
23
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
24
Queries ?
25
