SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

Introduction
HP NonStop SQL/MX Reference Manual—544517-008
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 transaction ends.
SERIALIZABLE (or REPEATABLE READ) provides the highest level of data
consistency. A transaction executing with this isolation level does not allow dirty reads,
non-repeatable reads, or phantoms.
For audited tables (SQL/MX tables are audited), 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 (including delete) rows in the range of rows (key
range if using unique primary key or all rows if using non-unique column) examined by
the transaction.
Non-Unique Key Considerations for SERIALIZABLE or REPEATABLE
READ
If the SELECT statement uses a unique column (primary key), SQL/MX locks only the
rows specified in the unique key range. If the SELECT statement uses a
non-unique column, SQL/MX locks all the rows (whole table) to guarantee
REPEATABLE READ access. For information on locks, see Database Integrity and
Locking on page 1-10.