CHAPTER 8

Transaction
Management and
Concurrency Control
 LEARNING OBJECTIVES

• In this chapter, you will learn:

• About database transactions and
their properties
• What concurrency control is and what
role it plays in maintaining the
database’s integrity
• What locking methods are and how
they work
• What timestamp protocol about
 WHAT IS TRANSACTION?

• A transaction is a logical, atomic unit of work that

contains one or more SQL statements. A transaction
groups SQL statements so that they are either all
committed (COMMITTED), which means they are
applied to the database, or all rolled back, which
means they are undone (ROLLBACK) from the database
• A transaction could be an entire program, a portion
of a program or a single command.
• The concept of a transaction is inherently about
organizing functions to manage data.
• A transaction may be distributed (available on
different physical systems or organized into
different logical subsystems) and/or use data
concurrently with multiple users for different
 TRANSACTION

• A transaction represents a real-world event such as

the sale of a product
• A transaction must be a logical unit of work. For
example, the product sale has an effect on inventory
and if it is a credit sale, it has an effect on customer
balances.
• A transaction must take a database from one
consistent state to another. Therefore, all parts of a
transaction must be executed or the transaction
must be aborted. (A consistent state of the database
is one in which all data integrity constraints are
satisfied)
 TRANSACTION

• Acceptance of an incomplete transaction will yield an

inconsistent database state.
• To avoid such a state, the DBMS ensures that all of a
transaction’s database operations are completed
before they are committed to the database.
• For example, a credit sale requires minimum of 3
database operations:
1. An invoice is created for the sold product
2. The product’s inventory quantity on hand is reduced
3. The customer accounts payable balance is increased by the
amount listed on the invoice.
• If only part 1 and 2 are completed, the database will be left
in an inconsistent state. Unless all 3 completed, the entire
sales transactions is canceled
 EXAMPLES OF
TRANSACTIONS

CREATE TABLE ValueTable (id int);

BEGIN TRANSACTION;
INSERT INTO ValueTable VALUES(1);
INSERT INTO ValueTable VALUES(2);
ROLLBACK;

BEGIN TRANSACTION;
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
COMMIT;
EXAMPLE OF TRANS ACTION –
SQL STATEMENT

To illustrate the concept of a

transaction, consider a banking
database. When a bank customer
transfers money from a savings
account to a checking account,
the transaction can consist of
three separate operations:

- Decrement the savings account

- Increment the checking account
- Record the transaction in the
transaction journal
- End transaction
 STATEMENT EXECUTION AND TRANS ACTION
CONTROL

• A SQL statement that runs successfully is different

from a committed transaction. 
• Executing successfully means that a single
statement was:
• Parsed – one stage in SQL statement processing
• Found to be a valid SQL construction
• Run without error as an atomic unit. For example, all
rows of a multirow update are changed.
• However, until the transaction that contains the
statement is committed, the transaction can be
rolled back, and all of the changes of the statement
can be undone. A statement, rather than a
 TRANSACTION COMMITTED

• Committing means that a user has explicitly or implicitly

requested that the changes in the transaction be made
permanent.
• An explicit request occurs when the user issues
 COMMIT statement.
• An implicit request occurs after normal termination of an
application or completion of a data definition language
(DDL) operation.
• The changes made by the SQL statement(s) of a
transaction become permanent and visible to other
users only after that transaction commits.
• Queries that are issued after the transaction commits will
see the committed changes.
 STATEMENT-LEVEL ROLLBACK

• If at any time during execution a SQL statement causes an error, all

effects of the statement are rolled back. The effect of the rollback is as if
that statement had never been run. This operation is a statement-level
rollback.
• Errors discovered during SQL statement execution cause statement-
level rollbacks.
• An example of such an error is
• attempting to insert a duplicate value in a primary key. 
• Single SQL statements involved in a deadlock (competition for the same data)
can also cause a statement-level rollback.
• Errors discovered during SQL statement parsing, such as a syntax error, have
not yet been run, so they do not cause a statement-level rollback.
• A SQL statement that fails causes the loss only of any work it would have
performed itself. It does not cause the loss of any work that preceded it in
the current transaction. If the statement is a DDL statement, then the
implicit commit that immediately preceded as it is not undone.
STATE TRANSACTION
DIAGRAM
 TRANS ACTION MANAGEMENT
WITH SQL STATEMENT

• Hence, the recovery manager keeps track of the following transaction

states and operations:
• BEGIN_TRANSACTION: This marks the beginning of transaction execution.
• READ or WRITE: These specify read or write operations on the database
items that are executed as part of a transaction.
• END_TRANSACTION: This specifies that read and write operations have
ended and marks the end limit of transaction execution. However, at this
point it may be necessary to check whether the changes introduced by the
transaction can be permanently applied to the database (committed) or
whether the transaction has to be aborted because it violates concurrency
control, or for some other reason (rollback).
• COMMIT_TRANSACTION: This signals a successful end of the transaction so
that any changes (updates) executed by the transaction can be safely
committed to the database and will not be undone.
• ROLLBACK (or ABORT): This signals the transaction has ended
unsuccessfully, so that any changes or effects that the transaction may have
applied to the database must be undone.
 TRANSACTION PROPERTIES
(ACID PROPERTIES)
Atomicity
• All operations of a transaction must be completed
• If not, the transaction is aborted

Consistency
• A transaction is consistently processing if its complete
execution, take database from one consistent state to
another.
Isolation
• Data used during transaction cannot be used by second
transaction until the first is completed

Durability / Permanency
• Ensures that once transactions are committed, they cannot
be undone or lost. This is achieved by saving data related
to transaction in more than one places along with
database.

13
 WHAT IS CONCURRENCY
CONTROL?

• Concurrency control is a database management

systems (DBMS) concept that is used to address
conflicts with the simultaneous accessing or altering
of data that can occur with a multi-user system.
• Concurrency control, when applied to a DBMS, is
meant to coordinate simultaneous transactions while
preserving data integrity.
• The Concurrency is about to control the multiple user
access of the data in the database environment.
• Objective - Ensures serializability of
transactions in a multiuser database environment
 DEFINITION OF SERIALIZABILITY

• Serializability of transactions means that a series of

concurrent transactions will yield the same result as if they
were executed one after another
• Transaction log is a special DBMS table that contains a
description of all the database transactions executed by the
DBMS.
• The information stored in the log is used by the DBMS to recover
the database after a transaction is aborted or after a system
failure.
• The transaction log is usually stored in a different hard
disk/media to prevent failure caused by media error
• This log play crucial role in maintaining database concurrency
control and integrity
 THE SCHEDULER

• Establishes the order in which the operations

are executed within concurrent transactions
• Interleaves the execution of database
operations to ensure serializability and
isolation of transactions
• Based on concurrent control algorithms to
determine the appropriate order
• Creates serialization schedule
• Serializable schedule: Interleaved execution
of transactions yields the same results as the
serial execution of the transactions
16
 INTERLEAVING CONCURRENCY

• Many computer systems, including DBMSs, are used simultaneously by more

than one user.
• This means the computer runs multiple transactions (programs) at the same
time.
• For example:
• an airline reservations system is used by hundreds of travel agents and
reservation clerks concurrently.
• Systems in banks, insurance agencies, stock exchanges and the like are also
operated by many users who submit transactions concurrently to the system.
• If as is often the case, there is only one CPU, then only one program can be
processed at a time.
• To avoid excessive delays, concurrent systems execute some
commands from one program (transaction), then suspended that
program and execute some commands from the next program, and so
on.
• A program is resumed at the point where it was suspended when it gets its turn
to use the CPU again. This is known as interleaving.
EXAMPLE OF INTERLEAVING
CONCURRENCY
EXAMPLE OF INTERLEAVING

• The figure shows two

programs A and B executing
concurrently in an
interleaved fashion.
• Interleaving keeps the CPU
busy when an executing
program requires an input
or output (I/O) operation,
such as reading a block
of data from disk.
• The CPU is switched to
execute another program
rather than remaining
idle during I/O time.
 PROBLEMS IN CONCURRENCY
CONTROL

Lost update
• Occurs in two concurrent transactions when:
• Same data element is updated
• One of the updates is lost

Uncommitted data
• Occurs when:
• Two transactions are executed concurrently
• First transaction is rolled back after the second
transaction has already accessed uncommitted data

Inconsistent retrievals
• Occurs when a transaction accesses data before and
after one or more other transactions finish working
21
with such data
 METHODS USE TO ENSURE
CONCURRENCY CONTROL

• We have concurrency control protocols to

ensure atomicity, isolation, and serializability
of concurrent transactions.
• Concurrency control protocols can be broadly
divided into two categories −
• Lock based protocols
• Time stamp based protocols
 LOCK-BASED PROTOCOLS

• Database systems equipped with lock-based protocols use

a mechanism by which any transaction cannot read or
write data until it acquires an appropriate lock on it.
• Locks are of two kinds −
• Binary Locks − A lock on a data item can be in two
states; it is either locked or unlocked.
• Shared/exclusive − This type of locking mechanism
differentiates the locks based on their uses. If a lock is
acquired on a data item to perform a write operation, it is
an exclusive lock. Allowing more than one transaction to
write on the same data item would lead the database into
an inconsistent state. Read operation is a shared lock as
no data value is being changed during reading process.
 LOCK-BASED PROTOCOLS

• There are 2 types of protocols available −

1) Simplistic/Basic Lock Protocol
• Simplistic lock-based protocols allow transactions to
obtain a lock on every object before a 'write' operation is
performed. Transactions may unlock the data item after
completing the ‘write’ operation.
2) ) Two Phase Locking Protocol (2PL) – This
protocol is all about growing and shrinking of locks.
The phases are called as growing (locks are being
acquired) and shrinking as all the lacks are being
released respectively
 2 PHASE LOCK-BASED
PROTOCOLS

1. Conservative 2-Phase Lock Protocol

• Conservative 2PL protocols evaluate their operations and create a list
of data items on which they need locks.
• Before initiating an execution, the transaction requests the system for
all the locks it needs beforehand. If all the locks are granted, the
transaction executes and releases all the locks when all its operations
are over. If all the locks are not granted, the transaction rolls back and
waits until all the locks are granted.
 LOCK-BASED PROTOCOLS

2) Strict Two-Phase Locking

• The first phase of Strict-2PL is same as 2PL.
• After acquiring all the locks in the first phase, the
transaction continues to execute normally.
• But in contrast to 2PL, Strict-2PL does not release a lock
after using it. Strict-2PL holds all the locks until the
commit point and releases all the locks at a time.
1. Strict 2-Phase Lock Protocol

• The execution takes place in 3 steps:

• As the transaction takes place, it keeps requesting or seeking the
permission for the lock it needs.
• Transaction acquires all the locks.
• Transaction releases the locks , this phase starts when it releases its
first lock.
FIGURE 10.7 - TWO -PHASE LOCKING
PROTOCOL

28
 PROBLEM IN LOCKING METHOD

• Resulting transaction schedule might not be

serializable
• Schedule might create deadlocks.
• In a database, a deadlock is a situation in which two
or more transactions are waiting for one another to
give up locks.
TABLE 10.13 - HOW A DEADLOCK
CONDITION IS CREATED

30
 EXAMPLE OF DEADLOCKS

• For example, Transaction A might hold a lock on some rows in

the Accounts table and needs to update some rows in the Orders table
to finish. Transaction B holds locks on those very rows in
the Orders table but needs to update the rows in the Accounts table
held by Transaction A. Transaction A cannot complete its transaction
because of the lock on Orders. Transaction B cannot complete its
transaction because of the lock on Accounts. All activity comes to a
halt and remains at a standstill forever unless the DBMS detects the
deadlock and aborts one of the transactions.
 3 BASIC TECHNIQUES TO
CONTROL DEADLOCKS

• Deadlock Prevention
•  A transaction requesting a new lock is aborted if there is a
possibility that a deadlock may occur. If the transaction is aborted,
all the changes made by this transaction are rolled back and all locks
are released. The transaction is then re-scheduled for execution.
Deadlock prevention works because it avoids the conditions that
lead to deadlocking.
•  Deadlock Detection
•  The DBMS periodically tests the database for deadlocks. If a
deadlock is found, one of the transactions (the "victim") is aborted
(rolled back and rescheduled) and the other transaction continues.
• Deadlock Avoidance
• The transaction must obtain all the locks it needs before it can be
executed. This technique avoids rollback of conflicting transactions
by requiring that locks be obtained in succession. However, the
serial lock assignment required in deadlock avoidance increases the
 TIME-STAMP BASED PROTOCOL

• This protocol is used very commonly. This protocol uses either

system time or logical counter as a timestamp.
• To ensure serializability, transaction associate with the time
called as time stamp. In simple words transaction is order
based on the time of arrival and there is no deadlock.
• Every transaction has a timestamp associated with it, and the
ordering is determined by the age of the transaction.
• A transaction created at 0002 clock time would be older than
all other transactions that come after it.
• For example, any transaction 'y' entering the system at 0004 is
two seconds younger and the priority would be given to the
older one.
• In addition, every data item is given the latest read and write-
timestamp. This lets the system know when the last ‘read and
write’ operation was performed on the data item.
 TIME STAMPING

• Disadvantages
• Each value stored in the database
requires two additional stamp fields
• Increases memory needs
• Increases the database’s
processing overhead
• Demands a lot of system resources
34
 TIMESTAMP ORDERING
PROTOCOL

• The timestamp-ordering protocol ensures

serializability among transactions in their
conflicting read and write operations.
• This is the responsibility of the protocol system
that the conflicting pair of tasks should be
executed according to the timestamp values
of the transactions.
- The timestamp of transaction Ti is denoted as TS(Ti).
- Read time-stamp of data-item X is denoted by
R-timestamp(X).
- Write time-stamp of data-item X is denoted by
W-timestamp(X)
THE END.