Information Technology Reference
In-Depth Information
that all the transactions are executed serially, one transaction at a time, although
in reality some transactions are running concurrently.
Two of the above properties, namely, atomicity and durability, are maintained
solely by the database management system, according to the transaction boundaries,
that is, the placement of the
commit
or
rollback
statements, as designated by the
application programmer. The other two properties are maintained partly by the
application programmer and partly by the database management system.
For consistency, the application programmer must ensure that each of her
committed transactions, when run alone in a failure-free environment on a consistent
logical database, retains the consistency of the database, while the system must
ensure that the consistency of the logical database is also preserved in the presence
of multiple concurrently running transactions, to the extent possible under the
isolation levels set for the transactions. If all the updating transactions are set to run
at the highest isolation level (i.e., serializable), the system is expected to provide
full preservation of the consistency of the logical database.
For isolation, the application programmer must ensure that each of her trans-
actions is designated to run at an isolation level high enough so that database
consistency is preserved to a sufficient extent, while the system must ensure that
the designated isolation level indeed is achieved in the presence of concurrent
transactions.
Example 1.3
Assuming the primary keys in r.X;V/ are integers, the following
embedded
SQL
program fragment reads the maximum key x from r and inserts
into r anewtuplewithkeyx
C
1:
exec sql select max
(X )
into
:
x
from
r ;
exec sql insert into
r
values
.
:x
C
1;
v
0
/;
exec sql commit
.
When run alone without interference from other transactions and in the absence of
failures, the program fragment generates a committed transaction of the form
B:::RŒx;
v
:::IŒx
C
1;
v
0
C ,
where the action RŒx;
v
retrieves the tuple .x;
v
/ with the maximum key x from r
and the action IŒx
C
1;
v
0
inserts the tuple .x
C
1;
v
0
/. (To determine the maximum
key x, also some other read actions may be needed.)
The above transaction is obviously logically consistent: when run alone, it
retains the consistency of an initially consistent logical database. The designer of
the transaction (the application programmer) is allowed to assume that no other
transaction can change the maximum key between the executions of the read action
RŒx;
v
and the insert action IŒx
C
1;
v
0
.
A violation of the primary-key constraint would occur if some other transaction
inserted a tuple with key x
C
1. This other transaction could, for example, be
generated from another instance of the same application, and both transactions may
read the same maximum key x and hence try to insert a tuple with the key x
C
1.
