1.
START
thc scLt of transactio~~s that have started. but not yet completed
va!idation. For each transaction
T
in this set. the scheduler maintains
ST.4R-1
(T).
the tilnc at which
T
started.
2.
K4L;
the set of transactions that have been validated hut not yet finished
tlie n-riting of phase
3.
For each transaction
T
in this set, the scheduler
niairitains both
srr \nr(T)
and
\:-\L(T),
the time at which
T
valiciated.
Sote that
\~L(T)
is also
thc
time at which

T
is irnagined to execute ill
the hypotlirtical serial order of esccutioi~.
3.
FIIV:
the set of trai~sactio~is that have corripletcd phase
3.
For thesc
tra~isactions
T,
the scheduler records
START(T), \' \I.(T),
and
FIS(T):
the
time at which
T
finished. In principle this set grows, but as a-e shall see.
n-e do not
havc to remember transaction
T
if
~ln(T)
<
ST.~KT(C-)
for
any
actir~c transaction
U
(i.e for any

U
in
START
or
VAL).
The
scheduler
may
thus periodically purge the
FIN
set to keep its size from growing
beyond bounds.
18.9.2
The
Validation Rules
If rnaintaincd by the
scheduler.
the information of Section
18.9.1
is cnotigh for
it to detect any potential violation of the
assulned serial order
of
the transac-
tions
-
the order in which the trai~sactions validate. To understand tlie rules.
Irt
us
first consider what can be I\-long ~vhe~i w\-r try to validate a transaction

T.
T
reads
X
/
U
writes
X
U
stalt
T
start
U
validated
T
validating
Figure
18.43:
T
cannot ~alidate if an earlier transaction is nolv ~viiting some-
thing
tlrat
T
slioulci have rcati
1.
Supposcx tlir~rc,
is
;I
transaction
L7

sur.11 t11;it:
(a)
C
is in
1/;-lL
or
FLV:
that is.
C-
has vnlid;~tcd.
(b)
FIS(C)
>
s'I-~\RT(T):
that is,
C
tiid
not
finish beforc
T
started.'"
-
-
'"ore
tlrat
if
1:
is
in
VAL.

then
C
has
not
yet
firris11c.d when
?.
validates.
In
that
case.
FIX((.')
is
trclirricall?. l~ndefined.
Holvever.
we lirlon. it
mrlst
he largpr
than
ST;\KT(T)
in
this
case.
(c)
RS(T)
n
nls(U)
is not empty; in particular, let it contain database
elenlent
S.

Then it is possible that
U
wrote
S
after
T
read
S.
In fact.
I/'
may not
even have written
A'
yet.
-2
situatiorl where
LT
wrote
X,
but not in time
is shown in
Fig,
18.43.
To interpret the figure. note that the dotted lines
connrct the eyents in real time ~vith the time at which they xvould have
occurred had transactions bee11 executed at the molnent they validated.
Since n.e don't kno~v n-hether or not
T
got to read li's value, \ve must
rollback

T
to avoid a risk that the actions of
T
and
U
will not be consistent
~vitli the assumed serial order
2.
Suppose there is a transaction
U
such that:
(a)
U
is in
VAL:
i.e.,
U
has successfully validated.
(h)
FIS(U)
>
\:-\L(T);
that is,
U
did not finish before
T
entered its
validation
phase.
(c)

\vs(T)
n
\\.s(U)
#
0:
in particular. let
S
be in both \\-rite sets.
Thcn the potential probleni is as sho~vn ill Fig.
18.44.
T
and
li
must both
\\rite values of
S,
and if \vc let
T
validate. it is possible that it will wiite
S
before
I-
does. Since \ve cannot be sure.
ne
rollback
T
to make sure it
does not violate the assumed serial order in which it follo~s
C'.
T

writes
X
I
U
writes
X
D.
validated
T
validating
U
finish
Figure
18.41:
T
cannot validate if it co~ild tl~en mite something ahead of an
earlier transaction
Tile
two
descrillpd above are the only situations in I\-hich
a
write
T
could
I,e pl~~sically ullrcalizablt.
In
Fig.
15.43.
if
C

finished before
7'
starred. tlle~l sure]!.
T
lv0~~ltl
read
tlic va111c of
S
that either
c-
or sollle later
trallsaction n.roce.
In
Fig.
18.44.
if
C.
finished hefore
T
validated. then
surely
C'
lvrote
.y
before
T
did. \Ye may tli~ls sunllnarize these observations with the
follon-ing rule for validating a transaction
T:
Check that

RS(T)
n
\\.s(U)
=
0
for any previously validated
C'
that did
not finish before
T
startcd, i.e if
FIS(~)
>
START(T).
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
982
CHAPTER
18.
COAiCURRENCY C'OXTROL
Check that
wS(T)
n
WS(U)
=
0
for any previously validated
U
that did
not finish before
T

validated, i.e., if
FIS(U)
>
v.%L(T).
Example
18.29
:
Figure 18.45 shows a time line during which four transactiorls
T,
U,
V,
and
IV
attempt to execute and validate. The read and write sets for
each transaction are indicated on the diagram.
T
starts first, although
U
is the
first to validate.
Figure 18.45: Four
transactiorls and their validation
1.
\'alidation of
U:
When
U
validates there are no other validated transac-
tions, so there is nothing to check.
U

validates successfully and writes a
value for database
element
D.
2.
\lidation of
T:
When
T
validates,
LT
is validated but not finished. Thus.
lve must check that neither the read nor write set of
T
has anything
in common with
WS(U)
=
{D).
Since
RS(T)
=
{.4.
B).
and
m(T)
=
{ I,
C),
both checks are successfiil. and

T
validates.
3.
\%lidation of
IT:
\lilien
17
validates.
li
is validated and finished. and
T
is validated but not finishtd
Also.
I'
started hefore
C
finished
711~5.
ne n~ust compare bath
RS(I')
and
n
~(13
against
ws(T)
Lilt onlv
RS(I
.)
nerds to be compared against
\\.s(l*).

\\e find:
RS(~-)
n
us(T)
=
{B)
n
{-4.C)
=
0.
.
ns(17)
n
~zs(T)
=
{D,
E)
n
{-4.C)
=
0
.
RS(~*)
n
~(u)
=
{B)
n
{D)
=

0.
Thus,
I
-
also validates
successfully.
18.9.
C04CL7RRE~1'CY CONTROL BY VALID-4T10hT
983
I
Just
a
Moment
I
lrou may have been concelned xvith a tacit notion that validation takes
place in a moment, or indivisible instant of time. For example,
we i~nagine
that vie can decide whether a transaction
U
has already validated before
we
start to validate transaction
T.
Could
U
perhaps finish validating while
n-e are xalidating
T?
If we are running on a uniprocessor system, and there is only one
scheduler process,

we can indeed think of validation and other actions of
the scheduler as taking place in an instant of time. The reason is that if
the scheduler is validating
T,
then it cannot also be validating
U,
so all
during
the validation of
T,
the validation status of
U
cannot change.
If
I\-e are running on a multiprocessor, and there are several sched-
uler
processes, then it might be that one is validating
T
while the other
is
validating
U.
If so, then we need to rely on whatever synchroniza-
tion mechanism the
~nultiprocessor system provides to make validation an
atomic
action.
4.
Iralidation of
15':

\'i;hen
\IT
validates,
~\-e
find that
U
finished bcfore
Ili
started. so no co~nparison betwen
IV
and
U
is performed.
T
is finished
before
11.
validates but did not finish before
Ti7
started, so [ve compare
onl\-
RS(TV)
with
\j's(T).
I.
is validated but not finished. so xe need to
cornpale both
~s(T1')
arid
I\

~(11~)
with
ws(T).
These tests are:
~s(rl/)
n
ws(~)
=
{A4. D)
n
{ l;C)
=
{.A).
~s(rv)
n
ws(l')
=
{.4.
D)
n
{D.
E}
=
{Dl.
\vs(11-)
n
ws(17)
=
{ I.
C)

n
{D;
E)
=
0.
Since the i~ltersections are not all empty.
Ti7
IS
not validated. Rather,
TIT
is rolled back and does not write values for
I
or
C.
18.9.3
Comparison of Three Concurrency-Control
Mechanisms
Tile
tllrce approaches to serializabllity that n-e have collsidered

locks. times-
tamps.
and validation
-
each have their advantages. First. they can be
corn-
pared for their storage utilization:
Locks:
Space in the lock table is proportional to
the

number of database
elements locked.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Tzmestamps: In a naive implementation, space is needed for read- and
write-times with every database element,
nhether or not it is currently
accessed. However, a more careful
implenlentation \%-ill treat all times-
tamps that are prior to the earliest active transaction as "minus infinity.'
and not record them. In that case.
we can store read- and write-times in
a
table analogous to a lock table, in which only those database elements
that have been accessed recently are mentioned at all.
Validation: Space is used for timestamps and read/\vrite sets for each
currently active transaction, plus a
few more transactions that finished
after
some currently active transaction began.
Thus, the amounts of space used by each approach is approximately propor-
tional to the sum over all active transactions of the number of database
elenle~lts
the transaction accesses. Timesta~nping and validation may use slightly more
space because they keep track of certain accesses by recently
committed
trans-
actions that a
lock table ~vould not record.
X
poter~tial problem with validation

is that the
w~ite set for a transaction must be known before the xrites occur
(but after the transaction's local cornputation has
been conlpleteti).
It'e can also conipare the methods for their effect on
the
ability of transac-
tions to complete
tvithout delay. The performance of the three methotfs depends
on
whether interaction among transactions (the likelihood that a tra~lractioci
will access an elenlent that is also being accessed by a concurrent transaction)
is high or low.
Locking delays transactions but avoids rollbaclts. even ~vhen interactio~l
is high. Tiniestamps and validation do not delay transactions. but call
cause them to rollback, which is a niore serious form of delay and also
~~astes resources.
If interference is lo\v. then neither timestamps nor validation ~vill cause
many rollbacks. and may be preferable to locking because they generally
have lolver overhead than a locking scheduler.
\\-hen a rollback is necessary, tinlestamps catch some proble~ns earlier
than validation,
which altx-ays lets a transactioll do all its i~iter~lal n-ork
before considering whether the transaction niust rollback.
18.9.4
Exercises for
Section
18.9
Exercise
18.9.1

:
In the follo~vi~lg scquc.nccs of events. \\e
IISP
R,(.\-) to mcnn
"transaction
T,
starts, and its read set
IS
the list of databa~e elc~nents
S."
=\lqo.
I/,
lrieans
.'T,
attempts to talidate." and II;(.Y) lneans that
T,
finishes. and
its write set was
S."
Tell nhat happens n-lien each sequence is piocessect bj a
validation-based scheduler.
*
a)
R1(.4.B); Rr(B,C); 1;; R3(C.
D):
15: II;(.4):
I>:
TI:L(,4): 11;(B):
b) R1(-4.B): R2(B,C):
Vl;

Rs(C,D),
t:;
fT-1(~4); 15: 11'2(A4);
1i73(~):
C)
R1(.4.B); Rr(I3.C); 15; R3(C. D):
15;
II7l(c):
1:;
11'2(-+1): 1ir3(D);
d) R1(-4.B); R2(B.C): R3(C);
V1:
i5;
If3;
llTl(-4): Ilr2(B); fv3(c):
e) Rl( I.B); R2(B.C); R3(C);
1;:
1;:
V3; ll'-l(C):
11-z(B);
1i73(>4):
f) Rl(-4.B):
R2(B,
C); R3(C);
11:
1;: 1;;
Ll-1 (-4) I17z(C): 1$-3(B):
18.10
Summary
of

Chapter
18
+
Conszstent Database States: Database states that obey xhatever i~nplied
or declared constraints the designers inte~lded are called consistent. It
is essential that operations on the database preserve
consiste~lcy. that is.
they turn one consistent database state into
anothel.
+
Cons~stenc~ of Concurrent Transacttons: It is normal for several trans-
actions to have access to a database at the same time.
Trarisactions, run
111
isolation, are assumed to preserve consistency of the database. It is the
job of the scheduler to assure that concurrently operating transactions
also
preserxe the consistency of the database.
+
Schedrrles: Tra~lsactions are brokcn into actions, lnaillly reading and writ-
i~lg from the database.
X
sequcnce of these actions from one
or
more
tra~lsactiolls is called a schedule.
+
Serial Schedules:
If
trallsactio~ls esecutc ollf

ar
a time, the s~ht!du!C is
said to be serial.
+
Serializable Schedules:
i
schcdnle that is equivalent in its effect on the
database to sollle serial schedule is said to bc serializable. 111terlcat-i11g of
actions
from
transactions is I~ossible in a serializable schedule that
I
I
is not itself serial, but \ye 1llust
ver?- careful what sequences of actions
I
i
m-e allol3 or all interlea\-ing \vill Iea~e the database in an inconsistent
state.
+
Conflict-se~alirabi~ity:
-1
ii~nple-to-te~t. sufficient condition for serializ-
ability is that the schedule can be made serial by a sequellce of stvaps
of
adjacellt actiolls \vithout conflicts. Such a schedule is called conflict-
sPrialixa]lle.
;\
collflicr occurs if ~vc try to snap tn-o actions of the same
transaction. or to sXvap tXyo ac.tio~~s that acccss the same datalxsr elenlent.

at least one of ~vhich actions is ~vritc.
+
PVecedence Gmyhs:
.in
easy tcst for
cullflirt-serializal~ility
is
to
construct
a precedellce graph for the schedule. Sodes correspond to transactions.
and there is an arc
T
+
C
if some action of
T
in the schedule conflicts
n-itIl a later action of
c.
.\
schedule is conflict-serializable if and onl> if
the precedence graph is
ac\-clic.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CH IPTER 18. CONCURRESCY COSTROL
+
Locking:
The most common approach to assuring serializable schedules is
to lock database
elernents before accessillg them, and to release the lock

after finishing access to the element. Locks on an
eleluent prevent otlier
transactions from accessing the element.
+
TWO-Phase Lockzng:
Lorking by itself does not assure serializability. How-
ever,
two-phase locking, in which all transactions first enter a phase ~vhere
they only acquire locks, and then enter a phase diere they only release
locks. will guarantee serializability.
+
Lock Modes:
To a\-oitl locking out transactions unnecessarily, systems
usually use several lock
modes, with different rules for each lriode about
when a lock can be granted. Most common is the system with shared
locks for read-only access and esclusive locks for accesses that include
writing.
+
Compatzbzlzty Matrzces:
A
compatibility matrix is a useful summaiy of
.
xhen it is legal to grant a lock in a certain lock mode, given that there
may be other locks, in the
same or other rnocles, on the same elelnent.
+
Update Locks:
A
scheduler can allow a transactiori that plans to read and

then write an element first to take an update lock, and later to upgrade
the lock to esclusive. Update
locks call be granted
hen
there are already
shared locks on the
elcmerit: but once there, an update lock prevents vtlier
locks from being granted on tliat element.
+
Increment Loch:
For the common case where a transaction nanti only to
add or subtract a constant
from an element, an increment lock is suitable.
Increnlent locks on the sanie elelne~lt do not conflict n-it11 each other.
although they conflict
bit11 shared and esclusi~e locks.
+
Locking Elements Li'zth a GI-u~zularfty Hzerarchy:
\\-hell both large and
srnall elenients
-
relations, disk; blorks. and tuples, perhaps
-
may need
to be locked, a
~va~lling system of locks enforces serializability. Tra~lsac-
tions place intention locks on large elements to warn other transactions
that tliey plan to access one or more of its subelements.
+
Locking Elemen,ts .irmnged in a Tree:

If database elements are only ac-
cessed by moving
dolvn
a
tree. as in a 13-tree index, then a non-tn-o-phase
locking strategy call enforce serializability. The rules require a lock to 11e
held
on the parent n-llilt, obtaining
a
lock on tlic child. altliough the lock
on the parent c;111 then be rtlleasrd
anti
adtlitiorial locks taken latcr.
+
Optimistic Concurrency Control:
Instead of locking, a scheduler can as-
sume transactions
dl be scrializahle. and abort a transactiori if some
potentially
nonserializable behavior is seen. This approach, called opti-
mistic, is divided into timestamp-based, and validation-based scheduling.
REFERESCES FOR
CII.4PTER
18
+
Timestamp-Based
Schedulers:
Tliis type of scheduler assigns tirnesta~ilps
to transactio~ls as they begin. Database elements have associated read-
and write-times, \\.!lich are the tiniestanlps of the transactions that most

recently 1;erformed those actions. If an irnpossible situation, such as a
read
by
one transaction of a value that sas written in that transaction's
future is detected. the violating transaction is rolled back,
i.e., aborted
and restarted.
+
Val2dntfon-Based Schedrrlers:
These schedlilers validate transactions after
tliey haye read pverything they need, but before they write Trar~sactions
that have wad. or \v111 nritc, an elenient that some other transaction is in
the
ploccss of xvriting. nil1 have an ambiguous result, so the transaction
is not
val~dated.
A
transaction that fails to validate is rolled back.
+
Mr~ltiverszon Timestamps:
A
common technique in practice is for read-
only
transactiolls
to
l~e scheduled
by
timestamps. but with multiple ver-
sio~is, rvhere a !\-rite of an element does not overwrite earlier values of that
ele~nent until all transactions that could possibly need the earlier value

have finished. IYriting transactions are scheduled by conventional locks.
18.11
References
for
Chapter
18
The book
[GI
is an important source for niaterial on scheduling,
as
well as
locking.
[3]
is another
important
source. Two recent surveys of concurrency
control are
[I21
alid
[Ill.
Probably tlie most significant paper in the field of transaction processing is
[4]
on two-phase locking. Tlle ~varning protocol for hierarchies of granularity
is
from
[3].
Son-tx-o-phase locking for trees is from
[lo].
The compatibility
matrix was introduced to study behavior of lock modes ill

[7].
Timestaiups as a concurrency control rilethod appeared in
[2]
and
[I].
Sched-
uling by
~alidation is from
[a].
The use of riiultiple versions was studied by
[9].
1.
P.
.\.
Brln>tein arid
1.
Goodman. Ti~nestamp-based algorithms for con-
currency control
ill
distributed database systems."
Proc. Intl. COIL~. on
l'ery Large Databnses
(1980).
pp.
28.3-300.
2.
P.
;\.
Benlstein.
S.

Goodman.
J.
13.
Rothnie,
Jr
and
C.
H.
Papadirn-
itriou. -Anal\-4s of sprializabiIity in
SDD-1:
a system of distributed data-
bases
(the full rcdlrrlda~lt case)."
IEEE
Tra11,s. on Software En,g~:neering
SE-4:3 (197S).
pp.
1.54-168.
3
P.
.A.
Bclnitein.
\
Hadlilncoi. ant1
S
Goodman.
Concu~rency Corltrol
and Recocery
171

Datrrbnsr: Sgstems.
Iddlson-IYesley. Reading
\IX,
1987.
1.
K.
P.
Esn-amn.
J.
S.
Gray.
R.
-1. Lorie, and I.
L.
Traiger. "The notions
of consistency and
pledicate locks in a database system."
Comm.
iiCM
19:ll (1976).
pp.
624-633.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
988
CII.4PTER
IS.
CONCURRENCY
CONTROL
5.
J.

N.
Gray,
F.
Putzolo. and I.
L.
Traiger. "Granularity of locks and degrees
of consistency
in
a
shared data base," in
G.
AI.
Sijssen (ed.),
JJodelzng zn
Duta Base 121anngen~ent Systems,
North Holland, Amsterdam. 19iG.
6.
J.
X.
Gray and
A.
Reuter,
'II-nnsaction Processing: Concepts and Tech-
nzques,
Morgan-Kaufrnann. San Francisco, 1993.
7.
H.
F.
Korth, "Locking primitives in a database system,"
J.

ACM
30:l
(19831, pp. 55-79.
8. H T. Kung and
J.
T. Robinson, "Optimistic concurrency control,.'
ACM
Trans. on Database
Systems
6:2 (1981), pp. 312-326.
9.
C.
H.
Papadimitriou and
P.
C. Kanellakis, "On concurrency control by
multiple versions,"
ACM
Trans. on Database Systems
9:l (1984), pp. 89-
99.
10.
A.
Silberschatz and
2.
Kedem, "Consistency in hierarchical database sys-
'
terns,"
J.
ACM

273
(1980). pp. 72-80.
11.
A.
Thomasian, "Concurrency control: methods, performance: and analy-
sis,"
Computing Surveys
30:l (1998), pp. 70-119.
12.
B.
Thuraisingham and
H P.
KO, "Concurrency control in trusted data-
base
managelner~t systems: a survey,"
SIGMOD Record
22:4
(1993),
pp. 52-60.
Chapter
19
More About
Transaction
Management
111
this chapter we cover several issues about transaction managelllent that
were not addressed in Chapters 17 or 18. If7e begin by reconciling the points of
vien- of these two chapters: how do the needs to recover from errors, to allow
transactions to abort: and to maintain serializability interact? Then,
we discuss

the management of deadlocks
aillong transactions: which typically result from
several
transactio~ls each having to wait for a resource, such as a lock, that is
held
by
another transaction.
This chapter also incllldes an introduction to distributed databases. IVe
focus on ho1v to lock elements that are distributed among several sites, perhaps
with replicated copies. Ke also consider how the decision to co~nmit or abort
a
transaction can be rnade ~vhen the transaction itself involves actions at several
sites.
Finally,
consider the problems that arise due
to
''long transactions."
There are applications,
such
as CAD syste~lls or "workflow" systems, in which
llumaii
and
conlputer processes interact, perhaps over a period of days. These
systelns. like short-transaction systems such as banking or airline reservations,
need to preserl-e consistency of the database state. Ho\T-ever, the concurrexlcy-
control methods discussed in Chapter 18 do not rvork reasonably when locks
are held for days, or decisions to validate are based on events that 'happened
days
in the past.
19.1

Serializability
and
Recoverability
In Chapter 17 Xve discussed the creation of a log and its use to recover the
database state when a
system crash occurs. \Ye introduced the vie\\- of database
cornputatio~l in which values move bet\\-ecn nonvolatile disk, volatile ~nain-
menlor? and the local address space of transactions. The guarantee the various
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
090
CHAPTER
19.
AIORE
ABOUT TRAj\'SACTION
JIALVL4GE-IIEIVT
logging methods give is that, should a crash occur, it ~57ill be able to reconstruct
tlie actions of the committed transactions (and
only the committed transac-
tions) on the disk copy of the database.
A
logging system makes no attempt
to support
serializabil~ty; it w~ll blindly reconstruct a database state, even if
it is the result of a
noriserializable schedule of actions. In fact, commercial
database systems do not always insist on
serializabilit~; and in sorne systems.
serializability is enforced only on explicit request of the user.
On the
othcr hand, Chapter 18 talked about serializability only. Scliedulels

designed according to the principles of that chapter may do things that the log
manager cannot tolerate. For instance, there is
nothlng in the serializability
definition that forbids a transaction with a lock
on an element
A
from writing
a new value of
A
into the database before committing, and thus violating a rule
of the logging policy.
\Verse,
a transaction might write into the database and
then abort without undoing the
Ivnte, which could easily result in an incon-
sistent database state, even though there is no system crash and the scheduler
.
theoretically maintains serializability.
19.1.1
The
Dirty-Data
Problem
Recall from Section 8.6.5 that data is "dirty"
if
it has been written by a trans-
action tliat is not yet committed.
The dirty data could appear either in the
buffers, or on disk, or both; either can cause trouble.
A
:=

A+100;
wl(d);
Il(B);
ul(:l); 125
12
(.A);
7'2
(.A);
A
:=
A*2;
wq
(.A)
;
250
12
(B)
Denied
~1
(B);
Abort;
ul(B);
/2(B):
112
(24);
r2
(B):
B
:=
B*2:

tc,,(B): 112(O);
.5
0
Figure 19.1: TI writes dirty data and then aborts
Example
19.1
:
Let us rcconsider the serializable schedule from Fig. 18.13.
but suppose that after reading
B,
TI has to abolt for
sonic
reason. Then tlie
sequence of events is as in Fig. 19.1. After Tl aborts, the sclieduler releases the
lock on
B
that TI obtained; that step is essential, or else the lock on B would
be unavailable to any other transaction, forever.
Ho~i-ever,
T2
has now read data that does not represent a consistent state
of the database.
That is,
?r2
read the value of
-4
that TI changed, but read
the value of B that existed prior to
Ti's
actions. It doesn't matter in this casc

whether or not the value 125 for
il
that TI created n-as mitten to disk or not;
?'?
gets that value from a buffer, regardless. As a result of reading an incorlsistcr~t
state,
T2
leaves the database (on disk) with an inconsistent state, where
-4
#
B.
The problem in Fig. 19.1 is that
-4
~vritten by
TI
is dirty data, whether
it is in a buffer or on disk. The fact that
1;
read
-4
and used it in its on-n
calculation
makes z's actions questionable. -1s
we
shall see in Section 19.1.2.
it is necessary, if such a situation is allowed to occur, to abort and roll back
T2
as \\-ell as TI.
Figure 19.2: TI has read dirty data from
T2

and nlust abort n-hen
Tl
docs
Example
19.2
:
Sow, consider Fig. 19.2.1~11ich sho~vs a sequellce of actions
i~n-
der
a
timestamp-based scheduler as in Section 18.8. Ho~vever: lye ilnagille that
this sclleduler does not use the colnrnit bit that \\-as introduced in Section 18.8.1.
Recall that, the purpose of this bit is to prevent a value that !\-as n-ritten b>-
an uncommitted transaction to be read by anot,her transaction. Th~s, when
TI
reads B at the second step, there is no co~nmit-bit check to tell
TI
to delay.
TI can pr.oceed and could eve11 write to disk and commit; we haye not shoiv11
further details of 1v11at Tl dors.
Eyei~tually.
7;
tries to ~i-ritc
C
in a ph!.sically unrealizable \\-a?. and
T2
aborts. The effecr of fi's prior write of
B
is cancelled: the value and \\-rite-ti~np
of

B
is
reset
to 1~11at it was before
T2
wrote. I-et
TI
has been allo~i-?ti to use this
cancelled value of
B
and can do anything ~ith it: such as using it to conlpute
nex values of
.A.
B, and/or
C
and ~vriting them to disk. Thus. TI? ha\-ing read
a
dirty value of
B,
can cause an inconsistellt database state. Xote that. had
the commit bit been recorded and used, the read
rl(13) at step (2) would
have
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
992
C'H-4l'TER
19.
MORE
ABOUT
TRA.VSS-iCTION

AI-A-YilGElIEST
I
19.1,
SERI.~LIZ ~BILITY
1SD
RECOVERABI~~ITI-
993
been delayed, and not allowed to occur until after
T2
aborted and the value of
B
had been restored to its previous (presumably committed) value.
19.1.2
Cascading Rollback
AS xe see from the exam~~les above, if dirty data is available to transactions,
then
\ve so~netilnes have to perform a cascading rollback. That is, when a
transaction
T
aborts, we must determine ~vhich tralisactions have read data
written by
T,
abort thein: and recursively abort any tralisactions that have read
data written by an
a.borted transaction. That is, we must find each transaction
L'
that read dirty data written by
T,
abort
C':

find any transaction
5-
that
read dirty data from
li,
abort
V:
and so on. To cancel the effect of an aborted
transaction, we can use the log, if it is one of the types (undo or
undo/redo)
that provides former ~ralalues. We may also be able to restore the data from the
disk copy of the database, if the effect of the dirty data has not migrated to
disk.
These approaches are considered in the next section.
As Jve have noted, a ti~ncstamp-based scheduler witti a conlrnit bit pre-
vents a transaction that rnay Ilax-e read dirty data from proceeding, so there is
no possibility of cascading rollbaclc xvith such
a
scheduler.
-4
validation-based
sclieduler avoids cascading rollback, because
~vriting to the database (el-en in
buffers) occurs only after it is determined that the transaction
JX-ill colnmit.
19.1.3
Recoverable Schedules
In order for any
of
the logging metllods ~ve Ilave discussed in Chapter

17
to ailon-
1-ecovery. the set of transactions that are regarded as committed
after
recol-el?-
must be consistent. That is. if a transaction
TI
is, after recovery. rega~drd
as committed, and
Tl
used a value written by
G,
the11
T2
must also remain
committed. after recovei
5
Thus,
n
e define:
-1
schedule is rccove~able if earh tra~lsaction coinmits only after each tians-
action from n-hlcli it lias read lias committed.
Example
19.3:
111
this and several subsequent exa~nples of schedules n-it11
read- and n-rite-actions, we shall use c, for the action .'transaction
T,
commits."

Here is
an example of a recoverable schedule:
Sl
:
IC,
(A):
ICI
(B):
w2
(-4):
r2
(B): cl
:
r2:
Sote that
2'2
reacts a value
(B)
\nitten by TI. so
T2
must rol~imit aftcr
TI
for
the sclledr~lc to l~e rccovcrable.
Scliedule
S1
above is evidently serial (and tllerefore sc~ializablc) as n-ell as
recoverable, but the
two concepts are orthogonal. For instance, the following
variation on

SI
is still recoverable, but not serializable.
In schedule
S2,
T2 must precede
TI
in a serial order because of the writing of
-4. but
TI
~liust precede
T2
because of the n-ritirlg and readillg of
B.
Fillally. observe the follotving variation on
S1.
\vllich is serializable but not
rccoveiable:
In sclledule
S3:
TI precedes
T2:
but their cornrnitrne~lts occur in the wrong order.
If before a crash. the corlllllit record for
T'2
reachcd disk, but the conllnit record
for
Ti
did 11ot. then regardless of whether u~ldo, redo, or urldo/redo logging
,$-ere used:
6

~votild be committed after recovery, but
Tl
would not.
fJ
Irl order fc,r
schpclules to be truly recoverable under ally of the
three loggilrg methods, there is one additional assiiniption ac nlust make re-
garding schedules:
The log's colllmit records reach disk in the order in which they are written.
As 15-c observed in Example 19.3 concerning sclirdule
Sg.
should it be possible fol
coniniit records to reach di4k in the wrong order. then
consistent
lecovery might
be iInllossible, \ye
return to a~id exploit this prillciple in Section 19.1.6.
19.1.4
Schedules
That
Avoid
Cascading
Rollback
Recoverable sclletiules solnetimes require cascading rollback. For instance, if
after first four
steps
of ~clicdule
S1
in Esnl~iple 19.3 TI had to roll back,
it

n-ould be lleccssary to roll back TL, as n-ell. To guar:lntec the absence of
cascadillg rollback, lleed a stronger co~lditioll tlian rccowrabilit~.
11'~
Siiy
that
:
-1
schedule olioids cascarlzng rollback (or
-is
an
.4CR
schedfile") if trans-
actions
ma!
lead only values written
11.1.
co~lnnitted tiansactions.
Put
allotller \v\-a\ a11
XCR
schedule forbids the rcadi~ig of dirty data. As for re-
col-erablc sclledules. \ye assume that "comlnitted" ~neans that the log's comn~it
record
has
reaclled disk.
Exalllple
19.4
:
5clicdules of Exalnple
19.3

are not
-1CR.
111 each case.
T2
reads
B
frolll the uncomniitted transaction TI. Hon-ever. consider:
son.,
T?
rends
B
ollly after TI. thc transaction that last n.rotc
B.
has colnlnit-
red.
alld its log record n-rittc~i to disk. Thus. sc,hcdnle
S1
is
.ACR.
as 'vell as
rcco\.crablc.
sotice tllat sllould a transaction such as
T2
read a value mitten
11)-
TI
after
TI conrmits. then surely
fi
either co~nnlits or a1)orts after T1 commits. Thus:

Ever>-
;\CR
schedule is recotwable.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
9914
CYL4PTER
19.
AfORE ABOUT TIZAh-SACTION
hIIA17.~~~~f~hr~
19.1.
SERI.4LIZABILITY
AJ-D
RECO\~ER.~~~L~~-
9%
19.1.5
Managing Rollbacks Using Locking
Our prior discussion applies to schedules that are generated by any kind of
scheduler. In the common case that the scheduler is lock-based, there is a simple
and commonly used
way
to
guarantee that there are no cascading rollbacks:
Strict
Locking:
.%
transaction must not release any exclusive Iocks (or
other locks, such
as
increment locks that allo~ir values to he changed)
until the transaction has either

con~mitted or aborted, and the commit or
abort log record has been
flushed to disk.
A
schedule of transactions that follow the strict-locking rule is called a
strict
schedule.
Two important properties of these schedules are:
1.
Every strict schedule
is
ACR.
The reason is that
a
transaction
T2
cannot
read
a
value of element
X
written by TI until Ti releases any exclusive
lock (or similar lock that
allolvs
X
to be changed). Under strict locking,
the release does not occur until after commit.
2.
Every strict schedule is serialzzable.
To see

why,
ohscrve that a strict
schedule is equivalent to the serial schedule in which
each tra~isaction
runs instantaneously at the time it commits.
IVith these observations,
we
can now picture the relationships among the dif-
ferent kinds of schedules we have seen so far. The containments are
suggested
in Fig.19.3.
Figure 19.3: Containments an noncontai~lments among classes of schetlules
Clearly. in a strict schedule. it is not possihle for a transaction
to
rcad dirty
data.
since data written to a huffer by an unconilnitted transaction re~nairls
locked until the transaction commits. Ho~vever: we still have tlie prohleni of
fising the data in buffers when
a
transaction aborts, since these cllallges must
have their effects cancelled. How difficult it is to
fix
buffered data depellds on
~vhether database elements are blocks or sornethi~lg smaller. \Ye shall consider
each.
Rollback
for
Blocks
If the lockable database elements are blocks. then there is a simple rollback

method that never requires us to use tile log. Suppose that a transaction T has
obtained an esc1usi~-e lock on block
A.
written a new value for
A
in a buffer,
and then had to abort. Since
-4
has been locked since
T
xvrote its value,
110
other transaction has lead
-4.
It 1s easy to restore the old value of
-4
provided
the
folloning rule is follo~ved
Blocks ~vritten by uilcominittcd transactiolls are pinned in main memory;
that is. their buffers are not
alloxved to be written to disk.
I11 this case. ne roll back.'
T
when it aborts by telling the buffer manager to
ignore the
value of
A.
That is, the buffer occupied by
-4

is not written anywhere,
and
its buffer is added to the pool of available buffers. \Ve call be sure that the
value of
A
on disk is the most recent value written by a committed transaction,
which is c~actly the value we want
A
to have.
Tllele 1s also a sinlple rollback method if we are using
a
multiversion system
as in Sections
18.8.5
and 18.8.6. \Ye niust again assume that blocks written by
~incomniitted transactions are pinned
111
memory. Then, we simply renlove the
value of
A
that was mitten by
T
from the list of available values of
A.
Sote
that because
T
was a i\iiting transaction, its value of
.I
~vas locked from the

time the lalue n.as \vritten to the time
it
aborted (assuming the timestamp/lock
scheme of Section 18.8.6 is used).
Rollback for
Small
Database E1ement.s
When lockable database elenlcnts are fractions of
a
block (e.g., tuples or oh-
~ects). then the sinlple appioach to restori~lg buffels that have been ~nod~fied hl-
aborted transactions nil1 not uoik The p~ohle~n is that a buffer may contain
data changed by t~vo or more transactions: if one of them aboits, Tve still nlust
plesesve tlie changes made by the other
\le
have several choices \vhen we must
restore
thc old value of a small database element
A
that n-as written by the
tlansaction that has a11ortt.d.
1. We can read the original value of
I
from the database stored on disk and
modify the buffer contents appropriately.
2.
If the log is an undo or untlo/redo log. then we can obtain the former
value from the log itself.
The
same code used to recover frorn crashes

ma?. be used for \-oll~ntary" rolll~acks as \~-cll.
3.
\IF can keep a separare ~nair~-l~lclr~ory log of the changes n~ade by car11
I
transaction, preserved for only the tinlc that transactio~l is active. The
i
old value call be fouxid fro111 this "log."
Sone of these approaches is ideal. The first s~~rely il~rolves a disk access.
The second (examining the log) might not involve a disk access. if the relevant
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
996
CHAPTER
19.
MORE ABOUT
TRAATSACTION JlilA-~lGE:lIEXT
When is a Transaction Really Committed?
The subtlety of group commit reminds us that a completed transaction can
be in several different states between
when it finishes its xvork and when it
is truly "committed." in the sense that under no circumstances, including
the occurrence of a system failure, will the effect of that transaction be
lost.
As
we noted in Chapter
17,
it is possible for
a
transaction to finlsh
its work and even write its
COMMIT

record to the log in a main-memory
buffer, yet have the effect of that transaction lost if there is a system crash
and the
COMMIT
record has not yet readied disk.
Lloreover, we saw in
Section
17.5
that even if the
COMMIT
record is on disk but not yet backed
up in the archive,
a
media failure can cause the transaction to be undone
and its effect to be lost.
In the absence of failure, all these states are equivalent, in
the sense
that each transaction will surely advance from being finished to having its
effects survive
even a media failure. However, when rve need to take failures
and recovery into account, it is important to recognize the differences
among these states, which
otherwis'e could all be referred to informally as
'L~ommitted."
portion of the log is still in a buffer Hone1 er. it could also invol~ e extensix e
esamination of portions of the log on disk. sea~ching for the update record that
tells the correct former value.
Tlie last approach does not require disk accesses.
but may consume a large fraction of
menioi y for the main-memory '.logs."

19.1.6
Group
Commit
Under some circumsta~ices, n-e can avoid reading dirty data even if re do not
flush
every commit record on the log to disk immediately. As long as a-e flush
log records in the order that they
ale written, we can release locks as soon as
tlle commit record is written to tlie log in a buffer.
Example
19.5:
Suppose transaction TI I\-rites
X,
finishes, writes its
COMMIT
record on the log, but the log record remains in a buffer. Even though TI
has not committed in the sense that its connilit record can survive a crash.
we shall release TL's locks. Then
T2
reads
S
and .'colnmits." but its co~nn~it
record, n-hicli follows that of TI. also remains in
a
buffer. Since we are flushing
log records
ill the order 1s-ritten.
T2
cannot be perceived as co~nmittcd
b?-

a
recovery manager (because its commit record reached disk) unless
Tl is also
perceived as committed. Thus, there arc three cases that the recovery manager
could find:
1. Neither
TI
nor
T.L
has its commit record on disk. Then both are aborted by
the recovery manager, and the fact that
T2
read
S
from an uncommitted
2.
TI is comnlitted. but T2 is not. There is no problerri for two reasons:
T2
did not read
S
from an uncomlnitted transaction, and it aborted anyway.
with no effect on the database.
3.
Both are corrnnitted. Then the read of
S
by Tz was not dirty.
On the other hand, suppose that the buffer containing
Tz's commit record
got flushed to
disk (say because the buffer manager decided to use the buffer

for
somet11i1:g else). but the buffer containing
TI'S
commit lecord did not.
If
there is a crash at that point. it will look to the recovery manager that
TI
did
not commit, but
T2
did. The effect of
T2
will be perlrianently reflected in tlie
database, but this effect was based on the dirty read of
X
by
T2.
Our conclusion from Esa~nple 19.5 is that we can release locks earlier than
the time that the transaction's commit record is flushed to disk. This policy,
often called
giazp
commit. is:
Do iiot release locks until the transaction finishes: and the comniit log
record at least appears in a buffer.
Flush log blocks in the order that they \\-ere created.
Group commit. like the policy of requiring .'recoverable schedules" as discussed
in Section 19.1.3, guarantees that there is never a read of dirty data.
19.1.7 Logical Logging
We salv in Section 19.1.5 that dirty reads are easier to
fis

up rvhen the unit of
locking is
the block or page. Holvever, there are at least two problems prese~lted
when database elements are blocks.
1.
-411 logging methods I-equirc either the old or new value of a database
element, or
both: to be recorded in the log. \Vhen the change to a block
is small,
e.g., a ren-rittcri attribute of one tuple.
or
an
inserted or deleted
tuple, then
there is a great deal of redundant information written on tile
log.
2.
Tlie recluireme~it that the schedule be recoverable; releasing its locks only
after
co~nnlit. car1 illhibit concurrency severely. For esample, recall our
discilssion in Section 18.7.1 of the advantage of early lock release as
xr
access data tllro,lgll a B-tree indes. If we require that locks be helti until
connnit. thcn this advalitagc cannot be obtained: and n-e effectively allon-
only one writing transaction to access a B-tree at any time.
Both these concerns motivate the use of
logical logging.
villere only the
changes to the blocks are described. There are several degrees of coniplesity.
depending on the nature of the change.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1.
.A
small rlunlber of bytes of the database element are changed, e.g the
update of a fixed-length field. This situation call be handled in
a
straight-
forward way, where
we record only the changed bytes and their positions.
Example 19.6
\rill show this situation and an appropriate form of update
record.
2.
The change to the database element is simply described; and easily re-
stored, but it
has the effect of cliangiiig most or all of the bytes in the
database element. One coninion situation: discussed in Example 19.7: is
when a variable-length field is changed and illuch of its record, and even
other records must slide
within the block. The new and old values of the
block look very different unless
we realize and indicate the simple cause
of the change.
3.
The change affects many bytes of
a
database clement, and further changes
call prevent this change from ever being undone. This situation is true
"logical" logging, since
we cannot even sec the undo/rcdo process as occur-

'
ring on the database elenieilts thetiiselves, hut rather on some higher-level
'.logical" structure that
tlie database elenletits represent.
1%
shall, in
Es-
ample
19.8,
take up the matter of B-trees, a logical structure represe~ited
by database clements that are disk blocks, to illustrate this co~rlples form
of logical logging.
Example
19.6
:
Suppose database elements are blocks that each contain a set
of tuples from some relation.
11'e call express the update of an attribute
by
a
log record that says somethirig like
.'tuple
t
had its attribute
a
changed fro~n
vahie
~'1
to
02.''

An insertion of a nerv tuple into empty space on the block can
be expressed as "a tuple
t
with value
(nl.
a2:.
.
.
:
ak)
was inserted beginning
at offset position
p."
Unless the attribute changed or the tuple inserted are
comparable in size to
a
block, the alnount of space taken by these records will
be much smaller than the entire block. lloreot-er, thcy serve for both undo and
redo operations.
Notice that both these operations are
idernpotent; if you perform them
scv-
era1 tinlcs on a block; the result is the same as perfor~ning them once. Liken-ise.
thcir implied inrerses, I\-here the value of
t[n]
is restored from
vz
back to 1.1. or
the tuple
t

is removed. are also idenrpoteiit. Thus. records of these types can
be used for rccol-cry in exactly tlie same
way
that update log rccords were used
throughout Cliaptcr 17.
0
Exanlple
19.7:
Again assunic database clc~nents arc blocks lioldiiig tl~plc.
but the tul~les Ilavc sonie rariahle-lengtil ficlds.
If
a
cllt~~~ge to a fi~ld such
as
Ivas described in Exalilple
19.6
occurs,
n.e
niay 1la1-e to slide large portio~ls of
the block to make room for
a
longer field. or to preserve space if a ficld beco~~ics
smaller. In extreme cases, ~ve could have to crcatc ail overfloxr block (1.c~cal1
Section 12.5) to hold part of the contents of the original block, or
wc
could
remove an
ovc.rflo\v block if
a
shorter field allows us to combine the contenrs of

two bl~clis into one.
As 101ig as the block and its o\.erflow block(s) are considered part of one
database
cl~inent, then it is straightforward to use the old and/or new value of
tlic changed field to tundo or redo the change. Ho~vever, the block-plus-overflox~~-
bloik(s) must l~e thougilt of as holding certain tuples at a "lo@cal" level 1Ve
nlay not even be able to restore the bytes of these blocks to their original state
after an
undo or redo, because there nlay have been reorganization of the blocks
due to othcr cliarges that varied the length of other fields. Holvever. if we think
of a database
ele~nent as being
a
collection of blocks that together represent
certain
tupleb. tile11 a redo or undo can indeed restore the logical *state" of the
eleme~it.
O
Hoxvever, it ]nay not be possible, as we suggested in Example 19.7, to treat
blocks as expandable
through the mechanis~ll of overflow blocks. IVe nmay thus
be able to
undo or redo actions only at
a
level higher than blocks. The next
esample discusses the important case
of
B-tree indexes, nhere the management
of blocks does not
perinit ove~flow blocks, and we must think of undo and redo

as
occuiring at the logical level of the B-tree itself; rather tllan the blocks.
Example
19.8
:
Let us consider the problem of logical logging for B-tree nodes.
Instead of
xvriting the old and/or new value of an entire node (block) on the
log.
we n-rite a short record that describes the change. These changes include:
1.
Insertion or deletion of a key/pointer pair for a child.
2.
Change of the key associated \x-it11
a
pointer
3.
Splittirig or ~rlerging of nodes.
Each of these changes call be indicated with a short log record. Even the
splittin: operation requires only telling xvhere t,he split occurs; and ivhere tahe
iiex
lodes
are. Likewise: merging requires only a reference to the nodes in-
volved; since
rhe manner of rnergirlg is determined by the B-tree rnallagenlent
algorithms used.
csillg logical iljii!at~ rerorris of these tj-pesalloirs us to release locks earlier
than xrould othern-ise be required for a recoverable schedule. The reasoil is
that dirt- reads of B-tree blocks are never a problem for the transaction that
reads

tl~ein. provided its only purpose is to use the B-tree to locate the data
the transaction needs to access.
For instance. suppose that
tra~lsactioll
T
reads a leaf node
dY.
but the trans-
action
c-
tilat 1a.t wrote
-\-
lates aborts. and sorne change nlade to
S
(e.g.; the
illscrrioll of
a
nelr keT/lloillter pair into
due to an insertion
of a
tuple
b\.
liceds to be undone. If
T
has also inserted a key/poi~~ter pair into
S.
then it is
liot possiMe to restore
'.
to the !ray it was before

LT
inodified it. Hoxevcr. tlie
effect of
L-
on
-\-
call be undone; in this exa~nple n-e would delete the key/pointer
pair that
C
had iiiscrted. Tlie resulting
5
is riot the same as that irllich ex-
isted before
U
operated: it has the i~lsertion made by
T.
Hon-ever, there is no
database inconsistency.
siilcc the B-tree as
a
ivhole continues to reflect only the
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1000
CHa4PTER
19.
MORE ABOUT TRAA7S.4CTION AlANrlGEJlEl-T
changes made by committed transactions. That is, we have restored the B-tree
at a logical level, but not at the physical level.
19.1.8
Recovery

From
Logical Logs
If the logical actions are idempotent
-
i.e they can be repeated any number
of times without harm
-
then we can recover easily using a logical log. For
instance, we discussed in Example 19
6
how a tuple insertion could be repre-
sented in the logical log by the tuple and the place within a block where the
tuple
was
placed. If we write that tuple in the same place two or more tune5
then it is as if we had written it once. Thus. when recovering, should \ve need
to redo
a
transaction that inserted a tuple, we can repeat the insertion into
the proper block at the proper place, without
worrying whether me had alread~
inserted that tuple.
In contrast, consider a situation
ishere tuples can move around withi11 blocks
or between blocks, as in Examples 19.7 and 19.8. Sow,
we cannot associate
a
particular place into which a tuple is to be inserted; the best we can do is place
in the log an action such as
'.the tuple

t
was inserted somewhere on block
B
If we need
to
redo the insertion of
t
during recovery, we may ~r,iild
up
with tno
copies oft in block
B.
W'oise, we may not know whether the block
B
1vit11 tlle
first copy oft made it to disk. Another transaction writing to another database
element on block
B
may have caused a copy of
B
to be written to disk. for
example.
To disambiguate situations such as this
~vhen we recover using a logical log.
a technique called log sequence numbers
has been developed.
Each log record is gi~en a number one greater than that of tlle previous
log record.' Thus, a typical logical log record has
the form
<L,T.

.I.
B>.
where:
-
L
is the log sequence number, an integer.
-
T
is the transaction involved.
-
A
is the action performed by
T.
e.g., "insert of tuple
t."
-
B
is the block on which the action was performed.
For each action, there is a cornpensating
action
that logically undoes the
action. -4s discussed in Esample
19.8.
the compensating action niny
not
restore the database to exactly the same state
S
it ~vould liar-e I~cc~l
in
had the action never occurred, but it restores the database to a statc that

is logically equivalent to
S.
For instance, the compensating action for
"insert tuple
t"
is "delete tuple t."
'~ventually the log sequence numbers must restart at
0;
but
the
time
hetween restarts
of
the
sequence is so large that no ambiguity can
occur.
19.1.
SERMLIZ.4BILITY AND RECOVERABILITY
If a transaction
T
aborts, then for each action performed or1 the database
by
T,
the compensating action is performed, and the fact that this action
was
performed is also rccorded in the log.
Each block maintains, in its header, the log sequence number of the last
action that affected that block.
Suppose
noxv that we need to use the logical log to recover after a crash.

Here is an
outlirie of tlle steps to take.
1.
Our first step is to reconstruct the state of the database at the time of the
crash. including blocks
xvhose current values were in buffers and therefore
got lost. To do so:
(a)
Find the most recent checkpoint on the log, and determine frorn it
the set of transactions that nere active at that time.
(b) For each log entry
<L,T,
A,
B>,
compare the log sequence number
IV
on block
B
with the log sequence number
L
for this log record.
If
!V
<
L,
then redo action
A:
that action was never perfornled on
block
I?.

However, if
N
2
L.
then do nothlng; the effect of
'4
was
already felt by
B.
(c) For each log entry that informs us that a transaction
T
started, com-
mitted,
or aborted, adjust the set of active transactions accordingly.
2.
The set of transactions that remain active .evllcn .se reach the end of the
log
must be aborted. To do so:
(a) Scan
the log again, rhis time from the end back to the plel-ious check-
point. Each time
we
encounter a record
<L.
T,
A.
B>
for a transac-
tion
T

that must be aborted. perfor111 the compensating action for
-4
on block
B
and record in the log the fact that that compensatillg
action was performed.
(b) If
we must abort a tiansaction that began prior to the most recent
checkpoint
(i.e., that transaction was on the active list for the check-
pillt). then continue back in the log until tile start-records fo~ all
such trailsactions have been found.
(c)
Write abort-records in the log for each of the transactions we had to
abort.
19.1.9
Exercises
for Section
19.1
*
Exercise
19.1.1
:
Consider
all \\-ays to insert locks (of a single type only. as
in
Section
18.3)
into the sequellce of actiorls
so that the transaction TI is:

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1002
CH.4PTER
19
NORE
ABOUT
TR-I*\SACTION
JIANIGEJIEYT
19.2.
I'IEIV SEXI,-LLIZ IBILITI- 1003
a) Two-phase locked, and strict.
19.2
View Serializability
b) Two-phase locked, but not strict.
Exercise
19.1.2: Suppose that each of the sequences of actions below
IS
fol-
lolved by an abort action for transactio~l TI. Tell whicli transactions need to be
rolled back.
*
a) r1(24); rz(B); wl(B); ~2(C)j rj(B); r3(C); 703(D);
b) rl (A): ml (B); rz(B); 102(C); r3(C); w3(D);
c) r2(A); r3(A); rl(A); w(B); r2(B): rz(B); m2(C); r3(C);
d) 72(-4); r3(A); rl(A); wl(B); rd(B); IUL(C); r3(C);
Exercise 19.1.3: Consider each of the sequences of actions in Exercise 19.1.2.
but now suppose that all three transactions cornrnit
and write their cornillit
record on the log immediately after their last action. Hon-ever, a crash occurs.
and a tail of the log mas not

writtcn to disk before the crash and is therefore
lost. Tell, depending on where the lost tail of the log begins:
2.
f
hat transactions could be consideled uncomnlitted9
ii.
ilre any dirty reads created during the recovery process?
If
so. n-hat
transactions need to he rolled back?
zii.
\$-hat additional dirty reads could have been created
if
the portion of tlie
log lost
was
not a tail. but rather solne potions in the middle?
!
Exercise 19.1.4
:
Consider the folloa-ing tn-o transactions.
TI:
WI
(-4):
(B);
r~
(C):
cl;
T2: WZ(-4): TZ(B): ?U~(C) CZ;
*

a) HOW nnany schedules of Tl and
T2
are rccovcrable?
b) Of these. how many are
.ICR
sclietlules?
c) How many are both rccoveral~lc and scrializnble?
d) How many are both
.iCR
and serializable?
Exercise 19.1.5: Give an example
of
an
.ICR
schedule wit11 shared and es-
clusive locks that is not strict.
Recall our discussion in Section
18.1.4
of how our true goal in tlie design of a
scheduler
is to allow only schedules that are serializable. We also saw how tiif-
ferences in what operations transactions apply to the data call affect whether or
not a given schedule is serializable.
lye also learned in Section
18.2
that sched-
uler~
nor~nally ellforce "conflict serializability," which guarantees serializability
regardless of
what tlie transactiolls do with their data.

However, there are weaker conditions than
conflict-serializability
that also
guarantee serializability. In this
sectiorl we shall consider one such condition,
called
.'vie\v-serializability:'
Intuitively, view-serializability considers all the
connectio~is between transactions
T
and
li
such that T writes a database el-
ement
~vhose value
U
reads. The key difference between view- and conflict-
serializability appears when
a
transaction
T
writes a value
A
that no other
transaction reads (because some
other transaction later writes its om11 value for
.A).
In that case, the KT(-4) action can be placed in certain other povitiolls
of the schedule (where
A

is like~vise never read) that ~vould not be permitted
under the definition of
conflict-serializability.
In this section, 11-e shall define
vie~v-serializability precisely and give a test for it.
19.2.1
View
Equivalence
Suppose we have two scheduIcs
S1
and
S2
of the same set of transactions.
Imagine that there is a hypothetical transaction To that wrote initial \alu?s for
each database element read by any transaction in the schedules, and another
hypothetical transaction
Tj
that reads every element written
by
one or more
tra~isactions after each schedule ends. Then for every read action ri(*.I) in one
of the schedules.
17c can find the write action
luj(;l)
that most closely preceded
the read in
question.' We say
T,
is the source of the read action ri(=l). Sote
that transaction

Tj
could be the lippothetical initial tra~isactioll
To,
and
Ti
could be
Tf
.
If for every read action ill one of the schedules, its source is the same in
the other schedule,
we say that
S1
and
Sg
are view-equivalent. Surely, view-
equivalent schedules are truly equivalent; they each do the same when executed
on any one database state. If a scliedille
S
is vie~v-equivalent to a serial schedule.
we say
S
is view-serializable.
1
Example 19.9
:
Consider
the \chetlulr
S
defined by:
TI

:
rl(-J)
1L-1
(B)
T?: r2(B) ~"(~4)
w2(B)
73
:
r3(-4)
1~'~
(B)
'~Vhile we ha\e not previously prevented
a
transaction from writing an element twice.
there is generally no need for it to do so. and in this study it is useful to assume that a
f
transaction only jvrites
a
given element once.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1004
CH-APTER
19.
AiORE ABOUT TR.AXSACTION .lIA4LY-4GE-\1EST
19.2.
SERIALIZ-4BILITY
Sotice that vie have separated the actions of each transaction vertically: to
indicate better
which transaction does what; you should read the schcd~lle from
left-to-right, as usual.

In
S,
both
TI
and
T2
write values of
B
that are lost; only tbe value of
B
written by
T3
survives to the elid of the schedule and is "read.' by the
hypothetical transaction Tf.
S
is not conflict-serializable. To see
rvhi,
first note
that
T2
writes
A
before TI reads
A:
so
l'?
must precede TI in
a
hypothetical
conflict-equivalent serial schedule.

TIie fact that the action
,lnl
(Bj
precedes
IC~(B) also forces TI to precede
T2
ill any co~iflict-equivalent serial schedulc.
Yet neither al(B) nor l(i2(B) has any long-term affect on tlie database. It is
these sorts of irrelevant
\\,rites that vien serializability is able to ignore, when
determining the true constraints on an equivalent serial schedule.
hIore precisely, let us consider the sources of all the reads in
S:
1.
The source of
rz(B)
is
To,
since there is no prior write of
B
in
S.
2. The source of rl(A) is
T2,
since
T.l
most recently wrote
-4
before the read.
3.

Likewise, the source of r3
(-4)
is
T2.
4.
The source of the hypothetical read of
=I
by
Tf
is
T2.
5.
Thc source of thc hypothetical read of
B
by
Tf
is
TJ,
the last witer of
B.
Of course, To appears before all real transactions iri any schrtiule, arid
Ij
ap-
pears after all transactions. If
we order
the
real transactions
(T.L:
TI.
T3).

then
the sources of all reads are the
same as in schedulc
S.
That is,
T2
reads
B,
and
surely
TO
is the previous "15-riter."
Tl
reads
-4;
but
Tz
already wrote
l.
so the
source of rl(.4) is
T2,
as in
S.
T3
also reads
.4:
but since the prior
T.2
\{-rote

-4.
that is the source of r3( l), as in
S.
Finally, the hypot,hctical Tf reads
-4
and
Bj
but the last writers of
d
and
B
in the sched~le
(T2:
TI, T3) are
T2
and
T3
rc-
spectivel!; also as in
S.
Ke conclude that
S
is a view-serializable scliedule, and
the schedule represented by the order
(fi,
TI:
T3)
is
a
vien cquivaleiit

schedule.
19.2.2
Polygraphs
and
the
Test
for
View-Serializability
Therc is a gcneralization of the precedence graph. ivhicll
n-c,
iiscd to tcst co11-
flict scri;ilixal~ility in
Section
18.2.2.
that reflects all thc prcc.odcncc, constrai~lts
required
1))-
thc dc~finition of vicn- scl.ializability. \Ye tl(+i~lr) ill(,
pol!/grclpli
for
;i
schedule to consist of the follo~ving:
1.
-1
node for cach transaction and additional rlodcs for tlic hypothetical
transactions
To
arid Tf.
2. For each action r,(S) with source
T,.

place an arc froni T, to
T,.
3.
Suppose Tj is the source of a read
ri(X),
and
Tk
is another ~vriter of
X.
It is not allowed for
Tk
to intervene between T, and
Ti,
so it must appear
either before
T, or after
Ti.
nTe represent this condition by an
arc
pair
(sho~r-n dashed) from Tk to Ti and froni
Ti
to
Tk.
Intuitively: one or the
other of an arc pair is
.'real," but lve don't care which, and when xe try
to
make the polygraph acyclic,
we

can pick whichever of the pair helps to
make it acyclic.
Honever? there are important special cases where the arc
pair becomes
a
single arc:
(a)
If
Tj
is
To,
then it is not possible for
Tk
to appear before
T',
so we
use an arc
Ti
+
Tk
in place of the arc pair.
(b) If
Ti
is Tf; then
Tk
cannot follow
Ti,
so we use an arc
Tk
+

Tj
in
place of the arc pair.
B
A
Figure 19.4: Beginxling of polygraph for Esample 19.10
Example
19.10:
Consider the schedule
S
from Example 19.9. \Ire show in
Fig. 19.4 the beginning of the polygraph
fo~
S,
where only the nodes and the
arcs
fi-om rule (2) have hcen placed. \Ye have also indicated the database
elemcnt causing each arc. That is,
-4
is passed from
T2
to TI.
T3.
and
Tf,
while
B
is passed fro111
To
to

T2
and from
T3
to Tf.
?;o\v, n.e lllust considel n-hat transactioils might interfere with each of these
five connections
by
n-~iting the same clen~cnt bet~vecn them. These potential
interferences are ruled out by the arc pairs from rule
(3).
although as
n-e
shall
see, in this example each of the arc pairs inrolves a special case and becomes a
single arc.
Consider the arc
&
-+
Ti based
on
eleliler~t
d.
The only writers of
A
are
To
and
T2.
and ncitller of rllem can get in tlie iniddle of this arc: since
To

cannot
move its posirioll. and
T2
is already an aid of the arc. Thus.
110
additional arcs
are needed.
;\
sinlilar argurntnt tells us no additional arcs are needed to keep
writers of
I
outside the arcs
T2
-+
7;
and
T?
-t
Tf.
So~r- collsider the arcs based on
B.
Xote that
To.
TI.
T?.
and T3 all n-rite
B.
Consider the arc To
-+
T2

first. TI and
T3
are otlier writers of
B:
To
and T2
also ~yrite
B;
but as sav, the arc ends cannot cause interfererlce. so we need
not consider them.
-1s we cannot place TI bet\\-een
To
and
T2,
in principle \re
need tlic arc pair
(TI
-+
To
T.r
-+
TI).
Honever. nothing can precede To, so
the optioll
TI
-+
To
is not possible. \Ye may in this special case just add the
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1006 CHAPTER

19.
AIORE ABOUT TRANS24CTION AM-TAGEJIENT
1"
19.2.
1-IETV SERI-4LIZdBILITY 1007
arc
T2
-+
Ti to the polygraph. But this arc is already there because of
.4,
so in
effect, we make no change to the polygraph to keep
Ti outside the arc
To
-+
T2.
We also cannot place
T3
between
To
and
T2.
Similar reasoning tells us to
add the arc
Tz
-+
T3,
rather than an arc pair. However, this arc too is already
in the polygraph because of
A,

so we make no change.
ivext, consider the arc
T3
-+
Tf.
Since
To, TI,
and
Tz
are other writers of
B,
we must keep them each outside the arc. To cannot be moved between
T3
and
Tf:
but
TI
or Tz could. Since neither could be moved after
Tf.
re must
constrain
Ti and
T.L
to appear before
T3.
There is already an arc
Tz
-+
T3, but
we must add to the polygraph the arc

Tl
-+
T3.
This change is the only arc we
must add to the polygraph, whose final set of arcs is shown in Fig. 19.5.
Figure 19.5: Complete polygraph for Example 19.10
Example
19.11
:
In Example 19.10, all the arc pairs turned out to be single
arcs as a special case. Figure 19.6 is an example of
a
schedule of four transac-
tions where there is a
true arc pair in the polygraph.
7-3(C);
wl
(B);
7-4
(B):
U'3
(A)
:
T4
(C);
w2
(0):
7-2
(B);
w4(.4);

u:4(B);
Figure 19.6: Esample of transactions whose polygrapl~ requires an arc pair
Figuie 19.7
sho\vs the polygraph, with only the arcs that conle fiolil the
source-to-reader connections.
.As
in Fig. 19.4 we label each arc by the element
(s)
that require it.
We
must then consider the possible ways that arc pairs could
be added. As
we saw
in
Example 19.10, there are several silnplifications Ive can
make.
\Then avoiding interference with the arc
T,
-t
T,,
the only transactiol~s
that need be considered as Tk (the transaction that cannot be
in
the middle)
are:
\Vriters of an e!ement that caused this arc
T,
-+
T,.
But not

To
or
Tf,
15-hich can never be Tn and
Sot
Ti
or
T,,
the ends of the arc itself.
\\*it11 these rules in mind. let us co~lsider the arcs due to database element
.4.
\\-l-hich is xritten by
To.
T3. and T4. \Ye need nut consider
To
at all.
T3
must
not get between
T4
-+
Tf.
so \ve add arc
T3
-+
T4; remember that the other
arc
in
the pair,
Tf

+
T3 is not an optiotl. Likewise, T3 must not get between
To
-+
Tl
or
To
-+
T2,
tvhich results in the arcs
TI
-+
T3
and
T2
-+
T3.
Figure 19.7: Beginning of pol\-graph for Example 19.11
Sou-, coilsider the fact that T4 also
must
not get in the middle of an alc
due to
-4.
It is all end of T4
-+
Tf.
so that alc is irrelevant.
TI
must not get
bet~~een

To
-+
TI
or
To
-+
T?
n-hicli ~esults in the arcs
TI
T4
and
ir?2
4
T4.
Sest. let us consider the arcs due to
B.
nhich is witten by
To, T1,
and T4.
.igain we need not consider
To.
The only arcs due to
B
are
TI
-+
T?,
TI
-+
T4,

and
T4
-t
Tf. Tl
cannot get in the middle of the first t~o, but the third requires
arc
Tl
-t
T4.
T4 can get in the middle of TI
-+
fi
only. This arc has neither end at
To
or Tf:
SO
it really requires an arc pair: (7.1
-+
TI,
Tz
-+
T4).
We show this arc
pair, as
well as all the other arcs added, in Fig.
19.8.
Test. consider the writers of C:
To
and Ti. -1s before,
To

cannot present a
problem. -41~0,
TI
is par[ of el-ery arc due to
C'.
50
it cannot get in the middle.
Similarl\
D
is ~~ritten only
by
To and
fi.
so
n-c
can dctcrmine that no Inore
arcs are nccessar): The final j~ol~graph is thus the one in Fig. 19.8.
i
19.2.3
Testing
for
View-Serializability
Since we must choose only one of each arc pair. we can find an equivalent serial
order for schedule
S
if and onl? if there is son-he selection from each arc pair
that turns
S's
polygraph into an acyclic graph. TO see why, notice that if there
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

1008 CHAPTER
19.
MORE ABOUT TRAA'SACTION ~fAX.4GEJ1EAJT
Figure 19.8: Complete polygraph for Example 19.11
is such an acyclic graph, then any topological sort of the graph gives an order in
which no writer may appear
between a reader and its source, arid every n-riter
appears before its readers. Thus, the reader-source connections in the serial
order are exactly the same as in S; the
two schedules are view-equivalent, and
therefore
S
is view-serializable.
Conversely, if
S
is view-serializable. then there is a view-equivalent serial
order
S'
E~ery arc pair (Tk
+
T,.
T,
-t
Tk) in S's polygraph niust have
Tk either before
T,
or after
T,
in
S':

otherw~se the writing by Tk breaks the
connection from
T,
to T,, which means that
S
and
Sf
are not view-equivalent
Likewise every arc in the polygraph
must be respected by the transaction order
of Sf.
Ke
conclude that there is a choice of arcs from each arc pair tliat makes
the polygraph into a graph for which the serial order
S'
is consistent with each
arc of the graph. Thus, this graph is acyclic.
Example
19.12: Consider the polygraph of Fig. 19.5. It is already a graph.
and it is acyclic. The only topological order is
(T2, TI, T3), which is therefore a
view-equivalent serial order for the schedule of
Example 19.10.
Sow consider the polygraph of Fig. 19.8. We must consider each choice from
the one arc pair. If we choose
T4
-t
TI. then there is a cycle. Honever, if we
choose
Tz

+
T4,
the result is an acyclic graph. The sole topological order for
this graph is
(Tl.T2, T3, T4). This order yields a view-equivalent serial order
and
shon-s that the original schedule is vie\\ serializable.
CI
19.2.4
Exercises for Section
19.2
Exercise 19.2.1
:
Draw tlie polygraph and finti all view-equ~valent se~ial orders
for the
following schedules:
*
a) ~1(.4); r2(.4); rg(.4); wl (B); w2(B); tc3(B);
C)
r1
( l):
r3(D):
ZL-~
(B): rd(B):
u3(B);
r4(B): 1b(C): rs(C): w4(~); rj(~);
1LV5
(B):
!
Exercise 19.2.2

:
Below are solne serial schedules. Tell 1io1v many schedules
are
(i)
coilflict-eqlli\.alcnt and
(zi)
vie\-;-equivalent to thcse yerial ~~Ii~dul~s.
*
a)
1-1
(-4); (ul(B); ~~(~4); lcq(B); r3('A) w3(B); that is, three transactions
each read
.4 and then write B.
b)
rl (.A); u)l
(B):
lC1
(C):
r3
(-4):
ulr
(B).
1r2(C);
that is. t~o trailsactioris each
read
-4 and then write B and
C.
19.3
Resolving Deadlocks
Several tinles \\-e have obscr\.ed that concurri.ntly es~cuting transactiorls can

compete for resources
and thereby reach
a
state \\-here there is a dearflock: each
of several transactions
is
waiting for a resource held
by
one of the otllcrs, and
none call make progress.
In Section
18
3.4
\ve saw how ordinaly operation of t~vo-pht~se-locked
transactions can still lead to a deadlock, because each has lockcd sonie-
thing that an0thc.r tral~sactio~i also needs to lock.
e
I11 Scc.tlun
15 1.3
ne saw how
the
ab11it)- to 11pgr.idt. loclcs from illarc~rl to
esclusiTe can cause a deadlock because each trdnsaction holds
a
shared
lock on the same elerneilt aiid lvarlts to upgrade the lock.
There are
t~vo broad ap1,roaches to dealing u-it11 deadlock.
\IF
car1 detect

deadlocks and fix tlle~n. or we call manage traiisactio~ls in such a way that
deadlocks are never able to form.
19.3.1
Deadlock Detection
by
Timeout
\\-hen a deadlock exists, it is genclrally iulpossible to repair the situation so tliat
all transactions involved can proceed. Thus. at least one of the traiisactio~ls \\-ill
have to he rolled back
-
al~ortcd and rcstartcd.
The silllplcsr
1t-a~
to detect ant1 resolve deadlocks is \\.it11
a
tinleo~rt.
Pllt
a limit on lion- long
zi
tr;rnsac.tio~~ may he active. and if a trilnsaction excectls
this tinle. roll it 1,ac.k.
For
csamplc. in a si~nple transaction qstcin.
IV\I<Y('
t?-pica1 transactions cxecutc ill nlillistc~ollds. a tirneout of one niiiiutc ~\-o~lltl
affect only transactions that are caught in a
deadlock.
If some transactions
are
nlore colnplcx. n-e might ~vant tlie tinieout to occur after a longer interval.

box-ever.
Sotice that nhen one transaction involved in the deadlock tirncs out. it
releases its locks or
otlic~ resources. Thus. tllercl is a chance that the other
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
transactions involved in the deadlock will complete before reaching their timeout
limits. However. since transactions involved in a deadlock are likely to have
started at approximately the
same time (or else, one would have completed
before another started), it is also possible that spurious
timeouts of transactions
that are no longer involved in a deadlock will occur.
19.3.2
The
Waits-For
Graph
Deadlocks that are caused
by
transactions waiting for locks held by another can
be addressed by a
waits-for
graph,
indicating which transactions are waiting for
locks held by another transaction. This graph can be used either to detect
deadlocks after they have formed or to prevent deadlocks from ever forming.
We shall assume the latter, which requires us to maintain the waits-for graph
at
all times, refusing to allow an action that creates a cycle in the graph.
Recall from Section 18.5.2 that a lock table maintains for each database
elenlent

X
a list of the transactions that are ~i-aiting for locks on
X,
as nell as
transactions that currently
holtl locks on
X.
The waits-for graph has a node
for each transaction that currently holds a lock or is waiting for one. There is
an arc from node (transaction) T to node
U
if there is some database elenleiit
d
such that:
1.
li
holds a lock on
A,
2.
T is waiting for
a
lock on
A,
and
3.
T
cannot get
a
lock on
A

in its desired mode unless
U
first releases its
lock on
.L3
If theie are no cycles in the waits-for graph, then each tiansactioii can
evenrually complete. There will be at least one transactiori u-aiting for no other
transaction, arid this transaction
snrely can complete. At that tlme. tlie~e will
be at least one other transaction that is not waiting, which can complete. and
FO
011.
Hon-ever. if there is
a
cycle. then no transaction in the cycle can ever make
progress. so there is a deadlock. Thus.
a
strategy for deadlock avoidance is to
roll back
any transaction that makes a request that ~vould cause a cycle in the
waits-for graph.
Example
19.13:
Suppose n-e have the following four transactions. each of
n-hich reads one element and n-rites another:
31n common sitnations,
such
as shared and exclusive locks; every waiting transacrion rvill
have to wit until
all

current lock holders release
their
locks;
but
there are examples of systems
of lock ]nodes where
a
transaction can get its lock after only some of the c~lrrent locks are
released: see Exercise
19.3.6.
\Ye
use a simple locking system \\-it11 only one lock mode, although the same
effect
nould be noted if we were to use a shared/exclusive system and took
locks in
thc
appropriate niode: sharcd for a read and exclusive for a write.
5)
12(.4):
Denied
6)
l3
(C);
Denied
7)
/4(z4);
Denied
3)
ll(B):
Denied

Figure 19.9: Beginning of a schedule mith a deadlock
In Fig. 19.9 is the beginning of a scliedule of these four transactions. In the
first
four steps. each transaction obtains
a
lock on the elenlent it wants
to
read.
It step
(3),
T2
tries to lock
.4:
but the request is denied because TI already has
a lock on
-4.
Thus:
T._,
waits for TI: and we draw an arc from the node for
T.2
to the node for
TI.
Figure 19.10: \Yaits-for graph after step (7) of Fig. 19.9
Similar1)
at
step (6)
T3
is denicd a lock on
C
because of

T2.
and at step (7).
T4
is de~iieti a lock on
.f
because of TI. The waits-for graph at this point is as
sho\\-n in Fig. 19.10. There is
110
cycle in this graph,
At step
(8).
TI
nus st
wait for the lock on
B
held by
T3.
If
\re
allon-ed TI
to
wait. then there ~ould be a cycle in the waits-for graph involving Ti. Tz, and
T3.
as suggested by Fig. 19.11. Since they are each waiting for allother to finish,
none can
iilake progress. and therefore there is a deadlock involving these three
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1012
CHAPTER
19.

310RE ABOUT
TRAArSACTION
AIA:\'ilGElIEST
4
Figure 19.11: Waits-for graph with a cycle caused by step
(8)
of Fig. 19.9
8
-
Figure 19.12: 1I'aits-for graph after TI is rolled back
transactions. Incidentally,
T4
could not finish either, although it is not in the
cycle. because
T4's progress depends on TI making progress.
Since
we roll back any transaction that would cause a cycle, then TI must
be rolled back, yielding thc waits-for graph of Fig. 19.12. TI relinquishc~s its
lock on
A,
which may be given to either T2 or
Ti.
Suppose it is given to T2.
Then
T2
can complete. n-hereupon it relinquishes its locks on .4 and
C.
Tow
T3.
which needs

a
lock on
C,
and T4, which needs a lock on 21, call both complete.
At
solne time, Tl is restarted, but it cannot get locks on .4 and
B
until T2.
T3.
and T4 have completed.
19.3.3
Deadlock Prevention
by
Ordering Elements
Sow. let us consider several more methods for deadlock prevention.
The
first
requires us to order database elements in some arbitrary but fixed order. For
instance, if database elements are blocks,
Ive could order them lexicographically
by
their physical address. Recall from Section 8.3.4 that the physical address
of a block is normally represented by a sequence of bytes describing its
locntioll
trithin the storage sl-stem.
If cvcry transaction is required to request locks on elenicnts in order
(a
con-
dition
that is not realistic in

no st
applications), then there can be no deadlock
due to transactions waiting for locks. For suppose
T2 is waiting for a lock on
I1
held by
TI; T3
is waiting for a lock on -42 held by
T2,
and so on, while
T,,
is waiting for
a
lock on An-1 held by Tn-l, and Tl is xvaiting for a lock on .4,
held by T,,. Since
2'2
11%
a
lock on -42 but is waiting for .AI,
it
nlust be that
I2
<
-41 in t'lie order of eleulents. Similarly,
<
for
i
=
3,4,.
.

.
;
n.
But
19.3.
RESOLVIA-G DEADLOCKS
1013
since
Tl
has a lock on
,A1
while it is waiting for
A,,
it also follo~s that
ill
<
A,.
\\re noly have
.Al
<
An
<
-An-1
<
.
.
.
<
-42
<

I1, which is impossible, since it
implies
A1
<
:I1.
Example
19.14:
Let us suppose elenlents are ordered alphabetically. Then
if the four transactions of
Examplel9.13 are to lock elelllents in alphabetical
order,
?il
and
T4
must be ren-ritten to lock elements in the opposite order.
Thus, the four transactions are
noxr:
Figure 19.13 shows what happens if the transactions execute ~vith the same
timing as Fig. 19.9.
TI
begins and gets
a
lock on
A.
T2
tries to begin next
by
gett~ng a lock on
-4,
but must ~vait for TI. Then. T3 begills by getting

a
lock
on
B.
but
T4
is unable to begin because it too needs
a
lock on
A,
for \vhich it
must wait
.
TI
Tl
T3
1) il(&4):rl(-4):
2
l2
(A):
Denied
3
)
13(B): r3(B):
T4
14(d);
Denied
Figure 19.13: Locking elenlentc in al~llnletical order prevents deadlock
Since
r.)

is stalled, it cannot proceed, and follo\ving the order of events in
Fig.
109.
T3
gets
a
turn next. It is able to get its lock on
C.
whereupon it
conipletes at step
(6).
Soi\ iviilr T3's locks on
B
and
C
released. TI is able
to
co~nplete. which it does at step (8). At this point. the lock on -4 becomes
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1014
CHAPTER
19.
XORE ABOUT TR.4ArSACT10i\' JIANAGEJIEYT
available, and we suppose that it is given on a
first-conie-first-served
basis to T2.
Then,
T2
can get both locks that it needs and completes at step (11). Finally.
T4

can get its locks and completes.
19.3.4
Detecting Deadlocks
by
Timestamps
\re can detect deadlocks by maintaining the waits-for graph, as we discussed
in Section
19.3.2. Ho~vever, this graph can be large, and analyzing it for cj-cles
each time a transaction has to wait for a lock can be time-co~isuming. An alter-
native to maintaining the waits-for graph is to associate with each transaction
a timestamp. This timestamp:
Is for deadlock detection only; it is not the same as the timestamp used for
concurrency control in Section
18.8,
even if
timestamp-based
concurrency
control is in use.
In
particular, if a transaction is rolled back,
it
restarts with
a
new, later
concurrency timestamp, but its timestamp for deadlock detection never
changes.
The timestamp is used when a transaction
T
has to wait for
a

lock that
is held
by
another transaction
U.
Two different things happen. depending on
whether
T
or
U
is older (has the earlier timestamp). There are two different
policies that can be used to
Inanage transactions and detect deadlocks.
1.
The Wait-Die Scheme:
(a) If T is older than
U
(i.e the timestamp of T is smaller than
L*'s
timestamp), then
T
is allo~ved to xait for the lock(s) held by
U.
(I))
If
li
is older than
T,
then T .'dies": it is rolled back.
2. The

iifound-
Wait
Scheme:
(a) If T is older than
CT,
it .'wounds"
C.
Usually. the "wound" is fatal:
C'
must roll back and relinquish to
T
the lock(s) that T needs from
U.
There is an csception
if,
by the time the "nound" takes effect.
C
has already finished and lcleased its locks. In that case.
C'
survives
and
need riot be rolled back.
(b)
If
C'
is older than
T.
then
T
waits for the lotk(s) held

by
IT
Example
19.15
:
Let us consider the wait-die schcmc. using the transactions
of
Esalnple 19.14. \Ye shall assume that T17T2:
T.$.
T4
is the order of times: i.e.:
Tl is the oldest transaction. lye also assume that ~vhen a transaction rolls back.
it does not restart soon enough to become active before the
other transactions
finish.
Figure 19.14
sho\x-s a possible sequence of events under the wait-die schcme.
TI gets the lock on .4 first. \Yhen
T2
asks for a lock on
4,
it dies; because TI
2
l?(.A); Dies
3)
13(B): r3(B):
4)
14(-4): Dies
5)
13(C): w3(C):

6)
US(B): 1~3(C);
12(=1);
Waits
12
(-4)
;
12
(c);
T.~
(C); t~'2(.4);
~1
( I)
:
tf2
(C)
;
r4
(D):
7c4(.4);
T14(*4): 11.,(D);
Figure 19.14: .Ictions of transactions detecting deadlock under the wait-(lie
schenie
TI
T2
T3
T4
1) 11(-4): rl(-4):
2)
l2 (A);

Waits
3) 13(B): r~(B1;
1
\
l4 (-4):
Waits
-/
5) l1 (B):
(B):
Wounded
6)
TL
(-4)
:
u
1
(B):
7)
1*(.4): 12 (C):
8)
r2
(C):
lC2
(-4);
u2(:l):
112
(C):
14 (-4): 1, (Dl:
r4
(D):

11'~
(-4):
u4(-4):
u,(D):
Ii(B):
r<(D):
I:(C):
u.i(C):
11,3(B):
~(c):
Figure 19.15: Actions of transactions detecting ticadlock tnldcr the I\-ound-wait
sclle~ile
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1016
CH.4PTER
19.
JIORE ABOUT TR=II\~SACTION AIIAh'AGEIIEYT
Why
Timestamp-Based Deadlock Detection Works
We claim that in either the wait-die or wound-wait scheme, there can be
no cycle in the waits-for graph, and hence no dcadlock. Suppose other-
wise; that is. there is a cycle such as
TI
-+
T2
-+
T3
-+
TI. One of the
transactions is the oldest, say

T?.
In the wait-dic scheme, you can only wait for younger transactions.
Thus, it is not possible that
TI is waiting for
TI,
since T2 is surely older
than
TI. In the wound-wait scheme, you can only wait for older transac-
tions. Thus, there is no
way
Tl
could be 11-aiting for the younger T3. \Ye
conclude that the cycle cannot exist, and therefore there is no deadlock.
is older than
T2. In step
(3),
T3 gets a lock on
B,
but in step
(4).
T4
asks for
.
a loclc on
d
and dies because TI, the holder of the lock on
A,
is older than T4.
Sext, T3 gets its lock on
C

and conlpletes. n'hen Tl continues, it finds the lock
on
B
available and also completes at step
(8).
Sow, the two transactions that rolled back
-
T2
and T4
-
start again.
-
Their timestamps as far
as
deadlock is concerned, do not change: T2 is still
older than
T4. Honever, XT-e assume that T4 restarts first, at step (9). and when
the older transaction
T.L
requests a lock on
I
at step (lo), it is forced to n-ait.
but does not abort.
Ti
completes at step (12), and then
TI
is allov-ed to run to
completion, as
slion-n in the last three steps.
Example 19.16: Sext, let us consider the same transactions running urlder

the 11-ound-wait policy, as shown in Fig. 19.15. As in Fig. 19.14, Tl begins by
locking
I.
When
T2
requests a lock on
I
at step
(2);
it waits, since Tl is older
than
T2. After
T3
gets its lock on
B
at step
(3),
T4
is also made to wait for the
lock on
.a.
Then, suppose that TI cont,inues at step (5) with its request for the lock on
B.
That lock is already held by T3; but Tl is older than T3. Thus,
TI
.'wounds'-
T3. Since
T3
is riot yet finished, the rvound is fatal: T3 relinquishes its lock and
rolls back. Thus; TI is able to complete.

\\:hen Tl makes the lock on
.1
available, suppose it is given to
T2.
n-hich
is thcn al~le to procccd. After
T2,
the lock is given to
T4:
which proceeds
to
coniplction. Finally. T3 restarts and co~llpletcs ~vithout interference.
19.3.5
Comparison of Deadlock-Management Met hods
In both the nait-die and n-ound-wait schc~n~es, older transactions kill off newer
transactions. Since tra~isactions restart ivith their old timestamp. eventually
each trallsaction becomes the oldest In tlie system and is sure to complete. This
guarantee. that
every transaction eventually
completes.
is called
no
starvat~orl
Xotice that otllcr schcnles described in this scction do not necessarily prevent
19.3. RESOLVISG DEADLOCKS
1017
starvation; if extra measures are not taken, a transaction could repeatedly start,
get
in\-olved in a deadlock, and be rolled back. See Exercise 19.3.7.
There is, however, a subtle difference in the way wait-die and wound-wait be-

have. In
wound-nait, a newer transaction is killed whenever an old transaction
asks for a lock held by the newer transaction. If
\ve assume that transactions
take their locks
near the time that they begin, it will be rare that an old trans-
action
was beaten to a lock by a new transaction. Thus, we expect rollback to
be rare in
wound-wait.
On the other hand, when a rollback does occur, wait-die rolls back
a
trans-
action that is still in the stage of gathering locks, presumably the earliest phase
of
the transaction. Thus, although wait-die Inay roll back more transactions
than n-ound-xait, these transactions tend to have done little work. In contrast,
when ~vound-~i-ait does roll back
a
transaction, it is likely to have acquired its
locks and for substantial processor time to have been invested in its activity.
Thus. either scheme
may turn out to cause more wasted work, depending on
the population of transactions processed.
We sliould also consider the advantages and disadvantages of both
wound-
n-ait and wait-die xhen compared with a straightfor\vard construction and use
of the waits-for graph. The important
poi~lts are:
Both wound-wait and wait-die are easier to implement than

a
system
that maintains or periodically constructs the
waits-for graph. The disad-
vantage of constructing the waits-for graph is even more extreme when
the database is distributed. and the
naits-for graph must be constructed
from a collection of lock tables at different sites. See Section 19.6 for
a
discussion.
Lsing the waits-for
minimizes the number of times
we must abort
a transaction because of deadlock.
fi
never abort a transaction unless
there really is a deadlock. On the other hand. either wound-wait or wait-
die will solnetimes roll back a transaction when there a-as no deadlock.
and no deadlock
11-ould have occurred had the transaction been allo~ved
to survive.
19.3.6
Exercises for Section
19.3
Exercise 19.3.1: For each of the sequences of actions belorv. assume that
shared locks are requested immediately
hcfore each read action. and exclusive
locks are
lequested immediately heforc every \\-rite action. .ilso, unlocks occur
imnlediately after the

filial
action that
a
transaction executes. Tell what actions
are denied, and
nhether deadlock occurs. Also tell holv tlie waits-for graph
evolves
during the executioll of the actions. If there are deadlocks, pick a
transaction to abort, and show
how the sequence of actions continues.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1018
CHAPTER
19.
IWORE ABOUT TRANSACTION MANAGE-\IEATT
Exercise
19.3.2
:
For each of the action sequences in Exercise 19.3.1, tell n-hat
happens under the wound-wait deadlock avoidance system. .Assume the order of
deadlock-timestamps is the same as the order of subscripts for
the transactions,
that is,
Tl,T2, T3,T4. Also assume that transactions that need to restart do so
in the order that they
were rolled back.
Exercise
19.3.3
:
For each of the action sequences in Exercise 19.3.1, tell

what happens under the wait-die deadlock avoidance system. Make the same
assumptions as in Exercise 19.3.2.
!
Exercise
19.3.4:
Can one have a waits-for graph with a cycle of length
n,
but
no smaller cycle, for any integer
n
>
l? What about
n
=
1, i.e., a loop on a
node?
!!
Exercise
19.3.5
:
One approach to avoiding deadlocks is to require each trans-
action to announce all the locks it wants at the beginning, and to either grant
all those locks or deny them all and make the transaction wait. Does this ap-
proach
avoid deadlocks due to locking? Either explain why, or give an example
of
a
deadlock that can arise.
!
Exercise

19.3.6:
Consider the intention-locking system of Section 18.6. De-
scribe how to construct the waits-for graph for this system of lock modes. Espe-
cially, consider the possibility that a database element
A
is locked by different
transactions in modes
IS
and also either
S
or
Ix.
If a request for a lock on
'1.
has to wait, what arcs do we draw?
*!
Exercise
19.3.7:
In Section 19.3.5 we pointed out that deadlock-detection
methods other than wound-wait and wait-die do not necessarily prevent star-
vation, where a transaction is repeatedly rolled back and never gets to finish.
Give an example of how using the policy of rolling back any transaction that
~vould cause a cycle can lead to starvation. Does requiring that transactions
request locks on elements in a fixed order necessarily prevent starvation?
\That
about timeouts as a deadlock-resolution mechanism?
19.4
Distributed Databases
We shall now consider the elements of distributed database systems. In a dis-
tributed system, there are many, relatively autonomous processors that may

participate in database operations. Distributed databases offer several oppor-
tunities:
1. Since many machines can be brought to bear on a problem, the opportu-
nities for
parallelisn~ and speedy response to queries are increased.
19.4.
DISTRIBUTED DATABASES 1019
2.
Since data may be replicated at several sites, the system may not hare to
stop processing just because one site or component has failed.
On the other hand, distributed processing increases the complexity of every
aspect of a database system, so we need to rethink how even the most basic
components of a
DBMS are designed. In many distributed environments, the
cost of communicating may dominate the cost of processing, so a critical issue
becomes how many messages are sent. In this section
we shall introduce the
principal issues,
while the next sections concentrate on solutions to two impor-
tant problems that come up in distributed databases: distributed commit and
distributed locking.
19.4.1
Distribution of Data
One important reason to distribute data is that the organization is itself dis-
tributed among many sites, and the sites each have data that is germane pri-
marily to that site. Some examples are:
1.
A
bank may have many branches. Each branch (or the group of branches
in a given city) will keep a database of accounts maintained at that branch

(or city). Customers can choose to bank at any branch, but will normally
bank at "their" branch, where their account data is stored. The bank
may also have data that is kept in the central office, such as employee
records and policies such as current interest rates. Of course, a backup of
the records at each branch is also stored, probably in a site that is neither
a branch office nor the central office.
2.
A
chain of department stores may have many individual stores. Each
store (or a group of stores in one city) has a database of sales at that
store and inventory at that store.
There may also be a central office
with data about employees, chain-wide inventory, credit-card customers,
and information about suppliers such as unfilled orders. and what each
is owed. In addition. there may be a copy of all the stores' sales data in
a "data warehouse."
which is used to analyze and predict sales through
ad-hoc queries issued by analysts: see Section 20.4.
3.
A
digital library may consist of a consortium of universities that each hold
on-line books
and other documents. Search at any site xvill examine the
catalog of documents available at all sites
and deliver an electronic copy
of the document to the user if
any site holds it.
In some cases,
what we might think of logically as a single relation has
been partitioned among many sites. For example, the chain of stores

might be
imagined to have a single sales relation, such as
Sales(itern, date, price, purchaser)
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
I
Factors in Communication Cost
I
.As band~idth cost drops rapidly. one might wonder whether communi-
cation cost needs to be considered
when designing a distributed database
system.
Sow ce~tain kinds of data are among the largest objects managed
electronically, so even with
very cheap communicatioil the cost of sending
a terabyte-sized piece of data
caniiot be ignored. Ho~vevcr, comlnunication
cost generally involves not only the shipping of the bits, but several layers
of protocol
that prepare the data for shipping, reconstit~~te them at the
receiving end, and manage the communication. These protocols each re-
quire substantial computation. While computation is also getting cheaper,
the
con~putation needed to perform the communication is likely to remain
significant, coinpared to the needs for conventional, single-processor exe-
cution of key database operations.
However, this relation does not exist physically. Rather. it is the union of a
number of relations with the same schema, one at each of the stores in the
chain. These local relations are called fragments, and the partitioning of a
logical relation into physical fragments is called
Aorzzontal decomposztion of

the relation
Sales.
We regard the partition as "horizontal" because we ma?;
visualize a single
Sales
relation with its tuples separated. by horizontal lines.
into the sets of tuples at
each store.
In other situations, a distributed database appears to have
partitioned a
relation
"r~erticall~;" by decomposing ~vhat niight be one logical relatiori into
two or more, each with a subset of the attributes, and with each relation at a
different site. For instance. if
lye want to find out which sales at the Boston store
\(-ere made to customers who are more than 90 days in arrears on their credit-
card payments, it \%-ould be useful to have a relation (or view) that included the
item. date, and purchaser
info~mation from
Sales.
alorig with the date of the
last credit-card payment by that purchaser. Howel-er, in the scenario we are
describing, this relation is decomposed vertically, and
\ye ~vould have to join the
credit-card-custorner relation at the central headquarters
with the fragment of
Sales
at the Boston store.
19.4.2
Distributed Transactions

.I
conscqucrice of the tlistribution of data is that a transaction Inay involve pro-
cesses at several sites. Thus. our
lnodel of what a transaction is must change.
So
longer is a transaction a piece of code executed by
a
single processor conl-
municating with a single scheduler and a single log manager at a single site.
Rather. a
transaction consists of conimunicating transactzon components. each
at a different site and communicating
with the local scheduler and logger Two
important issues that must thus be looked at anelr. arc:
1.
How do ne manage the comniit/abort decision when
a
transaction is dis-
tributed?
Khat happens if one component of the transaction wal1tS to
abort the ivhole transaction, ~yhile others encountered no problem and
lyant to commit:' jve discuss a technique called two-phase commit" in
Section
19.5:
it allors the decision to he made properly and also frequently
allows sites that
ale up to operate even if son~e other site(s) have failed.
2.
How do ne assure serializability of transactions that involve components
at several sites'?

\fi
look at locking in particulal, in Section
186
and
see how local lock tables can be used to support global locks on database
r.lenlmts and thus support serialirab~lity of transactions in a distributed
environment.
19.4.3
Data Replication
Oire important advantage of a distr~buted system is the ability to replicate data,
that is. to make copier of the data at diffeiellt sites. One slotivation is that if a
site
fails, there may be other sites that can provide the same data that as at
tlie failed site.
h
second use is ill inlpmving the speed of query answrilrg by
makillg a copy
of
needed data available at tlie sites where queries are initiated.
For
example:
1.
\
bank may lllake copies of current interest-rate policy arrilable at eacll
branch. so a qucry about rates does not have to lie sent to the central
office.
2.
\
chain store may keep copic~f infolmation about soppliers at each
store. so local

rcqucsts for infornlatioll about suppliers (e.g thr ma~lnger
needs the phone nu~i~ber of a si~pplier to cliecl; on a slliplne~lt)
CBI
be
handled
11-ithout scndillg messages to the ccntral office.
3
I
digital library may temporarily cache a copy of a poplilar document at
a
school ~vlicre students haye bee11 assigned to read tlie docunlent.
Holve\er. there are
problems tlrat most bc
faced
ahen data
is
repli-
cated.
a) HoXv do
w
keep copies identical?
111
nsmce. an update to a replicated
data elemel?t heconles a distri1,utc.d transaction that updates all copics.
b) Holy do lye
decide
\illprc and llcjii illany copies to kerp'? The siori. cnl~i's.
the Illore effort is rc<lllircd
to
pil lilt^.

11~t tlic casirr qurrics ~>ECOI~IC. For
exalllple. a r~]atioll flint is rarely opdatcd nright have copies crrryhllcre
for lilaxinlrim efficiency. ivhile a frecl~icntly updated relation might have
only one or t~o copies.
C)
1Yh.t happals "hen there is a cornnliillication failure in the netivork and
different copies of the same tlstir have the o~portimity to evolve separately
and
must then be reconciled den the netur-ork reconnects?
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1022
CmPTER
19.
.\IORE ABOUT TR.4NS.4CTIOAV .V~\I~~~V-~GE-~\IE-\~T
19.
j.
DISTRIBUTED
CG-'\I\IrT
19.4.4
Distributed Query Optimization
19.5
Distributed Commit
Tlie existence of distributed data also affects the complexity and options avail-
able in the design of a physical query plan (see Section
16.7).
Among the issues
that must be decided as
we choose a physical plan are:
1.
If there are several copies of a needed relation

R,
from which do rye get
the
valuc of
R?
2.
If we apply an operator, say join, to two relations R and
S,
n-e have
several options and must choose one.
Some of the possibilities are:
(a) We
can copy
S
to the site of R ant1 do tlie colnputation there.
(b)
We can copy R to the site of
S
and do the computation there.
(c)
117e can copy both
R
and
S
to a third site and do the conlputation
at that site.
Which is best depends on several factors, including which site has available
processing cycles.
and whether the result of the operation will be combined
with data at

a
third site. For example. if we are computing (R
w
S)
w
T.
Tve may choose to ship both R and
S
to the site of
T
and take both joins
there.
If a relation
R
is in fragments
R1,
R2,
. .
.
,
R,
distributed among several
sites,
11-e should also replace a use of
R
in the query by
a
use of
RI
U

R2
U
.
.
.
U
R,,
as
we seiect a logical query plan. The query may then allow us to simplify the
expression significantly. For instance, if the
R,'s each represent fragments of
the
Sales
relation discussed in Section
19.4.1,
arid each fragment is associated
with a single store, then a query about sales at the Boston store might allon-
us to leniove all R,'s except the fragment for Boston from the union.
19.4.5
Exercises for Section
19.4
*!!
Exercise 19.4.1: The following exercise ~vill allow you to address sonie of
the
problcrns that come
up
when deciding
011
a replication strategy for data.
Suppose there is a relation

R
that is accessed from
n
sites. Tlie it11 site issncs
qi
queries about
R
and
7li
updates to
R
pcr second. for
i
=
1.2:.
. .
.n.
Thc
sost of executing
a
query if there is
a
copy of
R
at the site issuing the cluerj- is
c,
wliile if tlierc is no copy there, and the query must be sent to some remote
site: then the cost is
10c.
The cost of esecuting an update is

d
for the copy of
R
at the issuing site and
10d
for every copy of R that is not at the issuing site.
.is a fij~lction of these parameters, how ~rould j-ou choose. for large
;en:
a set of
sites at
~vliich to replicate
R.
In this section, n.e shall address the ~roblem of holv
a
distributed transaction
that
has components at several sites can execute atomically. The next section
discusses
another important property of distributed transactions: executing
them
serializably. lie shall begin with an example that illustrates the problenis
that might arise.
Example 19.17
:
Consider our example of a chain of stores mentioned in Sec-
tion
19.4.
Suppose a manager of the chain wants to query all the stores, find the
ii~ventory of toothbrushes at cach, and issue instructions to move toothbrushes
from store io store in order to balance the inventory. The operation is done

by a single global transaction
T
that has cornpoilent
T,
at the ith store and
a
coniponent
To
at the office where the manager is located. The sequellce of
activities performed hy
T
are summarized belolv:
1.
Corilponellt
To
is created at tlie site of the nlanager.
2.
To swds messages to all the stores instructing them to create components
TI.
3.
Each
T,
executes a queq at store
i
to discover the number of toothbrushes
in
ill\-entory and reports this ~iu~nbcr to
To.
1.
To

takes these nuinhers and deterlni~les, by some algorithln we shall not
discuss.
\\-hat d~ipmcnts of tootht)rushci are desired. To then sends mcs-
sages such as -store
10
should ship
.500
toothblushes to store
7"
to the
appiopliate stores (~tores
7
and
10
in this instance).
3.
Stores receiving instructions update their inventory and perfor111 the ship-
ment s.
19.5.1
Supporting Distributed Atomicity
There are a nulnher of things that could go wong in Example
19.17,
and many
of these result
in violations of the atomicity of
T.
That is, some of the actions
comprising
T
get executed. b~~t otli~rs do not. SIechanisms such as logging and

recovery.
~vhi,.h n-c assume arc prespnt at each site, ~vill assure that each
Ti
is
csecuted atomicail?. but do not asslirc that
T
itself is atomic.
Example
19.18
:
Suppose a b11g in rhc algorithnl to
redistribute
tootlibrushes
migilt cause store
10
to be instructed to ship more
toothbrushes
than it has.
Ti0
~vill therefore abort. and no tootlibrushcs \<-ill be shipped from store
10;
neither
will the in\-entory at store
10
be changed. Ho~vever.
T7
detects no problems
and commits at
<tore
7.

updating its in\-cntory to reflect the supposedly shipped
toothbrushes.
?;ow. not only has
T
failed to execute aton~ically (since Tlo never
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1024
CHAPTER
19.
MORE ABOUT TRA.SSACTION S4AN.4GEAIEST
completes), but it has left the distributed database in an inconsistent state: the
toothbrush inventory does not equal
tllc number of toothbrushes on hand.
Another source of problems is the possibility that a site will fail or be dis-
connected
from the network wh~le the distributed transaction is running.
Example
19.19:
Suppose
Tlo
replies to
To's
first message by telling its inven-
tory of toothbrushes.
Ho\vever; the machine at store 10 then crashes, and the
instructions
from
To
are never received by
Tlo.

Can distributed transaction
T
ever commit? What should
TIo
do when its site recovers?
19.5.2
Two-Phase
Commit
In order to avoid the problems suggested in Section
19.5.1,
distributed DBMS's
use a complex protocol for deciding whether or not to commit a distributed
transaction. In this section,
\re shall describe the basic idea behind these pro-
tocols, called
two-phase commit
By making a global decision about commit-
ting, each
compo~ient of the transaction will commit, or none will. -4s usual.
~ve assume that the atomicity mechanisms at each site assure that either the
local component commits or it has no effect
on the database state at that site:
i.e., components of the transaction are atomic. Thus, by enforcing the rule
that either all components of a distributed transaction commit or none does.
we make the distributed transaction itself atomic.
Several salient points about the
trvo-phase commit protocol folloxv:
In a two-phase commit, we assume that each site logs actions at that site.
but there is no global log.
\Ye also assume that one site, called the

coordznator,
plays a special role
in deciding whether or not the distributed transaction can commit. For
example. the coordinator might be the site at which the transaction orig-
inates, such as the site of
To
in the esalnples
of
Scction
19.5
1.
The two-phase commit protocol involves sending certain ~nessagcs be-
tween the coordinator and the other
sites. .Is each message is sent, it is
logged at the sending site, to aid in
Iecovery should it be necessary.
Kith these points in mind, n.c can describe the two phases in terms of the
messages sent between sites.
Phase
I
In phase
1
of the two-phase commit. the coordinator for a distributed trans-
action
T
decides when to attempt to connnit
T.
Presumably the attempt to
commit occurs after the component of
T

at the coordinator site is ready to
"0
not confuse tao-phase commit tlith tno-phase locking. They are independent ideas.
designed
to
solve different problems.
19.5.
DISTRIBUTED COAIALIT
1025
commit, but in principle the steps must be carried out even if the coordinator's
component
lvants to abort (but mith obvio~s simplifications as rve shall see).
The coordinator polls all the sites mith
compollelits of the transaction
T
to
determine their wishes regarding the
commit/abort decision.
1.
The coordinator places a log record <Prepare
T>
on the log at its site.
2. The coordinator sends to each component's site (in principle including
itself) the message prepare
T.
3.
Each site receiving the message prepare
T
decides whether to commit or
abort its component of

T.
The site can delay if the component has not
yet completed its activity, but must eventually send a response.
4.
If a site wants to commit its component, it must enter a state called
precommitted.
Once in the precommitted state, the site cannot abort its
component of
T
without a directive to do so from the coordinator. The
following steps are done to become precommitted:
(a) Perform
whatever steps are necessary to be sure the local component
of
T
\$-ill not have to abort, even if there is a system failure follo~ved
by recovery at the site. Thus. not only must all actions associated
~vith the local
T
be performed. but the appropriate actions regarding
the log must be taken so that
T
will be redone rather than undone
in a recover): The actions depend on the logging method, but surely
the log records associated
\\-it11 nctions of the local
T
must be flushed
to disk.
(b) Place the record <Ready

T>
on the local log and flush the log to
disk.
(c) Send to the coordinator the message ready
T.
However. the site does not commit its component of
T
at this time; it
must ~~ait for phdae
2.
3.
If; instead, the site Ivants to abort its component of
T:
then it logs the
record <Don't commit
T>
and sends the message don't commit
T
to
the coordinator. It is safe to abort the component at this time, since
T
xvill surely abort if even one cornpontnt wants to abort.
The messages of phase
1
are suxmnarizcd in Fig.
19.16.
Phase
I1
The second phase begins ~vlien responses ready or don't commit are receixed
from each site by the coordinator. However. it is possible that some site falls to

respond: it
may be down. or it has been disconnected by the network.
1x1
that
case. after a suitable
timeout period. the coordinator tvill treat the site
as
if it
had sent don't commit.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1026
C'H.'tPTER
19.
JIORE
ABOUT TR.41WCTIOA\T
di'A-i-YAGEIIE-\rT
prepare
f/O
ready or
O~O(H
don't commit
Figure 19.16: Messages in phase
1
of two-phase colnnlit
1.
If the coordinator has received
ready
T
from all components of
T1

then
it decides to commit
T.
The coordinator
(a) Logs
<Commit
T>
at its site, and
(b) Sends message
commit
T
to all sites involved in
T.
2.
If the coordinator has received
don't commit
T
from one or more sites,
'
it:
(a) Logs
<Abort
T>
at its site, and
(b) Sends
abort
T
messages to all sites involved in
T
3.

If a site receives a
commit
T
message. it commits the component of
T
at
that site, logging
<Commit
T>
as it does.
4.
If a site receives the message
abort
T,
it aborts
T
and writes the log
record
<Abort
T>.
The messages of phase 2 are summarized in Fig. 19.17.
commit
or
Coordinator
0
Figure
19.17:
1Icssages in phase
2
of two-phase corn~nit

19.5.3
Recovery of Distributed Trallsactions
.It any time during the two-phase commit process, a site may fail. \Ye need
to make sure that what happens when the site recovers is consistent ~vith the
global decision that
was made about a distributed trdnsaction
T.
There are
several cases to consider: depending on the last log
entiy for
T.
19.5.
DISTRIBUTED COJOIIT
1027
1.
If the last log record for
T
was
<Commit
T>,
then
T
must have been
committed bv the coordinator. Depending
on the log nletl~od used,
it
1
-
may bc necessary to redo the component of
T

at the recovering site.
I
2.
If the last log record is
<Abort
T>.
then sinlilarly we kno~v that the
global decision
was to abort
T.
If the log method requires it. we undo the
component of
T
at the recolering site.
3.
If the last log record is
<Don't commlt
T>,
then the site knon-s that tllc
global decision must have been to abort
T.
If necessary. effects of
T
on
the local database arc undone.
4.
The hard case is when the last log record for
T
is
<Ready

T>.
Sow, the
recovering site does
not know 13-liether the global decision was to conimit
or abort
T.
This site must coinlnunlcate wit11 at least one other site to
find out the global decision for T. If the coordinator is up,
the site call
ask the coordinator. If the coordinator is not up at this time. some otller
site may be asked to consult its log to find out what happcncd to
T.
In
the \Torst case. no other site can be contacted. and the local cornpollent
of
T
must be
kept
active until the cornmit/abort decision is deterrninecl.
3.
It may also be the case that tlle local log lias no records about
T
tllat
conle from
actions of tlle tlvo-phase commit protocol. If so, then the
recovering
site may unilaterally decide to abort its component of
T:
~vhich
is consistent n.ith all logging nlethods. It is ~~ossiblc that tl~c coorclinator

already detected
a
timeout from the failetl site ant1 decitfcd to abort
T.
If
the failure \vas brief:
T
may still be active at other sites. but it ~vill never
be inconsistent if
the recovering site decides to abort its colliponent of
T
and responds \\-it11
don't commit
T
if later polled in phasc 1.
The above analysis assumes that tlic failed site is not the coortiinator. IVhcll
the coordinator fails during a two-phase commit, nc~v problems arise. First, the
survivilig participant sites niust either \T-ait for the coordinator to recover or
elect a
new coordinator. Since the coordi~lator co~tld
be
dolvn for an indefinite
period. there is good
nlotivation to elect a nexv leader: at least after
a
brief
~vaiting period to see if the coordinator conies hack up.
The
matter
of

lender
election
is in its on.11 right a cornples problrl~~ of dis-
tributed
systems. beyond the scol~c
of
this l~ooli. Hon-cvcr. a si~nplt> tncthod will
work in most situations. For instance. n-e ilia\- assume that all participallt sitc,s
hav~ uniqnr idcntif\-ing nl~rnbcrs:
IP
at1tlrci;scs n-ill n-ork in ninny sitllatiol~s.
Each participant sends nlessages almou~lcil~g its a~ailahility as 1e;idcr to ;ill thr'
other sites. pil-ing its identifying nunlbrr. After
a
suitable length of time. each
participant
ackno~vledges as the neu- coordirlator tlle lowest-n~lnlbered site from
nhicli it has Ileal-d. and sends messages to that effect to all the otllcr sites. If
all sites receive consistent messages: then there is a unique choice for
new coor-
dinator. and everyone
kao\vs about it.
If
there is i~iconsistellcy. or a s~lrrivillg
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1028
CHAPTER 19. AIORE ABOUT TRAIVS,~CTION -\I.;li\' IGEI\fE-\-T
sitc has failed to respond, that too will be universally kno~vn, and the election
stalts oler.
Now, the new leader polls the sites for information about each distributed

transaction
T.
Each site reports the last record on its log concerning
T,
if there
is one.
Tlle possible cases are:
1.
Some site has
<Commit
T>
on its log. Then the original coordinator
must have
~vanted to send
commit
T
messages everywhere, and it is safe
to commit
T.
2.
Similarly, if some site has
<Abort
T>
on its log, then the original coordi-
nator must have decided to abort
T,
and it is safe for the new coordinator
to order that action.
3.
Suppose now that no site has

<Commit
T>
or
<Abort
T>
on its log, but
at least one site does
not
have
<Ready
T>
on its log. Then since actions
.
are logged before the corresponding messages are sent, we know that the
old coordinator never received
ready
T
from this site and therefore could
not have decided to commit. It is safe for the
neTv coordinator to decide
to abort
T.
4.
The hard case is when there is no
<Commit
T>
or
<Abort
T>
to be

found, hut every surviving site has
<Ready
T>.
Sow, we cannot be sure
whether the old coordinator fo~und sonle reason to abort
T
or not; it could
have decided to do so because of actions at its oxvn site, or because of a
don't commit
T
message from another failed site, for example. Or the
old coordinator may hax decided to commit
T
and already conimitted
its local conlponelit of
T.
Thns, the nen- coordinator is not able to decide
xvhether to comniit or abort
T
and must wait until the original coordina-
tor recovers.
111 real systems, the database administrator has the ability
to intervene and manually force the waiting transaction
comporielits to
finish. The result is a
possi1)Ic loss of atomicity, but the person executing
the blocked transaction
will be notified to t,ake soille appropriate compen-
sating action.
19.5.4

Exercises for Section
19.5
!
Exercise
19.5.1:
Consider a transaction
T
initiated at a home computer that
a~ks bank
B
to transfer $10.000 from an acrount at
B
to an account at anothel
I~ank
C.
*
a) \That are the colnponents of distributed transactio11
T?
\That should tlie
conlponents at
B
and
C
do?
b)
\Vllat can go lvrong if there is not $10.000 in the account at
B?
c)
\That can go wrong if one or both banks'
computers

crash, or if the
netxvork is disconnected?
a.
19.6. DISTRIBUTED
L
OCI<IArG
1029
d) If one of the problems suggested in (c) occurs, how could the transaction
resume correctly
when the computers and network resume operation?
Exercise
19.5.2
:
In this exercise, n-e need a notation for describing sequences
of messages that can take place during a two-phase commit. Let
(i,
j,
3f)
mean
that site
i
sends the message
,If
to site
j,
where the value of
AI
and its meaning
can be
P

(prepare).
R
(ready),
D
(don't commit),
C
(commit), or
A
(abort).
We shall discuss a simple situation in which site
0
is the coordinator, but not
other:\-ise part of the transaction, and sites
1
and
2
are the components. For
instance, the following is one possible sequence of messages that could take
place during a successful commit of the transaction:
*
a) Give an example of
a
sequence of messages that could occur if site
1
wants
to commit and site
2
xvants to abort.
*!
b) How Inany possible sequences of messages such as the above are there, if

the transaction successfully commits?
!
c)
If site
1
wants to commit, but site
2
does not, how many sequences of
messages are there, assuming no failures occur?
!
d) If sitc
1
wants to commit. but site
2
is down and does not respond to
messages,
how many sequences are there?
!!
Exercise
19.5.3:
Csing the notation of Esercise
19.5.2,
suppose the sites are
coordiliator and
n
other sites that are the transaction components. As a
function of
n.
how many sequences of messages are there if the transaction
successfully commits'?

19.6
Distributed
Locking
In this section
we
shall see how to extend a locking scheduler to an environment
where transactions are distributed and consist of components at several sites.
n'e assume that lock tables are managed by individual sites, and that the
component of a transaction at a site
can only request a lock on the data elements
at that site.
I\'hen data is leplicated.
nc
must arrange that the copies of a single ele-
ment
S
are changed in the same n-a?
b!.
each transaction. This r~quircment
introduces a tlistinctioll betn-een locking the loy~cal database element
S
and
locking one or more of the copies
of
S.
In this section, lve shall offer
a
cost
model for distributed locking algorithms that applies to both replicated and
nonreplicated data.

However, before introducing the model, let us consider an
obvious (and
someti~nes adequate) solution to the problem of maintaining locks
in a distributed database
-
centralized locking.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.