Transactional Isolation - Transaction Processing - page 113

D 00 Df .0; 10/; .1; 1/; .2; 2/; .3; 3/; .4; 4/ g ,
which satisfies the integrity constraint. t

The definition of unrepeatable reads presented in the previous section also
encompasses read actions that are included in action sequences rolled back in partial
rollbacks completed before executing the offending update action by the other
transaction. There is good reason to restrict the definition of unrepeatable reads so
as to exclude such read actions.

Example 5.21 According to the definition, the action RŒx is an unrepeatable read
in the history

T 1 : B:::SŒP:::RŒx:::AŒP:::CŒP::: :::WŒyC

T 2 : BW ŒxC
However, it is unlikely that the unrepeatable read would cause any violation of
the integrity of the database. This is because it would exhibit bad transaction-
programming practice if an update done after CŒP in T 1 , such as the update WŒy,
would somehow depend on information read by RŒx. t

The above example suggests that we should impose the following additional
requirement on a logically consistent transaction: no update action following a
completed partial rollback should use information read by a read action included
in the action sequence rolled back in that partial rollback.

5.5 SQL Isolation Levels

The SQL language has a statement set transaction , which can be used in an
application program to set the isolation level ,or degree of isolation , of the next
transaction to be started.
The isolation anomalies that form the basis of the definition of isolation levels
are not exactly the same as the ones we defined above: the dirty and unrepeatable
reads in SQL parlance do not include phantom reads.
There are four possible isolation levels in SQL (ordered by complexity, the
first ones being the least restrictive and also the simplest to implement): read
uncommitted, read committed, repeatable read, and serializable. The meaning of
these isolation levels is stated below according to the corrected definitions given by
Berenson et al. [ 1995 ]:
1 ı Read uncommitted: at this lowest isolation level, dirty writes are not allowed,
but the transaction may do dirty or unrepeatable reads of any type.
2 ı Read committed: dirty writes and simple dirty reads are not allowed, but the
transaction may do phantom dirty reads and unrepeatable reads of any type.
3 ı Repeatable read: dirty writes, simple dirty reads, and simple unrepeatable reads
are not allowed, but the transaction may do phantom dirty reads and phantom
unrepeatable reads.