AIR UNIVERSITY

MULTAN CAMPUS
/

DISTRIBUTED DATABASE

Submitted to: Sir Irtaza Sheikh

Submitted by: -
Suheer Tahir – 153158
Junaid Saeed – 153172
Junaid Iqbal – 153179
Ahsan Javed – 153196
Semester: BS(CS)-VII
Topic: Transaction Management in DDB

Department of Computer Science

Air University Multan Campus
TRANSACTION MANAGEMENT IN DISTRIBUTED DATABASE 1

Transactions
A transaction is a single logical unit of work which accesses and possibly
modifies the contents of a database. Transactions access data using read and
write operations.
A transaction is a collection of actions that make consistent transformations of
system states while preserving system consistency.

Example: Consider the following SQL query for increasing by 10% the budget of
the CAD/CAM project.
UPDATE PROJ
SET BUDGET = BUDGET*1.1
WHERE PNAME = "CAD/CAM"

This query can be specified, using the embedded SQL notation, as a transaction
by giving it a name (e.g., BUDGET UPDATE) and declaring it as follows:
Begin transaction BUDGET_UPDATE
begin
EXEC SQL UPDATE PROJ
SET BUDGET = BUDGET*1.1
WHERE PNAME = "CAD/CAM"
end.

Example: Let us also assume that the relation definitions are as follows (where
the underlined attributes constitute the keys):
TRANSACTION MANAGEMENT IN DISTRIBUTED DATABASE 2

▪ FLIGHT(FNO, DATE, SRC, DEST, STSOLD, CAP)

▪ CUST(CNAME, ADDR, BAL)
▪ FC(FNO, DATE, CNAME, SPECIAL)
Let us consider a simplified version of a typical reservation application, where a
travel agent enters the flight number, the date, and a customer name, and asks
for a reservation.
Begin transaction Reservation
begin
input(flight no, date, customer name);
EXEC SQL SELECT STSOLD,CAP
INTO temp1,temp2
FROM FLIGHT
WHERE FNO = flight no AND DATE = date;
if temp1 = temp2 then
begin
output(“no free seats”);
Abort
end
else begin
EXEC SQL UPDATE FLIGHT
SET STSOLD = STSOLD + 1
WHERE FNO = flight no AND DATE = date;
EXEC SQL INSERT INTO FC(FNO,DATE,CNAME,SPECIAL)
VALUES (flight no, date, customer name, null);
Commit;
output(“reservation completed”)
end
end-if
end.

Characterization of Transactions
▪ 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 this
transaction
▪ base set (BS) − The union of the read set and write set of a transaction
constitutes its base set (BS = RS ∪ WS).
TRANSACTION MANAGEMENT IN DISTRIBUTED DATABASE 3

Transaction Operations
The low-level operations performed in a transaction are:
▪ Read − Read the data value from the database.
▪ Write − Read the data value from the database.
▪ Commit − A signal to specify that the transaction has been successfully
completed in its entirety and will not be undone.
▪ Abort− A signal to specify that the transaction has been unsuccessful
terminated.
Formalization of the Transaction
To define the transaction concept formally, following conventions are used:

▪ Oij(x) be some operation Oj of transaction Ti operating on entity x, where

Oj  {read,write} and Oj is atomic
▪ Operation set, OSi = j Oij
▪ Termination condition, Ni  {abort,commit}

With this terminology we can define a transaction Ti as a partial ordering over

its operations and the termination condition. A partial order P = {Σ, ≺} where Σ
consists of the operations and termination condition of a transaction, whereas
≺ indicates the execution order of these operations.

A transaction Ti is a partial order Ti = {Σi ,≺i}, where

1. Σi = OSi ∪ {Ni}.
2. For any two operations Oij,Oik ∈ OSi , if Oi j = {R(x)or W(x)} and Oik =
W(x) for any data item x, then either Oi ≺i Oik or Oik ≺i Oij.
3. ∀Oij ∈ OSi,Oij ≺i Ni .

Example: Consider a simple transaction T that consists of the following steps:

Read(x)
Read(y)
x ← x+y
Write(x)
Commit
TRANSACTION MANAGEMENT IN DISTRIBUTED DATABASE 4

The specification of this transaction according to the formal notation that we

have introduced is as follows:

Σ = {R(x),R(y),W(x),C}

≺ = {(R(x),W(x)),(R(y),W(x)),(W(x),C),(R(x),C),(R(y),C)}

where (Oi ,Oj) as an element of the ≺ relation indicates that Oi ≺ Oj .

DAG representation of the transaction,

Transaction States
A transaction may go through a subset of five states, active, partially
committed, committed, failed and aborted.
▪ Active − The initial state where the transaction enters is the active state.
The transaction remains in this state while it is executing read, write or
other operations.
▪ Partially Committed − The transaction enters this state after the last
statement of the transaction has been executed.
▪ Committed − The transaction enters this state after successful
completion of the transaction and system checks have issued commit
signal.
▪ Failed − The transaction goes from partially committed state or active
state to failed state when it is discovered that normal execution can no
longer proceed or system checks fail.
▪ Aborted − This is the state after the transaction has been rolled back
after failure and the database has been restored to its state that was
before the transaction began.
The following state transition diagram depicts the states in the transaction and
the low level transaction operations that causes change in states.
TRANSACTION MANAGEMENT IN DISTRIBUTED DATABASE 5

Properties of Transactions
1. Atomicity
Atomicity refers to the fact that a transaction is treated as a unit of
operation. Therefore, either all the transaction’s actions are completed,
or none of them are. This is also known as the “all-or-nothing property.”
2. Consistency
The consistency of a transaction is simply its correctness. In other words,
a transaction is a correct program that maps one consistent database
state to another.
The four consistency levels are defined as follows:
Degree 0: Transaction T sees degree 0 consistency if:
- T does not overwrite dirty data of other transactions.
Degree 1: Transaction T sees degree 1 consistency if:
- T does not overwrite dirty data of other transactions.
- T does not commit any writes before EOT.
Degree 2: Transaction T sees degree 2 consistency if:
- T does not overwrite dirty data of other transactions.
- T does not commit any writes before EOT.
- T does not read dirty data from other transactions.
Degree 3: Transaction T sees degree 3 consistency if:
- T does not overwrite dirty data of other transactions.
- T does not commit any writes before EOT.
- T does not read dirty data from other transactions.
- Other transactions do not dirty any data read by T before T completes.
TRANSACTION MANAGEMENT IN DISTRIBUTED DATABASE 6

3. Isolation
Isolation is the property of transactions that requires each transaction to
see a consistent database at all times. In other words, an executing
transaction cannot reveal its results to other concurrent transactions
before its commitment.
If two concurrent transactions access a data item that is being updated
by one of them, it is not possible to guarantee that the second will read
the correct value.
- Dirty read
T1 modifies x which is then read by T2 before T1 terminates; T1 aborts
⇒ T2 has read value which never exists in the database.
- Non-repeatable (fuzzy) read
T1 reads x; T2 then modifies or deletes x and commits. T1 tries to read x
again but reads a different value or can’t find it.
- Phantom
T1 searches the database according to a predicate while T2 inserts new
tuples that satisfy the predicate.
4. Durability
Durability refers to that property of transactions which ensures that
once a transaction commits, its results are permanent and cannot be
erased from the database.

Transaction Structure
There are two transaction structure as follows:
1. Flat transaction
A flat consists of a sequence of primitive operations embraced between
a begin and end markers.
Begin_transaction Reservation
…
end.
2. Nested transaction
The operations of a transaction may themselves be transactions.

Begin_transaction Reservation
…
Begin_transaction Airline
TRANSACTION MANAGEMENT IN DISTRIBUTED DATABASE 7

…
end. {Airline}
Begin_transaction Hotel
…
end. {Hotel}
end. {Reservation}

Workflows
A collection of tasks organized to accomplish some business process.
Example: Let us further extend the reservation transaction example. The entire
reservation activity consists of the following tasks and involves the following
data:
▪ 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.
Figure depicts this workflow where 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.
TRANSACTION MANAGEMENT IN DISTRIBUTED DATABASE 8

Distributed Transactions
A distributed transaction includes one or more statements that, individually or
as a group, update data on two or more distinct nodes of a distributed
database.
Example: Assume the database configuration depicted in following figure.

The following distributed transaction executed by SCOTT updates the local

SALES database, the remote HQ database, and the remote MAINT database:
Begin transaction BUDGET UPDATE
begin
EXEC SQL UPDATE [email protected]
SET loc = 'REDWOOD SHORES'
WHERE deptno = 10;
EXEC SQL UPDATE scott.emp
SET deptno = 11
WHERE deptno = 10;
EXEC SQL UPDATE [email protected]
SET room = 1225
WHERE room = 1163;
COMMIT;
end.