Information Technology Reference
In-Depth Information
When the programs are run serially, one by one, on an initially consistent database
r.
X
;V/, the transactions generated are of the forms:
T
1
D
BW
Œx;
u
; 1W Œy;
u
;1C
T
2
D
BW
Œx;
v
; 2W Œy;
v
;2C,
where
v
D
1 if T
1
is run first and
u
D
2 if T
2
is run first. Both transactions are
logically consistent with respect to the integrity constraint: both transactions retain
the consistency of the logical database. Hence both serial histories T
1
T
2
and T
2
T
1
keep the database consistent.
However, it is possible that the following nonserial history is generated:
T
1
:
BW
Œx;0;1
WŒy;2;1C
T
2
:
BW
Œx;1;2WŒy;0;2C
This history can be run on all databases that include tuples .x; 0/ and .y; 0/,and
will produce a database where the tuples with keys x and y are .x; 2/ and .y; 1/.
Thus, this history breaks the integrity of the database. The action W Œx; 1; 2 by T
2
is a dirty write, and this is the only isolation anomaly in this history.
t
The next two examples show how a dirty read can break the consistency of a
database.
Example 5.17
An integrity constraint states that the database must have tuples with
keys x and y, and the values in both tuples must be positive. When run serially on a
consistent database r.
X
;V/, the programs
exec sql update
r
set
V
D
0
where
X
D
x;
exec sql update
r
set
V
D
1
where
X
D
x;
exec sql commit
and
exec sql select
V
into
:w
from
r where X
D
x;
exec sql update
r
set
V
D
:w
where
Y
D
y;
exec sql commit
generate two logically consistent transactions:
T
1
D
BW
Œx;
u
;0WŒx;0;1C;
T
2
D
BR
Œx;
w
W Œy;
v
;
w
C .
On the other hand, the nonserial history
T
1
:
BW
Œx;1;0
WŒx;0;1C
T
2
:
BR
Œx; 0W Œy; 1; 0C
is also possible; this history can be run on every database that includes tuples .x; 1/
and .y; 1/ and produces a database where the tuples with keys x and y are .x; 1/
and .y; 0/. Thus, the history breaks the integrity of the database. The only isolation
anomaly in the history is the dirty read RŒx; 0 by T
2
. Transaction T
2
reads a value
that is not yet a final, committed value and uses it to update the database.
t