Starting a transaction with READ COMMITTED isolation level

From InterBase

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 the WAIT 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;

Advance To:

Retrieved from "https://docwiki.embarcadero.com/InterBase/2020/e/index.php?title=Starting_a_transaction_with_READ_COMMITTED_isolation_level&oldid=25952"