Information Technology Reference
In-Depth Information
9.5
Cursor Stability
Requiring serializability (isolation level 4
ı
), or preventing all isolation anomalies,
does not always allow for enough concurrency. A transaction T that produces a
report of a major part of the database will prevent concurrent writes to all tuples
of the database that T has already processed: T will acquire S locks on the keys x
of the tuples .x;
v
/ that it reads, and the locking protocol requires that all the locks
are kept until T commits. Another transaction T
0
can read or delete the tuples that
T has read, or insert new tuples in between them, only after T has committed or
completed rollback.
However, the transaction T reads each tuple only once and does not update
the database, and thus, allowing unrepeatable reads might perhaps not cause great
harm. The isolation level of transaction T could be dropped down to 2
ı
(or read-
committed level) by changing the read locks in T to short-duration locks. In this
case, however, it is possible that the report produced by T does not represent a
transaction-consistent state of the database.
Consider the following special case of an unrepeatable read. A
lost update
happens when a transaction T
1
does not observe an update performed by another
transaction T
2
. More specifically, T
1
reads a key range, after which T
2
updates a
tuple with key x in this range and commits. Then T
1
also updates x:
H
D
:::R
1
Œy;
z
:::o
2
Œx:::C
2
:::o
1
Œx:::
Here o
1
Œx and o
2
Œx are updates (inserts, deletes, or writes) on key x,andy
x
z
. Note that transaction T
1
may base its update on the result of the read action
R
1
Œy;
z
. Such a lost update is possible if the S lock acquired on key y for the
action R
1
Œy;
z
is of short duration. After the S lock is released at the end of the
action, T
2
may get X locks on key x and its next key for its action o
2
Œx.
Example 9.6
Consider the following transactions on a banking database:
T
1
D
BR
Œx;
v
1
W Œx;
u
1
;
v
1
100C .
T
2
D
BR
Œx;
v
2
W Œx;
u
2
;
v
2
200C .
Transaction T
1
can be interpreted as “withdraw 100 euro from account x”and
transaction T
2
as “withdraw 200 euro from account x.”
The following history can be run on every database that contains the tuple .x;
v
/:
T
1
:
BR
Œx;
v
W Œx;
v
200;
v
100C
T
2
:
BR
Œx;
v
W Œx;
v
;
v
200C
The only isolation anomaly present in this history is the unrepeatable read RŒx;
v
by
T
1
. This history becomes possible if the S lock on key x acquired by T
1
for the read
action RŒx;
v
is released immediately after performing the action. After this history,
the balance for account x will be
v
100,where
v
is the original balance. Transaction
T
1
based its update on an old balance, and the update by T
2
was overwritten.
t
How can we be sure that the update will always be based on the fresh value of
the tuple .x;
v
/? The solution is to read .x;
v
/ and keep the S lock on key x until it
