Chapter 10Transaction Control

Everything in Firebird happens in transactions. Units of work are isolated between a start point and an end point. Changes to data remain reversible until the moment the client application instructs the server to commit them.

10.1Transaction Statements

Firebird has a small lexicon of SQL statements that are used by client applications to start, manage, commit and reverse (roll back) the transactions that form the boundaries of all database tasks:

SET TRANSACTION

for configuring and starting a transaction

COMMIT

to signal the end of a unit of work and write changes permanently to the database

ROLLBACK

to reverse the changes performed in the transaction

SAVEPOINT

to mark a position in the log of work done, in case a partial rollback is needed

RELEASE SAVEPOINT

to erase a savepoint

10.1.1SET TRANSACTION

Used forConfiguring and starting a transaction

AvailableDSQL, ESQL

Syntax

   |SET TRANSACTION
   |   [NAME tr_name]
   |   [READ WRITE | READ ONLY]
   |   [[ISOLATION LEVEL]
   |     { SNAPSHOT [TABLE STABILITY]
   |     | READ COMMITTED [[NO] RECORD_VERSION] }]
   |   [WAIT | NO WAIT]
   |   [LOCK TIMEOUT seconds]
   |   [NO AUTO UNDO]
   |   [IGNORE LIMBO]
   |   [RESERVING <tables> | USING <dbhandles>]
   | 
   |<tables> ::= <table_spec> [, <table_spec> ...]
   | 
   |<table_spec> ::= tablename [, tablename ...]
   |      [FOR [SHARED | PROTECTED] {READ | WRITE}]
   | 
   |<dbhandles> ::= dbhandle [, dbhandle ...]

Table 10.1SET TRANSACTION Statement Parameters
ParameterDescription

tr_name

Transaction name. Available only in ESQL

seconds

The time in seconds for the statement to wait in case a conflict occurs

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 database can access. Available only in ESQL

The SET TRANSACTION statement configures the transaction and starts it. As a rule, only client applications start transactions. The exceptions are the occasions when the server starts an autonomous transaction or transactions for certain background system threads/processes, such as sweeping.

A client application can start any number of concurrently running transactions. A limit does exist, for the total number of running transactions in all client applications working with one particular database from the moment the database was restored from its backup copy or from the moment the database was created originally. The limit is 231-1, or 2,147,483,647.

All clauses in the SET TRANSACTION statement are optional. If the statement starting a transaction has no clauses specified in it, it 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;

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.

10.1.1.1Transaction Parameters

The main parameters of a transaction are:

  • data access mode (READ WRITE, READ ONLY)

  • lock resolution mode (WAIT, NO WAIT) with an optional LOCK TIMEOUT specification

  • isolation level (READ COMMITTED, SNAPSHOT, TABLE STABILITY)

  • a mechanism for reserving or releasing tables (the RESERVING clause)

10.1.1.1.1Transaction Name

The optional NAME attribute defines the name of a transaction. Use of this attribute is available only in Embedded SQL. 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.

10.1.1.1.2Access Mode

The two database access modes for transactions are READ WRITE and READ ONLY.

  • If the access mode is READ WRITE, operations in the context of this transaction can be both read operations and data update operations. This is the default mode.

  • If the access mode is READ ONLY, only SELECT operations can be executed in the context of this transaction. Any attempt to change data in the context of such a transaction will result in database exceptions. However, it does not apply to global temporary tables (GTT) that are allowed to be changed in READ ONLY transactions.

10.1.1.1.3Lock Resolution Mode

When several client processes work with the same database, locks may occur when one process makes uncommitted changes in a table row, or deletes a row, and another process tries to update or delete the same row. Such locks are called update conflicts.

Locks may occur in other situations when multiple transaction isolation levels are used.

The two lock resolution modes are WAIT and NO WAIT.

10.1.1.1.3.1WAIT Mode

In the WAIT mode (the default mode), if a conflict occurs between two parallel processes executing concurrent data updates in the same database, a WAIT transaction will wait till the other transaction has finished — by committing (COMMIT) or rolling back (ROLLBACK). The client application with the WAIT transaction will be put on hold until the conflict is resolved.

If a LOCK TIMEOUT is specified for the WAIT transaction, waiting will continue only for the number of seconds specified in this clause. If the lock is unresolved at the end of the specified interval, the error message Lock time-out on wait transaction is returned to the client.

Lock resolution behaviour can vary a little, depending on the transaction isolation level.

10.1.1.1.3.2NO WAIT Mode

In the NO WAIT mode, a transaction will immediately throw a database exception if a conflict occurs.

10.1.1.1.4Isolation Level

Keeping the work of one database task separated from others is what isolation is about. Changes made by one statement become visible to all remaining statements executing within the same transaction, regardless of its isolation level. Changes that are in process within other transactions remain invisible to the current transaction as long as they remain uncommitted. The isolation level and, sometimes, other attributes, determine how transactions will interact when another transaction wants to commit work.

The ISOLATION LEVEL attribute defines the isolation level for the transaction being started. It is the most significant transaction parameter for determining its behavior towards other concurrently running transactions.

The three isolation levels supported in Firebird are:

  • SNAPSHOT

  • SNAPSHOT TABLE STABILITY

  • READ COMMITTED with two specifications (NO RECORD_VERSION and RECORD_VERSION)

10.1.1.1.4.1SNAPSHOT Isolation Level

SNAPSHOT isolation level — the default level — allows the transaction to see only those changes that were committed before this one was started. Any committed changes made by concurrent transactions will not be seen in a SNAPSHOT transaction while it is active. The changes will become visible to a new transaction once the current transaction is either committed or rolled back completely, but not if it is just rolled back to a savepoint.

Autonomous Transactions

Changes made by autonomous transactions are not seen in the context of the SNAPSHOT transaction that launched it.

10.1.1.1.4.2SNAPSHOT TABLE STABILITY Isolation Level

The SNAPSHOT TABLE STABILITY isolation level is the most restrictive. As in SNAPSHOT, a transaction in SNAPSHOT TABLE STABILITY isolation sees only those changes that were committed before the current transaction was started. After a SNAPSHOT TABLE STABILITY is started, no other transactions can make any changes to any table in the database that has changes pending. Other transactions are able to read other data, but any attempt at inserting, updating or deleting by a parallel process will cause conflict exceptions.

The RESERVING clause can be used to allow other transactions to change data in some tables.

If any other transaction has an uncommitted change pending in any (non-SHARED) table listed in the RESERVING clause, trying to start a SNAPSHOT TABLE STABILITY transaction will result in an indefinite wait (default or explicit WAIT), or an exception (NO WAIT or after expiration of the LOCK TIMEOUT).

10.1.1.1.4.3READ COMMITTED Isolation Level

The READ COMMITTED isolation level allows all data changes that other transactions have committed since it started to be seen immediately by the uncommitted current transaction. Uncommitted changes are not visible to a READ COMMITTED transaction.

To retrieve the updated list of rows in the table you are interested in — refresh — the SELECT statement just needs to be requested again, whilst still in the uncommitted READ COMMITTED transaction.

RECORD_VERSION

One of two modifying parameters can be specified for READ COMMITTED transactions, depending on the kind of conflict resolution desired: RECORD_VERSION and NO RECORD_VERSION. As the names suggest, they are mutually exclusive.

  • NO RECORD_VERSION (the default value) is a kind of two-phase locking mechanism: it will make the transaction unable to write to any row that has an update pending from another transaction.

    • if NO WAIT is the lock resolution strategy specified, it will throw a lock conflict error immediately

    • with WAIT specified, it will wait until the other transaction either commits or is rolled back. If the other transaction is rolled back, or if it is committed and its transaction ID is older than the current transaction’s ID, then the current transaction’s change is allowed. A lock conflict error is returned if the other transaction was committed and its ID was newer than that of the current transaction.

  • With RECORD_VERSION specified, the transaction reads the latest committed version of the row, regardless of other pending versions of the row. The lock resolution strategy (WAIT or NO WAIT) does not affect the behavior of the transaction at its start in any way.

10.1.1.1.5NO AUTO UNDO

The NO AUTO UNDO option affects the handling of unused record versions (garbage) in the event of rollback. With NO AUTO UNDO flagged, the ROLLBACK statement just marks the transaction as rolled back without deleting the unused record versions created in the transaction. They are left to be mopped up later by garbage collection.

NO AUTO UNDO might be useful when a lot of separate statements are executed that change data in conditions where the transaction is likely to be committed successfully most of the time.

The NO AUTO UNDO option is ignored for transactions where no changes are made.

10.1.1.1.6IGNORE LIMBO

This flag is used to signal that records created by limbo transactions are to be ignored. Transactions are left in limbo if the second stage of a two-phase commit fails.

Historical Note

IGNORE LIMBO surfaces the TPB parameter isc_tpb_ignore_limbo, available in the API since InterBase times and mainly used by gfix.

10.1.1.1.7RESERVING

The RESERVING clause in the SET TRANSACTION statement reserves tables specified in the table list. Reserving a table prevents other transactions from making changes in them or even, with the inclusion of certain parameters, from reading data from them while this transaction is running.

A RESERVING clause can also be used to specify a list of tables that can be changed by other transactions, even if the transaction is started with the SNAPSHOT TABLE STABILITY isolation level.

One RESERVING clause is used to specify as many reserved tables as required.

10.1.1.1.7.1Options for RESERVING Clause

If one of the keywords SHARED or PROTECTED is omitted, SHARED is assumed. If the whole FOR clause is omitted, FOR SHARED READ is assumed. The names and compatibility of the four access options for reserving tables are not obvious.

Table 10.2Compatibility of Access Options for RESERVING

 

SHARED READ

SHARED WRITE

PROTECTED READ

PROTECTED WRITE

SHARED READ

Yes

Yes

Yes

Yes

SHARED WRITE

Yes

Yes

No

No

PROTECTED READ

Yes

No

Yes

No

PROTECTED WRITE

Yes

No

No

No

The combinations of these RESERVING clause flags for concurrent access depend on the isolation levels of the concurrent transactions:

  • SNAPSHOT isolation

    • Concurrent SNAPSHOT transactions with SHARED READ do not affect one other’s access

    • A concurrent mix of SNAPSHOT and READ COMMITTED transactions with SHARED WRITE do not affect one another’s access, but they block transactions with SNAPSHOT TABLE STABILITY isolation from either reading from or writing to the specified table[s]

    • Concurrent transactions with any isolation level and PROTECTED READ can only read data from the reserved tables. Any attempt to write to them will cause an exception

    • With PROTECTED WRITE, concurrent transactions with SNAPSHOT and READ COMMITTED isolation cannot write to the specified tables. Transactions with SNAPSHOT TABLE STABILITY isolation cannot read from or write to the reserved tables at all.

  • SNAPSHOT TABLE STABILITY isolation

    • All concurrent transactions with SHARED READ, regardless of their isolation levels, can read from or write (if in READ WRITE mode) to the reserved tables

    • Concurrent transactions with SNAPSHOT and READ COMMITTED isolation levels and SHARED WRITE can read data from and write (if in READ WRITE mode) to the specified tables but concurrent access to those tables from transactions with SNAPSHOT TABLE STABILITY is blocked completely whilst these transactions are active

    • Concurrent transactions with any isolation level and PROTECTED READ can only read from the reserved tables

    • With PROTECTED WRITE, concurrent SNAPSHOT and READ COMMITTED transactions can read from but not write to the reserved tables. Access by transactions with the SNAPSHOT TABLE STABILITY isolation level is blocked completely.

  • READ COMMITTED isolation

    • With SHARED READ, all concurrent transactions with any isolation level can both read from and write (if in READ WRITE mode) to the reserved tables

    • SHARED WRITE allows all transactions in SNAPSHOT and READ COMMITTED isolation to read from and write (if in READ WRITE mode) to the specified tables and blocks access completely from transactions with SNAPSHOT TABLE STABILITY isolation

    • With PROTECTED READ, concurrent transactions with any isolation level can only read from the reserved tables

    • With PROTECTED WRITE, concurrent transactions in SNAPSHOT and READ COMMITTED isolation can read from but not write to the specified tables. Access from transactions in SNAPSHOT TABLE STABILITY isolation is blocked completely.

Tip

In Embedded SQL, the USING clause can be used to conserve system resources by limiting the databases the transaction can access to an enumerated list (of databases). USING is incompatible with RESERVING. A USING clause in SET TRANSACTION syntax is not supported in DSQL.

See alsoSection 10.1.2, “COMMIT, Section 10.1.3, “ROLLBACK

10.1.2COMMIT

Used forCommitting a transaction

AvailableDSQL, ESQL

Syntax

  |COMMIT [WORK] [TRANSACTION tr_name]
  |  [RELEASE] [RETAIN [SNAPSHOT]];

Table 10.3COMMIT Statement Parameter
ParameterDescription

tr_name

Transaction name. Available only in ESQL

The COMMIT statement commits all work carried out in the context of this transaction (inserts, updates, deletes, selects, execution of procedures). New record versions become available to other transactions and, unless the RETAIN clause is employed, all server resources allocated to its work are released.

If any conflicts or other errors occur in the database during the process of committing the transaction, the transaction is not committed and the reasons are passed back to the user application for handling and the opportunity to attempt another commit or to roll the transaction back.

10.1.2.1COMMIT Options

  • The optional TRANSACTION tr_name clause, available only in Embedded SQL, specifies the name of the transaction to be committed. With no TRANSACTION clause, COMMIT is applied to the default transaction.

    Note

    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.

  • The optional keyword WORK is supported just for compatibility with other relational database management systems that require it.

  • The keyword RELEASE is available only in Embedded SQL and enables disconnection from all databases after the transaction is committed. RELEASE is retained in Firebird only for compatibility with legacy versions of InterBase. It has been superseded in ESQL by the DISCONNECT statement.

  • The RETAIN [SNAPSHOT] clause is used for the soft commit, variously referred to amongst host languages and their practitioners as COMMIT WITH RETAIN, CommitRetaining, warm commit, et al. The transaction is committed but some server resources are retained and the transaction is restarted transparently with the same Transaction ID. The state of row caches and cursors is kept as it was before the soft commit.

    For soft-committed transactions whose isolation level is SNAPSHOT or SNAPSHOT TABLE STABILITY, the view of database state is not updated to reflect changes by other transactions, and the user of the application instance continues to have the same view as when the transaction started originally. Changes made during the life of the retained transaction are visible to that transaction, of course.

Recommendation

Use of the COMMIT statement in preference to ROLLBACK is recommended for ending transactions that only read data from the database, because COMMIT consumes fewer server resources and helps to optimize the performance of subsequent transactions.

See alsoSection 10.1.1, “SET TRANSACTION, Section 10.1.3, “ROLLBACK

10.1.3ROLLBACK

Used forRolling back a transaction

AvailableDSQL, ESQL

Syntax

  |ROLLBACK [WORK] [TRANSACTION tr_name]
  |[RETAIN [SNAPSHOT] | TO [SAVEPOINT] sp_name | RELEASE]

Table 10.4ROLLBACK Statement Parameters
ParameterDescription

tr_name

Transaction name. Available only in ESQL

sp_name

Savepoint name. Available only in DSQL

The ROLLBACK statement rolls back all work carried out in the context of this transaction (inserts, updates, deletes, selects, execution of procedures). ROLLBACK never fails and, thus, never causes exceptions. Unless the RETAIN clause is employed, all server resources allocated to the work of the transaction are released.

10.1.3.1ROLLBACK Options

  • The optional TRANSACTION tr_name clause, available only in Embedded SQL, specifies the name of the transaction to be committed. With no TRANSACTION clause, ROLLBACK is applied to the default transaction.

    Note

    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.

  • The optional keyword WORK is supported just for compatibility with other relational database management systems that require it.

  • The keyword RETAIN keyword specifies that, although all of the work of the transaction is to be rolled back, the transaction context is to be retained. Some server resources are retained and the transaction is restarted transparently with the same Transaction ID. The state of row caches and cursors is kept as it was before the soft rollback.

    For transactions whose isolation level is SNAPSHOT or SNAPSHOT TABLE STABILITY, the view of database state is not updated by the soft rollback to reflect changes by other transactions. The user of the application instance continues to have the same view as when the transaction started originally. Changes that were made and soft-committed during the life of the retained transaction are visible to that transaction, of course.

See alsoSection 10.1.1, “SET TRANSACTION, Section 10.1.2, “COMMIT

10.1.3.1.1ROLLBACK TO SAVEPOINT

The optional TO SAVEPOINT clause in the ROLLBACK statement specifies the name of a savepoint to which changes are to be rolled back. The effect is to roll back all changes made within the transaction, from the created savepoint forward until the point when ROLLBACK TO SAVEPOINT is requested.

ROLLBACK TO SAVEPOINT performs the following operations:

  • Any database mutations performed since the savepoint was created are undone. User variables set with RDB$SET_CONTEXT() remain unchanged.

  • Any savepoints that were created after the one named are destroyed. Savepoints earlier than the one named are preserved, along with the named savepoint itself. Repeated rollbacks to the same savepoint are thus allowed.

  • All implicit and explicit record locks that were acquired since the savepoint are released. Other transactions that have requested access to rows locked after the savepoint must continue to wait until the transaction is committed or rolled back. Other transactions that have not already requested the rows can request and access the unlocked rows immediately.

See alsoSection 10.1.4, “SAVEPOINT

10.1.4SAVEPOINT

Used forCreating a savepoint

AvailableDSQL

Syntax

  |SAVEPOINT sp_name

Table 10.5SAVEPOINT Statement Parameter
ParameterDescription

sp_name

Savepoint name. Available only in DSQL

The SAVEPOINT statement creates an SQL:99-compliant savepoint that acts as a marker in the stack of data activities within a transaction. Subsequently, the tasks performed in the stack can be undone back to this savepoint, leaving the earlier work and older savepoints untouched. Savepoint mechanisms are sometimes characterised as nested transactions.

If a savepoint already exists with the same name as the name supplied for the new one, the existing savepoint is deleted and a new one is created using the supplied name.

To roll changes back to the savepoint, the statement ROLLBACK TO SAVEPOINT is used.

Memory Considerations

The internal mechanism beneath savepoints can consume large amounts of memory, especially if the same rows receive multiple updates in one transaction. When a savepoint is no longer needed, but the transaction still has work to do, a Section 10.1.5, “RELEASE SAVEPOINT statement will erase it and thus free the resources.

Sample DSQL session with savepoints

   |CREATE TABLE TEST (ID INTEGER);
   |COMMIT;
   |INSERT INTO TEST VALUES (1);
   |COMMIT;
   |INSERT INTO TEST VALUES (2);
   |SAVEPOINT Y;
   |DELETE FROM TEST;
   |SELECT * FROM TEST; -- returns no rows
   |ROLLBACK TO Y;
   |SELECT * FROM TEST; -- returns two rows
   |ROLLBACK;
   |SELECT * FROM TEST; -- returns one row

See alsoSection 10.1.3.1.1, “ROLLBACK TO SAVEPOINT, Section 10.1.5, “RELEASE SAVEPOINT

10.1.5RELEASE SAVEPOINT

Used forErasing a savepoint

AvailableDSQL

Syntax

  |RELEASE SAVEPOINT sp_name [ONLY]

Table 10.6RELEASE SAVEPOINT Statement Parameter
ParameterDescription

sp_name

Savepoint name. Available only in DSQL

The statement RELEASE SAVEPOINT erases a named savepoint, freeing up all the resources it encompasses. By default, all the savepoints created after the named savepoint are released as well. The qualifier ONLY directs the engine to release only the named savepoint.

See alsoSection 10.1.4, “SAVEPOINT

10.1.6Internal Savepoints

By default, the engine uses an automatic transaction-level system savepoint to perform transaction rollback. When a ROLLBACK statement is issued, all changes performed in this transaction are backed out via a transaction-level savepoint and the transaction is then committed. This logic reduces the amount of garbage collection caused by rolled back transactions.

When the volume of changes performed under a transaction-level savepoint is getting large (~50000 records affected), the engine releases the transaction-level savepoint and uses the Transaction Inventory Page (TIP) as a mechanism to roll back the transaction if needed.

Tip

If you expect the volume of changes in your transaction to be large, you can specify the NO AUTO UNDO option in your SET TRANSACTION statement to block the creation of the transaction-level savepoint. Using the API instead, you would set the TPB flag isc_tpb_no_auto_undo.

10.1.7Savepoints and PSQL

Transaction control statements are not allowed in PSQL, as that would break the atomicity of the statement that calls the procedure. However, Firebird does support the raising and handling of exceptions in PSQL, so that actions performed in stored procedures and triggers can be selectively undone without the entire procedure failing.

Internally, automatic savepoints are used to:

  • undo all actions in the BEGIN…​END block where an exception occurs

  • undo all actions performed by the procedure or trigger or, in for a selectable procedure, all actions performed since the last SUSPEND, when execution terminates prematurely because of an uncaught error or exception

Each PSQL exception handling block is also bounded by automatic system savepoints.

Note

A BEGIN…​END block does not itself create an automatic savepoint. A savepoint is created only in blocks that contain the WHEN statement for handling exceptions.