Chapter 10. Transaction 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.1. Transaction 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.1. `SET 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.1. SET TRANSACTION Statement Parameters

Parameter	Description
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 2³¹-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.1. Transaction 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.1. Transaction 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.2. Access 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.3. Lock 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.1. `WAIT` 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.2. `NO WAIT` Mode

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

10.1.1.1.4. Isolation 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.1. `SNAPSHOT` 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.2. `SNAPSHOT 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.3. `READ 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.5. `NO 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.6. `IGNORE 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.7. `RESERVING`

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.1. Options 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.2. Compatibility 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.

10.1.2. `COMMIT`

Used forCommitting a transaction

AvailableDSQL, ESQL

Syntax

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

Table 10.3. COMMIT Statement Parameter

Parameter	Description
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.1. `COMMIT` 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.

10.1.3. `ROLLBACK`

Used forRolling back a transaction

AvailableDSQL, ESQL

Syntax

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

Table 10.4. ROLLBACK Statement Parameters

Parameter	Description
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.1. `ROLLBACK` 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.

10.1.3.1.1. `ROLLBACK 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.4. `SAVEPOINT`

Used forCreating a savepoint

AvailableDSQL

Syntax

SAVEPOINT sp_name

Table 10.5. SAVEPOINT Statement Parameter

Parameter	Description
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

10.1.5. `RELEASE SAVEPOINT`

Used forErasing a savepoint

AvailableDSQL

Syntax

RELEASE SAVEPOINT sp_name [ONLY]

Table 10.6. RELEASE SAVEPOINT Statement Parameter

Parameter	Description
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.6. Internal 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.7. Savepoints 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.

Prev	Up	Next
9.23. `USER`	Home	Chapter 11. Security