Chapter 10

Transaction and Transaction Mgt

 Transaction
In Database Transaction is a logical unit of work
It is a basic unit of consistent and reliable computing
A transaction is considered to be a sequence of
read or/and write operations; it may even consist of
a single statement.
Consistent transaction:
Transaction consistency refers to the actions of
concurrent transactions.
 Transaction
On single copy of database no problem
A distributed and replicated environment
generate complication.
A replicated database is in a mutually consistent
state if all the copies of every data item in it have
identical values.
Transaction Model
Consistent May be Consistent
State of DB Temporarily State
Inconsistent

Begin Execution of End of

Transaction T Transaction T Transaction T
Transaction Example T-SQL
Transaction BUDGET_UPDATE
begin
EXEC SQL UPDATE PROJ
SET BUDGET = BUDGET * 1.1
WHERE JNAME = “CAD/CAM"
end
Transaction Management is difficult in case of the
concurrent access to the database by multiple users.
Multiple read-only transactions cause no problem
at all, however, if one or more of concurrent
transactions try to update data, that may cause
problem.
A transaction is considered to be a sequence of
read or/and write operations; it may even consist of
a single statement.
Example Database
Airline Reservation System
– FLIGHT(fNo, fDate, fSrc, fDest,
stSold, fCap)
– CUST(cName, cAddr, cBal)
– FC(fNo, fDate, cName, cSpecial)
Begin transaction Reservation
begin
input(flight no, date, customer name); (1)
EXEC SQL UPDATE FLIGHT (2)
SET STSOLD = STSOLD + 1
WHERE FNO = flight no
AND DATE = date;
EXEC SQL INSERT (3)
INTO FC(FNO,DATE,CNAME,SPECIAL)
VALUES (flight no,date,customer name, null);
output(“reservation completed”) (4)
end
 Transaction Example
Line (1): is to input the flight number, the date, and the
customer name.
Line (2): updates the number of sold seats on the
requested flight by one.
Line (3): inserts a tuple into the FC relation. With the
assumption that customer is an old customer.
Null shows the customer has no special request.
line (4): reports the result of the transaction to the
agent’s terminal.
 Transactions’ Termination Condition
The reservation transaction of Example has an implicit
assumption about its termination.
It assumes that there will always be a free seat and does
not take into consideration the fact that the transaction
may fail due to lack of seats.
This is unrealistic assumption
Two possible states for a transaction termination
Commit: on successful completion
Abort: stops without completing its task
 Transactions’ Termination Condition
Commit has two effects:
First:
It signals the DBMS that effect of transaction should be
reflected in database
Thus other transactions could see the change
Second:
The point at which a transaction is committed is a “point of no
return.”
The results of the committed transaction are now permanent
and cannot be undone.
 Transactions’ Termination Condition
Abort:
When a transaction is aborted, its execution is stopped
All of its already executed actions are undone by
returning the database to the state before their execution.
This is also known as rollback.
A transaction aborts itself because of a condition that
would prevent it from completing its task successfully.
Transactions’ Termination Condition
Let us return to our
reservation system example.
One thing we did not consider
is that there may not be any
free seats available on the
desired flight.
Begin_transaction Reservation
input(flight_no, dt, c_name);
EXEC SQL Select stSold, CAP INTO temp1, temp2
WHERE fNo = flight_no and date = dt
if temp1 = temp2 then
output("no free seats"); Abort
else
EXEC SQL update flight
set stSold = stSold + 1 where
fNo = flight_no and date = dt;
EXEC SQL insert into FC values (flight_no,
dt, c_Name, null); Commit;
output("reservation completed")
end
 Characterization of transaction
Two basic operations in a transaction Read/Write
 Read set (RS): The set of data items that are read
by a transaction.
Write set (WS): The set of data items whose values
are changed by a transaction
RS and WS need not to be mutually exclusive.
Base set (BS) = RS U WS
 Characterization of transaction
In reservation example insert is a write operation
so:
RS[Reservation] = {FLIGHT.STSOLD,
FLIGHT.CAP}
WS[Reservation] = {FLIGHT.STSOLD, FC.FNO,
FC.DATE, FC.CNAME, FC.SPECIAL}
BS[Reservation] = {FLIGHT.STSOLD, FLIGHT.CAP,
FC.FNO, FC.DATE, FC.CNAME, FC.SPECIAL}
Above Characterizations are simple
since they do not consider insert
and delete, so concern more a static
DB
Means a DB that doesn’t grow
Dynamic Trs have to deal with
Phantoms.
Characterization of transaction
Consider that transaction T1, during its
execution, searches the FC table for the names
of customers who have ordered a special meal.
It gets a set of CNAME for customers who
satisfy the search criteria.
While T1 is executing, transaction T2 inserts
new tuples into FC with the special meal
request, and commits.
Characterization of transaction
If T1 were to re-issue the same search
query later in its execution, it will get
back a set of CNAME that is different
than the original set it had retrieved.
Thus, “phantom” tuples have
appeared in the database.
Let Oij(x) be some operation Oj of
transaction Ti operating on data item
x, where Oj ∈{read,write} and Oj is
atomic
Set of operations of Transaction Ti, OSi
= Uj Oij.
Ni (Termination condition) ∈{abort,commit}
Consider a transaction T
Read(x)
Read(y)
x=x+y
Write(x)
Commit
Then
 Notation normally used is
T = {R(x), R(y), W(x), C}
(Operations a & Termination Condition)
< = {(R(x),W(x)), (R(y),W(x)),
(W(x),C), (R(x),C), (R(y),C)}
(Execution Order)
ACID Properties of a
Transaction
1- Atomicity: also known as “all or
none” property
–refers to the atomicity of entire Tr
rather than an individual operation
–It requires from system to define some
action in case of any interruption in
execution of Tr.
–Two types of failures
requiring procedures
from
–Transaction Recovery
or
–Crash Recovery
 –Transaction Recovery:
Failure due to input data errors,
deadlocks, or other factors.
Then the transaction aborts itself
Or DBMS aborts it while handling the
deadlock
Crash Recovery
Failure is caused by system crashes, such as
media failures, processor failures,
communication link breakages, power outages
An important difference between the two types of
failures is that during some types of system crashes,
the information in volatile storage may be lost or
inaccessible.
ACID
2- Consistency: refers simply to
the correctness of a transaction
–A Transaction should transform the
DB from one consistent state to
another consistent state.
–Concern of Semantic Integrity
Control and Concurrency Control
–Classification of consistency for
Transaction uses the term “Dirty
Data”; data that has been updated by
a Transaction before its
commitment.
Degree 3 Consistency
1- T does not overwrite dirty data of other
Transaction
2- T does not commit any writes until it
completes all its writes (i.e., until end of
Transaction)
3- T does not read dirty data from other
Transaction
4- Other Transaction do not dirty any data
read by T before T commits
Degree 2 Consistency
Transaction T sees degree 2 consistency if:
1. T does not overwrite dirty data of other
transactions.
2. T does not commit any writes before EOT.
3. T does not read dirty data from other
transactions.
Degree 1 Consistency
Degree 1: Transaction T sees degree 1
consistency if:
1. T does not overwrite dirty data of
other transactions.
2. T does not commit any writes before
EOT.
 –Consistency:
It is true that a higher degree of consistency
encompasses all the lower degrees.
Multiple levels allows programmers to to
define transactions that operate at different
levels.
while some transactions operate at Degree 3
consistency level, others may operate at lower
levels and may see, for example, dirty data.
3- Isolation
- A Transaction cannot reveal its
results to other Transaction
before commitment
- Required in particular when one
of the Transaction is updating a
common data item
- Consider two Trs T1 and T2
T1: Read(X) T2: Read(x)
x = x+1 x = x+1
Write(X) Write(X)
Commit Commit
- Two possible serial executions are T1,
T2 or T2, T1
- First Tr gets 50 and makes it 51, other
makes it 52
- In any case it will be 52 at the end of
both Trs
An interleaved execution
may result “Lost Update”
Like
(T1:Read(x), T1:x =x+1,
T2:Read(x), T1:Write(x),
T2:x=x+1, T2:write(x),
T1:commit, T2:commit)
Isolation and consistency
are interrelated, one
supports other
Degree 3 provides full
isolation
SQL-92 identified isolation
levels based on following
phenomena.
Dirty Read: A transaction reads
the written value of another
transaction before its
commitment, like,
--, W1(x), ----, R2(x), --- ,C1(or
A1)-----, C2(or A2).
Non-repeatable or Fuzzy Read:
Two reads of same data item by
same Tr and a write by another
Tr on the same data item
--, R1(x), ----, W2(x), --- ,C2-----,
R1(x)----
Phantom: T1 performs a
read on a predicate, T2
inserts tuples that satisfy
the predicate
--, R1(P), ----, W2(yinP),
--- ,C2(orA2)-----, C1(orA1)---
Isolation levels
Read Uncommitted: all three
phenomena possible
Read Committed: fuzzy read, phantoms
possible; DR not possible
Repeatable Read: Only phantoms
possible
Anomaly Serializable: None of the
phenomena possible
4- Durability:
Once committed, changes to
DB are permanent.
Types of Transactions
 Types of Transaction
Various Criteria for classification of
transaction
Duration of Transaction
Organization of read and write actions
According to Transaction structure
 Types of Transaction
Duration of Transaction
Online Transactions ( Short Life)
These transactions have very short execution/ response
times (typically, on the order of a couple of seconds)
And access a relatively small portion of the database.
This class of transactions used by large majority of current
transaction applications.
Examples: banking transactions and airline reservation
transactions.
Types of Transaction
Duration of Transaction
Batch Transactions (Long Life)
Take longer to execute (response time
being measured in minutes, hours, or
even days)
Access a larger portion of the database.
 Types of Transaction
Duration of Transaction
Batch Transactions (Long Life)
 Typical applications that might require batch transactions are
 design databases,
 statistical applications,
 report generation,
 complex queries, and
 image processing.
Conversational transaction, which is executed by interacting with the
user issuing it.
 Types of Transaction
Organization of read and write actions
 General (if read and write transactions are
intermixed no specific ordering)
 Two-step (all reads before any write)
 Restricted (Read an item before write)
 If a transaction is both twostep and restricted, it is called
a restricted two-step transaction.
 Action model (each Read/write on an item to be
atomic.)
 Types of Transaction
General:
T1 : {R(x),R(y),W(y),R(z),W(x),W(z),W(w),C}
Two-step:
T2 : {R(x),R(y),R(z),W(x),W(z),W(y),W(w),C}
Restricted:
T3 : {R(x),R(y),W(y),R(z),W(x),W(z),R(w),W(w),C}
Note that T3 has to read w before writing
 Types of Transaction
Two-step restricted:
T4 : {R(x),R(y),R(z),R(w),W(x),W(z),W(y),W(w),C}
Action:
T5 : {[R(x),W(x)], [R(y),W(y)], [R(z),W(z)],
[R(w),W(w)],C}
Note that each pair of actions within square brackets
is executed atomically
Types of Transaction
 Types of Transaction
Classification on the basis of Structure of
Transaction
Flat transactions
Closed nested transactions
Open nested transactions
Workflow models which, in some cases, are
combinations of various nested forms.
Flat transaction
Flat transactions have a single start point (Begin
transaction) and a single termination point (End
transaction).
Consists of a sequence of primitive operations
embraced between a begin and end markers.
Begin_transaction Reservation
…
end.
Most of the transaction management work in
databases has concentrated on flat transactions.
Nested transaction
Transaction enclosed inside another transaction
a transaction to include other transactions with
their own begin and commit points.
Begin_transaction Reservation
…
Begin_transaction Airline
…
end {Airline}
end {Reservation}
 Nested Transactions
Have the same properties as their
parents;may themselves have
other nested transactions.
No restriction of level of nesting
Introduces concurrency control
and recovery concepts within the
transaction.
 Types of Transaction…. Structure
Closed nesting
Sub transactions begin after their parents and
finish/comit before them
This generality is necessary to support application areas
where transactions are more complex than in traditional
data processing.
Commitment of a sub transaction is conditional upon
the commitment of the parent (commitment through
the root)
These transactions enforce atomicity at the top-most
level.
 Types of Transaction…. Structure
Open nesting
Sub transactions can execute and commit
independently.
Open nesting relaxes the top-level atomicity
restriction of closed nested transactions.
an open nested transaction allows its partial
results to be observed outside the transaction.
Sagas and split transactions are examples of open
nesting.
 Types of Transaction…. Structure
Open nesting
A saga is a “sequence of transactions that can be
interleaved with other transactions”
The DBMS guarantees that either all the transactions
in a saga are successfully completed or compensating
transactions are run to recover from a partial
execution.
A compensating transaction effectively does the
inverse of the transaction that it is associated with.
 Types of Transaction…. Structure
For example, if the transaction adds $100 to a
bank account, its compensating transaction
deducts $100 from the same bank account.
If a transaction is viewed as a function that
maps the old database state to a new database
state, its compensating transaction is the
inverse of that function.
 Types of Transaction…. Structure
Two properties of sagas are:
(1) only two levels of nesting are allowed, and
(2) at the outer level, the system does not support full atomicity.
A saga differs from a closed nested transaction in that its level
structure is more restricted (only 2) and
That it is open (the partial results of component transactions
or sub-sagas are visible to the outside).
Furthermore, the transactions that make up a saga have to be
executed sequentially.
 Advantages of Nested Transaction
They provide a higher-level of
concurrency among transactions.
More concurrency is possible
within a single transaction.
Example 10.10 is implemented as a
flat transaction, it may not be
possible to access records about a
specific flight concurrently.
 Advantages of Nested Transaction
 In other words If one travel agent issues the
reservation transaction for a given flight, any
concurrent transaction that wishes to access
the same flight data will have to wait until
the termination of the first, which includes
the hotel and car reservation activities in
addition to flight reservation.
However, a nested implementation will
permit the second transaction to access the
flight data as soon as the Airline
subtransaction of the first reservation
transaction is completed.
 Advantages of Nested Transaction
Secondly it is possible to recover independently from failures
of each subtransaction.
This limits the damage to a smaller part of the transaction,
making it less costly to recover.
In a flat transaction, if any operation fails, the entire
transaction has to be aborted and restarted, whereas in a
nested transaction, if an operation fails, only the
subtransaction containing that operation needs to be
aborted and restarted.
 Advantages of Nested Transaction
Finally, it is possible to create new
transactions from existing ones simply by
inserting the old one inside the new one as a
subtransaction.
Workflows
Workflows
Flat transaction model
suits relatively small and
simple environments
Certain environments
need combination of
open and nested models.
 Workflows
A candidate definition “ a collection of
tasks organized to accomplish some
business activity”
A workflow is also defined as an activity
consisting of a set of tasks with well-
defined precedence relationship among
them.
 Workflows
Different types of workflows.
Human-oriented workflows:
 Which involve humans in performing the
tasks.
System support is provided to facilitate
collaboration and coordination among humans
But humans themselves are ultimately
responsible for the consistency of the actions.
 Workflows
System-oriented workflows:
 are those that consist of computation-
intensive and specialized tasks that can be
executed by a computer.
The system support in this case is substantial
and involves concurrency control and recovery,
automatic task execution, notification, etc.
 Workflows
Transactional workflows:
Range in between human-oriented and system oriented
workflows and borrow characteristics from both
They involve “coordinated execution of multiple tasks that
(a) may involve humans,
(b) require access to HAD [heterogeneous, autonomous,
and/or distributed] systems, and
(c) support selective use of transactional properties [i.e.,
ACID properties] for individual tasks or entire
workflows.”
Workflows
Workflows generally involve
long transactions, like a
reservation transaction that
may include Airline, Hotel,
Auto reservations and bill
generation.
 Workflows
Let us further extend the reservation
transaction of Example 10.3. The entire
reservation activity consists of the
following tasks and involves the
following data:
 Workflows
Customer request is obtained (task T1) and Customer Database
is accessed to obtain customer information, preferences, etc.;
 Airline reservation is performed (T2) by accessing the Flight
Database;
 Hotel reservation is performed (T3), which may involve sending
a message to the hotel involved;
 Auto reservation is performed (T4), which may also involve
communication with the car rental company;
 Bill is generated (T5) and the billing info is recorded in the
billing database.
Workflows
Workflows
There is a serial dependency of T2 on T1,
and T3, T4 on T2; however, T3 and T4
(hotel and car reservations) are
performed in parallel and T5 waits until
their completion.
 Workflows
Workflows is modeled as activity with open
nesting semantics i.e., a partial results to be visible
outside the activity boundary.
Means permits access to the results of sub-activity
before the commitment of the major activity.
Tasks that make up the activity are allowed to
commit individually.
But in case of abort when an activity aborts, all of
its components should be aborted.
 Workflows
Therefore, compensating transactions
are defined for the components of an
activity.
Thus, if a component has already
committed when an activity aborts, the
corresponding compensating transaction
is executed to “undo” its effects.
 Workflows
 Some components of an activity are
declared as vital, main activity aborts if a
vital component aborts, otherwise it may
commit even if a non-vital component
aborts.
The workflow always aborts when one of
its component aborts. For example:
 Workflows
 T2 (airline reservation) and T3 (hotel
reservation) may be declared as vital so that if
an airline reservation or a hotel reservation
cannot be made, the workflow aborts and the
entire trip is canceled.
However, if a car reservation cannot be
committed, the workflow can still successfully
terminate.
 Workflows
It is possible to define contingency tasks that
are invoked if their counterparts fail.
For example:
if the hotel reservation component for Hilton
fails, the Sheraton alternative is tried rather
than aborting the task and the entire
workflow.
 Architecture Revisited
Distributed execution monitor
consists of two modules:
a transaction manager (TM)
a scheduler (SC)
 Architecture Revisited
The distributed execution monitor consists of two
modules:
The Transaction Manager (TM) is responsible for
coordinating the execution of the database
operations on behalf of an application.
The Scheduler (SC) is responsible for the
implementation of a specific concurrency control
algorithm for synchronizing access to the database.
 Architecture Revisited
A third component that participates in the
management of distributed transactions is the
local recovery managers (LRM) that exist at each
site.
Their function is to implement the local
procedures by which the local database can be
recovered to a consistent state following a
failure.
 Architecture Revisited
Each transaction originates at one site,
which we will call its originating site.
The execution of the database
operations of a transaction is
coordinated by the TM at that
transaction’s originating site.
 Architecture Revisited
The TMs implement an interface for the
application programs which consists of
five commands: begin transaction, read,
write, commit, and abort.
 Architecture Revisited
1. Begin transaction. This is an indicator to
the TM that a new transaction is starting.
The TM does some bookkeeping, such as
recording the transaction’s name, the
originating application, and so on, in
coordination with the data processor.
 Architecture Revisited
2. Read. If the data item to be read is stored
locally, its value is read and returned to the
transaction.
Otherwise, the TM finds where the data item
is stored and requests its value to be returned
(after appropriate concurrency control
measures are taken).
 Architecture Revisited
3. Write. If the data item is stored locally, its
value is updated (in coordination with the
data processor).
Otherwise, the TM finds where the data item
is located and requests the update to be
carried out at that site )after appropriate
concurrency control measures are taken).
 Architecture Revisited
4. Commit. The TM coordinates the sites involved
in updating data items on behalf of this
transaction so that the updates are made
permanent at every site.
5. Abort. The TM makes sure that no effects of the
transaction are reflected in any of the databases
at the sites where it updated data items.
 Architecture Revisited
Beside these services a TM can
communicate with SCs and data
processors at the same or at different
sites.
Architecture revisited
Thanks