SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

Introduction

HP NonStop SQL/MX Reference Manual—523725-004

1-20

Transaction Isolation Levels

A transaction has an isolation level that is either READ UNCOMMITTED, READ

COMMITTED, or SERIALIZABLE or REPEATABLE READ. The SQL/MX

implementation for REPEATABLE READ and SERIALIZABLE is equivalent.

SERIALIZABLE is used for purposes of illustration.

You can set the isolation level of a transaction explicitly by using a SET

TRANSACTION statement. See SET TRANSACTION Statement on page 2-223.

You can set your system default for the transaction isolation level by specifying the

ISOLATION_LEVEL entry in the SYSTEM_DEFAULTS table. The default isolation level

of a transaction is determined according to the rules specified in ISOLATION_LEVEL

on page 10-44.

READ UNCOMMITTED

This isolation level allows your transaction to access locked data. You cannot use

READ UNCOMMITTED for transactions that modify the database.

READ UNCOMMITTED provides the lowest level of data consistency. A transaction

executing with this isolation level is allowed to:

•

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.