Transaction Management and

Concurrency Control
What Is a Transaction?

•A transaction is a logical unit of work

that must be either entirely completed or aborted;
no intermediate states are acceptable.
•Most real-world database transactions are formed by
two or more database requests.
•A database request
is a single SQL statement in an application program or
transaction.
What Is a Transaction?
•A consistent state
is one which all data integrity constraints are satisfied.
•A transaction that changes the contents of the database
must alter the database
from one consistent database state to another.
•To ensure consistency of the database,
every transaction must begin with the database in a known
consistent state.

consistent consistent
transaction
state state

database requests
Example Of A Transaction
●Asthis transaction is taking place,
the DBMS must ensure that no other transaction access X.

Read Modify Write

Figure 9.1
Example Of A Transaction
● A transaction is a logical unit of work
that must be either entirely completed or aborted

Y = 50

X = X - 10
Y = Y + 10

Y = 60
What Is a Transaction?
•Evaluating Transaction Results
•Examining the current balance for an account:
SELECT ACC_NUM, ACC_BALANCE
FROM CHECKACC
WHERE ACC_NUM = ‘0908110638’;

•represents a transaction because we accessed the

database.
•The database remains in a consistent state after the
transaction, because it did not alter the database.
 What Is a Transaction?
•Evaluating Transaction Results
•An accountant wishes to register the credit sale of 100 units
of product X to customer Y in the amount of $500.00:
• Reducing product X’s Quantity on hand by 100.
• Adding $500.00 to customer Y’s accounts receivable.

A real-world UPDATE PRODUCT

transaction SET PROD_QOH = PROD_QOH - 100
WHERE PROD_CODE = ‘X’;
UPDATE ACCREC
SET AR_BALANCE = AR_BALANCE + 500
WHERE AR_NUM = ‘Y’;

• If the above two transactions are not completely executed, the

transaction yields an inconsistent database.
• The DBMS must be able to recover the database
to a previous consistent state.
What Is a Transaction?

•Evaluating Transaction Results

•The DBMS does not guarantee
that the semantic meaning of the transaction truly
represents the real-world event.
• Although the syntax of the following UPDATE command is
correct, its use yields incorrect results.

UPDATE PRODUCT
SET PROD_QOH = PROD_QOH + 10
WHERE PROD_CODE = ‘X’;
 What Is a Transaction?
•Transaction Properties
•Atomicity
• All transaction operations must be completed
• Incomplete transactions aborted
•Durability
permanence of the database’s consistent state.
•Serializability
• concurrent transactions are treated as though they were executed in serial
order (one after another).
• important in multi-user and distributed databases.
•Isolation
means that the data used during the execution of a transaction
cannot be used by a second transaction until the first one is
completed.
 What Is a Transaction?
•Transaction Management with SQL
•Transaction support is provided
• COMMIT
• ROLLBACK
•When a transaction sequence is initiated,
it must continue through all succeeding SQL statements until one
of the following four events occurs:
• A COMMIT statement is reached.
The COMMIT statement automatically ends the SQL transaction.
• A ROLLBACK statement is reached.
• The end of a program is successfully reached ( = COMMIT).
• The program is abnormally terminated ( = ROLLBACK).
What Is a Transaction?
•Transaction Management with SQL
• Example:
UPDATE PRODUCT
SET PROD_QOH = PROD_QOH - 100
WHERE PROD_CODE = ‘345TYX’;
UPDATE ACCREC
SET AR_BALANCE = AR_BALANCE + 3500
WHERE AR_NUM = ‘60120010’;
COMMIT;

• If UPDATE is the application’s last action and the application

terminates normally
COMMIT is not necessary
• A transaction begins implicitly when the first SQL statement is
encountered.
Some SQL (not follow ANSI) use: BEGIN TRANSACTION;
What Is a Transaction?
•The Transaction Log
• A transaction log
keeps track of all transactions that update the database.
• The information stored in the log is used by the DBMS
for a recovery requirement triggered by a ROLLBACK statement or a
system failure.
• The transaction log
stores before-and-after data about the database and any of the tables,
rows, and attribute values that participated in the transaction.
• The transaction log is itself a database, and it is managed by the DBMS
like any other database.
A Transaction Log

Before After

Table 9.1
Concurrency Control

•Concurrency control
coordinates simultaneous execution of transactions
in a multiprocessing database.
•The objective of concurrency control is to
ensure the serializability of transactions in a multi-user
database environment.
•Simultaneous execution of transactions over a shared
database can create several data integrity and
consistency problems:
• Lost Updates.
• Uncommitted Data.
• Inconsistent retrievals.
Concurrency Control
•Lost Updates
•Two concurrent transactions update PROD_QOH:
TRANSACTION COMPUTATION
T1: Purchase 100 units PROD_QOH = PROD_QOH + 100
T2: Sell 30 units PROD_QOH = PROD_QOH - 30

•See Table 9.2 for the serial execution under normal

circumstances.
•See Table 9.3 for the lost update problems resulting
from the execution of the second transaction before the
first transaction is committed.
 Concurrency Control
•Uncommitted Data
•Data are not committed when
two transactions T1 and T2 are executed concurrently and the
first transaction is rolled back after
the second transaction has already accessed the uncommitted
data –
thus violating the isolation property of the transaction.

TRANSACTION COMPUTATION
T1: Purchase 100 units PROD_QOH = PROD_QOH + 100 (Rolled back)
T2: Sell 30 units PROD_QOH = PROD_QOH - 30
Concurrency Control

•Inconsistent Retrievals
•Inconsistent retrievals occur when a transaction
calculates some summary (aggregate) functions over a
set of data while other transactions are updating the
data.

•Example:
• T1 calculates the total quantity on hand of the products stored in
the PRODUCT table.
• At the same time, T2 updates the quantity on hand (PROD_QOH)
for two of the PRODUCT table’s products.
Retrieval During Update

T1 T2

Table 9.6
Transaction Results: Data Entry Correction

Table 9.7
Inconsistent Retrievals

Table 9.8
Concurrency Control
•The Scheduler
•The scheduler establishes the order in which the operations
within concurrent transactions are executed.
•The scheduler interleaves the execution of database operations
to ensure serializability.
•To determine the appropriate order, the scheduler bases its
actions on concurrency control algorithms, such as locking or
time stamping methods.
•The scheduler also makes sure that the computer’s CPU is used
efficiently.
 Read/Write Conflict Scenarios:
Conflicting Database Operations Matrix

• T1 and T2 are executed concurrently over the same data.

Table 9.9
 Concurrency Control with
Locking Methods
•Concurrency can be controlled using locks.
•A lock guarantees exclusive use of a data item to a current
transaction.
•A transaction acquires a lock prior to data access; the lock is
released (unlocked) when the transaction is completed.
•All lock of information is managed by a lock manager.
 Concurrency Control with
Locking Methods
•Lock Granularity
•Lock granularity indicates the level of lock use.
•Database level (See Figure 9.2)
•Table level (See Figure 9.3)
•Page level (See Figure 9.4)
•Row level (See Figure 9.5)
•Field level
 A Database-Level Locking Sequence
• T1 and T2 cannot access the same database concurrently,
even if they use different tables.

T1( Update Table A ) T2( Update Table B )

Figure 9.2
 An Example Of A Table-Level Lock
•T1 and T2 cannot access the same table concurrently,
even if they use different rows.

T1( Update Row 5 ) T2( Update Row 30 )

Figure 9.3
 An Example Of A Page-Level Lock
•T1 and T2 cannot access the same page concurrently,
even if they use different rows.
• the most frequently used multiuser DBMS locking methods.

Figure 9.4
 An Example Of A Row-Level Lock
• Although it improves the availability of data,
its management requires high overhead cost.

row

Figure 9.5
Concurrency Control with
Locking Methods
•Lock types
•Binary Locks
•Shared/Exclusive Locks

•Binary Locks
• A binary lock has only two states:
locked (1) or unlocked (0).
• If an object is locked by a transaction,
no other transaction can use that object.
• If an object is unlocked, any transaction can lock the object for its use.
• A transaction must unlock the object
after its termination.
An Example Of A Binary Lock

Table 9.10
 Concurrency Control with Locking
Methods
•Shared/Exclusive Locks
(1)Exclusive Locks
•An exclusive lock exists when access is specially reserved for
the transaction that locked the object.
•The exclusive lock must be used
when the potential for conflict exists.

issued when a transaction wants to

update unlocked data item.
 Concurrency Control with Locking
Methods
(2)Shared Locks
•A shared lock exists
when concurrent transactions are granted READ access on the basis
of a common lock.
•A shared lock produces no conflict as long as the concurrent
transactions are read only.

issued when a transaction wants to

read data and no exclusive lock is held on that data item.
 Concurrency Control with Locking
Methods
•Shared/Exclusive Locks
▪Although the possibility of shared locks renders data access more
efficient,
a shared/exclusive lock schema increases the lock manager’s
overhead.
•Three lock operations needed:
• READ_LOCK(check the type of lock)
• WRITE_LOCK(issue the lock)
• UNLOCK(release the lock)
Concurrency Control with Locking Methods
•Shared/Exclusive Locks

Current Shared Exclusive

Unlock
Want to Lock Lock

Shared Shared
Read Wait
Lock Lock

Exclusive
Write Wait Wait
Lock
 Concurrency Control with Locking
Methods
•Although locks prevent serious data inconsistencies, Potential
Problems with Locks
•The resulting transaction schedule may not be serializable.
•The schedule may create deadlocks.
•Solutions
•Two-phase locking for the serializability problem.
•Deadlock detection and prevention techniques for the deadlock
problem.
 Concurrency Control with Locking
Methods
•Two-Phase Locking
•The two-phase locking protocol defines how transactions
acquire and relinquish locks.
It guarantees serializability,
but it does not prevent deadlocks.
•In a growing phase,
a transaction acquires all the required locks without
unlocking any data.
Once all locks have been acquired,
the transaction is in its locked point.
•In a shrinking phase,
a transaction releases all locks and cannot obtain any new
locks.
 Concurrency Control with Locking
Methods
•Rules for Two-Phase Locking Protocol
•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.
Two-Phase Locking Protocol

Figure 9.6
 Concurrency Control with Locking
Methods
•Deadlocks (Deadly Embrace)
•Occurs when two transactions wait for each other to
unlock data
•Deadlocks exist when two transactions T1 and T2 exist in
the following mode:
holds T1 requests
T1 = requests X and holds Y
T2 = requests Y and holds X
Y X

•If T1 has not unlocked data item Y, T2 cannot begin; and, if

requests T2 holds
T2 has not unlocked data item X, T1 cannot continue. (See
Table 9.11)
 How A Deadlock Condition Is Created

Table 9.11
 Four Conditions for Deadlock
● All four of these conditions must be present :
1. Mutual exclusion condition
• each resource assigned to 1 process or is available
2. Hold and wait condition
• process holding resources can request additional
3. No preemption condition
• previously granted resources cannot forcibly taken away
4. Circular wait condition
• must be a circular chain of 2 or more processes
• each is waiting for resource held by next member of the
chain
 Concurrency Control with Locking
Methods
•Three Techniques to Control Deadlocks:
• Deadlock Prevention
A transaction requesting a new lock is aborted
if there is a possibility that a deadlock can occur.
• Deadlock Detection
The DBMS periodically tests the database for deadlocks.
If a deadlock is found, one of the transactions (“victim”) is aborted, and
the other transaction continues.
• Deadlock Avoidance
The transaction must obtain all the locks it needs before it can be
executed.
Avoid deadlocks by allocating resources carefully.
 Concurrency Control with
Time Stamping Methods
•The time stamping approach
assigns a global unique time stamp to each transaction to
schedule concurrent transactions.
•The time stamp value produces an explicit order in which
transactions are submitted to the DBMS.
•Time stamps must have two properties:
• Uniqueness
assures that no equal time stamp values can exist.
• Monotonicity
assures that time stamp values always increase.
•The DBMS executes conflicting operations
in time stamp order to ensure the serializability.
 Concurrency Control with Optimistic
Methods

•Optimistic Methods
•It is based on the assumption
that the majority of the database operations do not conflict.
•A transaction is executed without restrictions
until it is committed.
 Concurrency Control with Optimistic
Methods
•Optimistic Methods
• Each transaction moves through two or three phases:
• Read Phase:
The transaction reads the database, executes the needed computations, and
makes the updates to a private copy of the database values.
All updates of the transaction are recorded in a temporary update file.
• Validation Phase:
The transaction is validated to assure that the changes made will not affect the
integrity and consistency of the database.
• Write Phase:
The changes are permanently applied to the database.
• acceptable for mostly read or query database systems that require very
few update transaction.
Database Recovery Management
•Recovery restores a database
from a given state, usually inconsistent,
to a previously consistent state.
•Recovery techniques are based on the atomic transaction
property:
All portions of the transaction must be applied and completed to produce a
consistent database.
If, for some reason, any transaction operation cannot be completed, the
transaction must be aborted, and any changes to the database must be
rolled back.
Database Recovery Management

•Levels of Backup
•Full backup of the database
It backs up or dumps the whole database.

•Differential backup of the database

Only the last modifications done to the database are copied.

•Backup of the transaction log only

It backs up all the transaction log operations that are not reflected
in a previous backup copy of the database.
 Database Recovery Management
•Database Failures
•Software
Operating system, DBMS, application programs, viruses
•Hardware
Memory chip errors, disk crashes, bad disk sectors, disk full errors
•Programming Exemption
Application programs (a withdrawal of zero balance account)

•Transaction
Deadlocks
•External
Fire, earthquake, flood
Database Recovery Management
•Transaction Recovery Procedures:
•Deferred-write/Deferred-update
Transaction operations do not immediately update the database. Instead,
all changes are written to the transaction log.
The database is updated only after the transaction reaches its commit
point.

•Write-through/Immediate-update
The database is immediately updated by transaction operations during
the transaction’s execution, even before the transaction reaches its
commit point.
The transaction log is also updated.
If a transaction fails, the database uses the log information to roll back the
database.