FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 Context VariablesSecurity 

Almost all operations in Firebird occur in the context of a transaction.Units of work are isolated between a start point and end point.Changes to data remain reversible until the moment the client application instructs the server to commit them.

Unless explicitly mentioned otherwise in an “Available in” section, transaction control statements are available in DSQL.Availability in ESQL is — bar some exceptions — not tracked by this Language Reference.Transaction control statements are not available in PSQL.

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 248 — 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.

Transaction Name

The optional NAME attribute defines the name of a transaction.Use of this attribute is available only in Embedded SQL (ESQL).In ESQL applications, named transactions make it possible to have several transactions active simultaneously in one application.If named transactions are used, a host-language variable with the same name must be declared and initialized for each named transaction.This is a limitation that prevents dynamic specification of transaction names and thus rules out transaction naming in DSQL.