Database Reference
In-Depth Information
must contain also the identifier of the transaction
ID
T
. Moreover, each update of
the database in the RDB machine
M
R
is marked by the transaction
ID
T
in the way
that, when COMMIT or ROLLBACK is requested by some transaction
ID
T
, then
only the updates made by that transaction are committed. As we will see, these
new requirements will change the Input/Output functions of the universal and RDB
machines, with respect to their previous definitions.
In such a system, some kind of
concurrency control mechanism
is needed to
ensure that concurrent transactions do not interfere with each other's operation.
Without such a mechanism, there are numerous problems that can arise; to avoid
these problems, as most commonly encountered in practice, a concurrency control
mechanism known as
locking
is used. The basic idea of locking is simple: when a
transaction needs an assurance that some object that it is interested in—typically
a database tuple—will not change in some unpredictable manner while its back is
turned (as it were), it
acquires a lock
on that object. The effect of the lock is to
“lock other transactions out of” the object, and thus in particular to prevent them
from changing it. The first transaction is thus able to carry out its processing in the
certain knowledge that the object in question will remain in an idle state for as long
as that transaction wishes it to.
We assume throughout this section that X-locks (
exclusive locks
) and S-locks
(
shared locks
) are the only kinds of locks available for categorial RDB machine
M
R
.
If transaction
ID
T
=
N
a
holds an X-lock on tuple
t
, then a request from transac-
tion
ID
T
=
N
b
for a lock of either type on
t
will cause
N
b
to go into a wait state.
N
b
will wait until
N
a
's lock is released. The mechanism for such synchronization is as
follows: The transaction
N
b
(of a program in a universal machine) makes CALLs
for some embedded SQL statement and waits for response data from
M
R
.If
N
a
's
lock is not released, then the error flag which returns from
M
R
is different from 0
(in the same time-sharing window), and transaction
N
b
has to invoke again (possi-
bly in a subsequent time-sharing window) the same CALLs (wait state). Only when
N
a
's lock is released (into some different time-sharing window) then the response
for CALLs of the transaction
N
b
will return with the error flag equal to 0 and with
requested data.
If transaction
ID
T
=
N
a
holds (in the machine
M
R
) an S-lock on tuple
t
, then:
•
A request from transaction
ID
T
=
N
B
for an X-lock on
t
will cause
N
b
to go into
a wait state (and
N
b
will wait until
N
a
's lock is released) as described above (by
subsequent CALLs of the same embedded SQL statement, until response from
M
R
does not return with the error flag equal to 0);
•
A request from transaction
N
b
for an S-lock on
t
will be granted (that is,
N
b
will
now also hold an S-lock on
t
).
When, in the categorial RDB machine
M
R
, a transaction successfully retrieves a
tuple, it automatically acquires an S-lock on that tuple. When a transaction suc-
cessfully updates a tuple, it automatically acquires an X-lock on that tuple. In the
categorial RDB machine
M
R
, the X-locks of an transaction
ID
T
are held until the
next synchpoint of the same transaction
ID
T
. S-locks are also normally held until
that time.
