Starting a transaction with READ COMMITTED isolation level
Go Up to Isolation Level
To start a READ COMMITTED
transaction, the isolation level must be specified. For example, the following statement starts a named transaction, t1
, for READ WRITE
access and sets isolation level to READ COMMITTED
:
EXEC SQL SET TRANSACTION NAME t1 READ WRITE READ COMMITTED;
Isolation level always follows access mode. If the access mode is omitted, isolation level is the first parameter to follow the transaction name.
READ COMMITTED
supports mutually exclusive optional parameters, RECORD_VERSION
and NO RECORD_VERSION,
which determine the READ COMMITTED
behavior when it encounters a row where the latest version of that row is uncommitted:
RECORD_VERSION
specifies that the transaction immediately reads the latest committed version of a row, even if a more recent uncommitted version also resides on disk.NO RECORD_VERSION
, the default, specifies that the transaction can only read the latest version of a requested row. If theWAIT
lock resolution option is also specified, then the transaction waits until the latest version of a row is committed or rolled back, and retries its read. If the NO WAIT option is specified, the transaction returns an immediate deadlock error.
Because NO RECORD_VERSION
is the default behavior, it need not be specified with READ COMITTED
. For example, the following statements are equivalent. They start a named transaction, t1
, for READ WRITE
access and set isolation level to READ COMMITTED NO RECORD_VERSION
.
EXEC SQL SET TRANSACTION NAME t1 READ WRITE READ COMMITTED; EXEC SQL SET TRANSACTION NAME t1 READ WRITE READ COMMITTED NO RECORD_VERSION;
RECORD_VERSION
must always be specified when it is used. For example, the following statement starts a named transaction, t1
, for READ WRITE
access and sets isolation level to READ COMMITTED RECORD_VERSION
:
EXEC SQL SET TRANSACTION NAME t1 READ WRITE READ COMMITTED RECORD_VERSION;