Starting With SET TRANSACTION

Go Up to Starting the Default Transaction

SET TRANSACTION issued without parameters starts the default transaction, GDS__TRANS, with the following default behavior:

READ WRITE WAIT ISOLATION LEVEL SNAPSHOT

The following table summarizes these settings:

Parameter	Setting	Purpose
Access mode	READ WRITE	Access mode. This transaction can select, insert, update, and delete data.
Lock resolution	WAIT	Lock resolution. This transaction waits for locked tables and rows to be released to see if it can then update them before reporting a lock conflict.
Isolation level	ISOLATION LEVEL SNAPSHOT	This transaction receives a stable, unchanging view of the database as it is at the moment the transaction starts; it never sees changes made to the database by other active transactions.

Note:
Explicitly starting the default transaction is good programming practice. It makes a program’s source code easier to understand.

The following statements are equivalent. They both start the default transaction with the default behavior.

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

To start the default transaction, but change its characteristics, SET TRANSACTION must be used to specify those characteristics that differ from the default. Characteristics that do not differ from the default can be omitted. For example, the following statement starts the default transaction for READ ONLY access, WAIT lock resolution, and ISOLATION LEVEL SNAPSHOT:

EXEC SQL
SET TRANSACTION READ ONLY;

As this example illustrates, the NAME clause can be omitted when starting the default transaction.

Important:
In DSQL, changing the characteristics of the default transaction is accomplished as with PREPARE and EXECUTE in a manner similar to the one described, but the program must be preprocessed using the gpre -m switch.

For more information about preprocessing programs with the -m switch, see Preprocessing, Compiling, and Linking. For more information about transaction behavior and modification, see Specifying SET TRANSACTION Behavior.

Advance To:

• Starting a Named Transaction