Information Technology Reference
In-Depth Information
Example 12.8
Consider again the banking-database transactions
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
(“withdraw 100/200 euro from account x”) of Example
9.6
. The history
T
1
:
BR
Œx;
v
W Œx;
v
200;
v
100C
T
2
:
BR
Œx;
v
W Œx;
v
;
v
200C
is possible at statement-level read consistency when the read action is generated
from a
select
query and the write action from a separate
update
statement.
In the above history, the read and write actions of both transactions operate
on the most recent committed version of the tuple with key x. However, because
of the unrepeatable read by T
1
, the history is not possible at transaction-level
read consistency: both transactions are update transactions and are therefore, by
definition, required to run at the serializable isolation level.
t
In implementing statement-level read consistency by locking, no S locks are
used, because the read actions by both read-only and update transactions operate
on committed versions of the (advanced) start times of the transactions. Commit-
duration X locks are used to protect the update actions.
If read actions and update actions are generated from the same
SQL
statement,
then the system may protect both actions by X locks (or U locks upgraded later to
X locks), in which case no unrepeatable reads may occur after all.
Example 12.9
If both the read and write actions in the transactions of Example
12.8
are generated from the single
SQL
statements
update
r
set
V
D
V
100
where
X
D
:x
update
r
set
V
D
V
200
where
X
D
:x
then the key x is X-locked for commit duration before reading and updating the
tuple with key x, so no lost update will occur.
t
12.5
Snapshot Isolation
Several well-known database management systems that use versioning for con-
currency control enforce an isolation level called
snapshot isolation
. With snap-
shot isolation, no distinction is made between read-only and update transac-
tions. All read actions by any transaction read from the start-time version of the
transaction, except that, again, read actions on data updated by the transaction
itself read the current data. All update actions operate on the most recent tuple
versions.
Under snapshot isolation, if two committed transactions T
1
and T
2
are concurrent,
that is, at some timepoint both are active, then they are required to have the
disjoint-
write property
defined as follows: the
write sets
of T
1
of T
2
,thatis,thesetsof