Specifying SET TRANSACTION Behavior

Go Up to Starting a Named Transaction

Use SET TRANSACTION to start a named transaction, and optionally specify its behavior. The syntax for starting a named transaction using default behavior is:

SET TRANSACTION NAME name;

For a summary of the default behavior for a transaction started without specifying behavior parameters, see the page Starting With SET TRANSACTION. The following statements are equivalent: they both start the transaction named t1, using default transaction behavior.

EXEC SQL
SET TRANSACTION NAME t1;
EXEC SQL
SET TRANSACTION NAME t1 READ WRITE WAIT ISOLATION LEVEL SNAPSHOT;

The following table lists the optional SET TRANSACTION parameters for specifying the behavior of the default transaction:

SET TRANSACTION parameters
Parameter	Setting	Purpose
Access Mode	`READ ONLY` or `READ WRITE`	Describes the type of access this transaction is permitted for a table. For more information about access mode, see Access Mode.
Lock Resolution	`WAIT` or `NO WAIT`	Specifies what happens when this transaction encounters a locked row during an update or a delete operation. It either waits for the lock to be released so it can attempt to complete its actions, or it returns an immediate lock conflict error message. For more information about lock resolution, see Lock Resolution.
Isolation Level	`SNAPSHOT` provides a view of the database at the moment this transaction starts, but prevents viewing changes made by other active transactions. `SNAPSHOT TABLE STABILITY` prevents other transactions from making changes to tables that this transaction is reading and updating, but permits them to read rows in the table. `READ COMMITTED` reads the most recently committed version of a row during updates and deletions, and allows this transaction to make changes if there is no update conflict with other transactions.	Determines the interaction of this transaction with other simultaneous transactions attempting to access the same tables. `READ COMMITTED` isolation level also enables a user to specify which version of a row it can read. There are two options: `RECORD_VERSION:`the transaction immediately reads the latest committed version of a requested row, even if a more recent uncommitted version also resides on disk. `NO RECORD_VERSION:` if an uncommitted version of the requested row is present and `WAIT` lock resolution is specified, the transaction waits until the committed version of the row is also the latest version; if `NO WAIT` is specified, the transaction immediately returns an error (“deadlock”) if the committed version is not the most recent version.
Table Reservation	`RESERVING`	Specifies a subset of available tables to lock immediately for this transaction to access.
Database Specification	`USING`	Specifies a subset of available databases that this transaction can access; it cannot access any other databases. The purpose of this option is to reduce the amount of system resources used by this transaction. Note: `USING` is not available in `DSQL`.

The complete syntax of SET TRANSACTION is:

EXEC SQL
SET TRANSACTION [NAME name]
[READ WRITE| READ ONLY]
[WAIT | NO WAIT]
[[ISOLATION LEVEL] {SNAPSHOT [TABLE STABILITY]
| READ COMMITTED [[NO] RECORD_VERSION]}]
[RESERVING <reserving_clause>
| USING dbhandle [, dbhandle ...]];
<reserving_clause> = table [, table ...]
[FOR [SHARED | PROTECTED] {READ | WRITE}] [, <reserving_clause>]

Transaction options are fully described in the following sections.

Topics

Advance To:

Access Mode