SQL/MX 2.x Reference Manual (H06.04+)

Introduction
HP NonStop SQL/MX Reference Manual—540440-003
1-22
Transaction Isolation Levels
•
Read data modified by a concurrent transaction (sometimes referred to as dirty
reads)
•
Read different committed values for the same item at different times or find that the
item no longer exists (sometimes referred to as nonrepeatable reads)
•
Read different sets of committed values satisfying the same predicate at different
times (sometimes referred to as phantoms)
READ COMMITTED
This option allows your transaction to access only committed data.
The implementation requires that a lock can be acquired on the requested data—but
does not actually lock the data, thereby reducing lock request conflicts. If a lock cannot
be granted (implying that the row contains uncommitted data), the transaction request
waits until the lock in place is released.
READ COMMITTED provides the next level of data consistency. A transaction
executing with this isolation level does not allow dirty reads, but both nonrepeatable
reads and phantoms are possible.
READ COMMITTED provides sufficient consistency for any transaction that does not
require a repeatable-read capability.
SERIALIZABLE or REPEATABLE READ
This option locks all data accessed through the transaction and holds the locks on data
in audited tables until the end of the transaction.
SERIALIZABLE provides the highest level of data consistency. A transaction executing
with this isolation level does not allow dirty reads, nonrepeatable reads, or phantoms.
For audited tables, SERIALIZABLE uses shared locks for unmodified rows and
exclusive locks for modified rows—but all locks are held by the transaction and not
released until the transaction ends. SERIALIZABLE prevents other users from inserting
or modifying rows in the range of rows examined by the transaction.
For nonaudited tables, SERIALIZABLE does not provide repeatable-read protection.
That is, it does not prevent other users from inserting or modifying rows in the range of
rows examined by the transaction.