Information Technology Reference
In-Depth Information
phantom reads were allowed, so that the
SQL
isolation level 3
ı
would be considered
sufficient, we could easily apply the read-write locking protocol to the key-range
model: an insert action IŒx;
v
or a delete action DŒx;
v
is simply protected by a
single commit-duration X lock on x and a read action RŒx;
z
;
v
by a commit-
duration S lock on x (see Problem
6.2
).
If also phantoms are to be prevented, a more sophisticated locking scheme is
needed.
Example 6.5
The following history contains a phantom dirty read but is possible
under the read-write locking protocol:
H
1
D
T
1
W
BD
Œ2;
v
:::
T
2
W
BR
Œ3; >1;
w
:::
The contents of the database in this example are at first
f
.1;
u
/; .2;
v
/; .3;
w
/
g
. Locks
are acquired as follows:
1. For DŒ2;
v
, a commit-duration X lock on key 2 for T
1
.
2. For RŒ3; >1;
w
, a commit-duration S lock on key 3 for T
2
.
The locks are granted without wait.
t
Example 6.6
The following history contains a phantom unrepeatable read but is
possible under the read-write locking protocol:
H
2
D
T
1
W
BR
Œ3; >1;
w
:::
T
2
W
BI
Œ2;
v
:::
The contents of the database in this example are at first
f
.1;
u
/; .3;
w
/
g
. Locks are
acquired as follows:
1. For RŒ3; >1;
w
, a commit-duration S lock on key 3 for T
1
.
2. For IŒ2;
v
, a commit-duration X lock on key 2 for T
2
.
The locks are granted without wait.
t
The basic solution to the above problem would be to S-lock the entire range that
is read. In other words, when performing, for example, the read action RŒ3; > 1,
the range .1; 3 should be S-locked for commit duration. The S lock on .1; 3 is
interpreted as S-locking all keys x with 1<x
3. Then the insertion IŒ2 could
not be performed by T
1
before T
2
commits, because T
1
requires an X lock on key 2
already S-locked by T
2
for commit duration because key 2 is in the range .1; 3.
For efficiency reasons we do not want to use the scheme of locking ranges
explicitly, but we implement this effect by requiring that an insert action IŒx or a
delete action DŒx not only locks the key x butalsothekeynexttox in the database.
Here, as is shown in the sequel, for IŒx key x is locked for commit duration and
the key next to x only for short duration (the lock is released immediately after the
action), and for DŒx key x is locked for short duration but the key next to x for
commit duration. Then the effect of range locking is obtained by requiring for a
readactionanSlockonlyonthekeytoberead.
