Transaction Statements

Firebird has a small lexicon of SQL statements to start, manage, commit and reverse (roll back) the transactions that form the boundaries of most database tasks:

SET TRANSACTION: configures and starts a transaction
COMMIT: signals the end of a unit of work and writes changes permanently to the database
ROLLBACK: undoes the changes performed in the transaction or to a savepoint
SAVEPOINT: marks a position in the log of work done, in case a partial rollback is needed
RELEASE SAVEPOINT: erases a savepoint

`SET TRANSACTION`

Configures and starts a transaction

Available in

DSQL, ESQL

Syntax

SET TRANSACTION
   [NAME tr_name]
   [<tr_option> ...]

<tr_option> ::=
     READ {ONLY | WRITE}
   | [NO] WAIT
   | [ISOLATION LEVEL] <isolation_level>
   | NO AUTO UNDO
   | RESTART REQUESTS
   | AUTO COMMIT
   | IGNORE LIMBO
   | LOCK TIMEOUT seconds
   | RESERVING <tables>
   | USING <dbhandles>

<isolation_level> ::=
    SNAPSHOT [AT NUMBER snapshot_number]
  | SNAPSHOT TABLE [STABILITY]
  | READ {UNCOMMITED | COMMITTED} [<read-commited-opt>]

<read-commited-opt> ::=
  [NO] RECORD_VERSION | READ CONSISTENCY

<tables> ::= <table_spec> [, <table_spec> ...]

<table_spec> ::= tablename [, tablename ...]
  [FOR [SHARED | PROTECTED] {READ | WRITE}]

<dbhandles> ::= dbhandle [, dbhandle ...]

Table 1. `SET TRANSACTION` Statement Parameters
Parameter	Description
tr_name	Transaction name.Available only in ESQL
tr_option	Optional transaction option.Each option should be specified at most once, and some options are mutually exclusive (e.g. `READ ONLY` vs `READ WRITE`, `WAIT` vs `NO WAIT`)
seconds	The time in seconds for the statement to wait in case a conflict occurs.Has to be greater than or equal to `0`.
snapshot_number	Snapshot number to use for this transaction
tables	The list of tables to reserve
dbhandles	The list of databases the database can access.Available only in ESQL
table_spec	Table reservation specification
tablename	The name of the table to reserve
dbhandle	The handle of the database the transaction can access.Available only in ESQL

Generally, only client applications start transactions.Exceptions are when the server starts an autonomous transaction, and transactions for certain background system threads/processes, such as sweeping.

A client application can start any number of concurrently running transactions.A single connection can have multiple concurrent active transactions (though not all drivers or access components support this).A limit does exist, for the total number of transactions in all client applications working with one particular database from the moment the database was restored from its gbak backup or from the moment the database was created originally.The limit is 2⁴⁸ — 281,474,976,710,656.

All clauses in the SET TRANSACTION statement are optional.If the statement starting a transaction has no clauses specified, the transaction will be started with default values for access mode, lock resolution mode and isolation level, which are:

SET TRANSACTION
  READ WRITE
  WAIT
  ISOLATION LEVEL SNAPSHOT;

Warning

Database drivers or access components may use different defaults for transactions started through their API.Check their documentation for details.

The server assigns integer numbers to transactions sequentially.Whenever a client starts any transaction, either explicitly defined or by default, the server sends the transaction ID to the client.This number can be retrieved in SQL using the context variable CURRENT_TRANSACTION.

Note

Some database drivers — or their governing specifications — require that you configure and start transaction through API methods.In that case, using SET TRANSACTION is either not supported, or may result in unspecified behaviour.An example of this is JDBC and the Firebird JDBC driver Jaybird.

Check the documentation of your driver for details.

The NAME and USING clauses are only valid in ESQL.