SET TRANSACTION
Go Up to Statement and Function Reference (Language Reference Guide)
Starts a transaction and optionally specifies its behavior. Available in ESQL (GPRE), DSQL, and ISQL.
SET TRANSACTION [NAME transaction]
[READ WRITE | READ ONLY]
[WAIT | NO WAIT]
[[ISOLATION LEVEL] {SNAPSHOT [TABLE STABILITY]
| READ COMMITTED [[NO] RECORD_VERSION]}]
[RESERVING reserving_clause
| USING dbhandle [, dbhandle …]]
[[NO] SAVEPOINT];
reserving_clause = table [, table …]
[FOR [SHARED | PROTECTED] {READ | WRITE}] [, reserving_clause]
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in
isql, the semicolon is a terminating symbol for the statement, so it must be included.| Argument | Description |
|---|---|
|
|
Specifies the name for this transaction.
|
|
|
[Default] Specifies that the transaction can read and write to tables. |
|
|
Specifies that the transaction can only read tables. |
|
|
[Default] Specifies that a transaction wait for access if it encounters a lock conflict with another transaction. |
|
|
Specifies that a transaction immediately return an error if it encounters a lock conflict. |
|
|
Specifies the isolation level for this transaction when attempting to access the same tables as other simultaneous transactions; default: |
|
|
Reserves lock for tables at transaction start. |
|
|
Limits database access to a subset of available databases; SQL only. |
|
NO SAVEPOINT |
If NO SAVEPOINT is mentioned, the transaction is executed without starting an implicit savepoint for any SQL statements that execute within the context of that transaction. By default, InterBase starts an implicit savepoint to guarantee the atomicity of an SQL statement. For more information, see Chapter 5, "Working with Transactions" section on "Working with the NO SAVEPOINT Option" in the API Guide. |
Description: SET TRANSACTION starts a transaction, and optionally specifies its database access, lock conflict behavior, and level of interaction with other concurrent transactions accessing the same data. It can also reserve locks for tables. As an alternative to reserving tables, multiple database SQL applications can restrict a transaction access to a subset of connected databases.
Applications preprocessed with the
gpre -manual switch must explicitly start each transaction with a SET TRANSACTION statement.SET TRANSACTION affects the default transaction unless another transaction is specified in the optional NAME clause. Named transactions enable support for multiple, simultaneous transactions in a single application. All transaction names must be declared as host-language variables at compile time. In DSQL, this restriction prevents dynamic specification of transaction names.
By default a transaction has READ WRITE access to a database. If a transaction only needs to read data, specify the READ ONLY parameter.
When simultaneous transactions attempt to update the same data in tables, only the first update succeeds. No other transaction can update or delete that data until the controlling transaction is rolled back or committed. By default, transactions WAIT until the controlling transaction ends, then attempt their own operations. To force a transaction to return immediately and report a lock conflict error without waiting, specify the NO WAIT parameter.
ISOLATION LEVEL determines how a transaction interacts with other simultaneous transactions accessing the same tables. The default ISOLATION LEVEL is SNAPSHOT. It provides a repeatable-read view of the database at the moment the transaction starts. Changes made by other simultaneous transactions are not visible.
SNAPSHOT TABLE STABILITY provides a repeatable read of the database by ensuring that transactions cannot write to tables, though they may still be able to read from them.
READ COMMITTED enables a transaction to see the most recently committed changes made by other simultaneous transactions. It can also update rows as long as no update conflict occurs. Uncommitted changes made by other transactions remain invisible until committed. READ COMMITTED also provides two optional parameters:
NO RECORD_VERSION, the default, reads only the latest version of a row. If theWAITlock resolution option is specified, then the transaction waits until the latest version of a row is committed or rolled back, and retries its read.RECORD_VERSIONreads the latest committed version of a row, even if more recent uncommitted version also resides on disk.
The RESERVING clause enables a transaction to register its desired level of access for specified tables when the transaction starts instead of when the transaction attempts its operations on that table. Reserving tables at transaction start can reduce the possibility of deadlocks.
The USING clause, available only in SQL, can be used to conserve system resources by limiting the number of databases a transaction can access.
Examples: The following embedded SQL statement sets up the default transaction with an isolation level of READ COMMITTED. If the transaction encounters an update conflict, it waits to get control until the first (locking) transaction is committed or rolled back.
EXEC SQL
SET TRANSACTION WAIT ISOLATION LEVEL READ COMMITTED;
The next embedded SQL statement starts a named transaction:
EXEC SQL
SET TRANSACTION NAME T1 READ COMMITTED;
The following embedded SQL statement reserves three tables:
EXEC SQL
SET TRANSACTION NAME TR1
ISOLATION LEVEL READ COMMITTED
NO RECORD_VERSION WAIT
RESERVING TABLE1, TABLE2 FOR SHARED WRITE,
TABLE3 FOR PROTECTED WRITE;