| Firebird 2.5 Language Reference → Firebird 2.5 Language Reference → Transaction Control → Transaction Statements |
|
|
|
Table of Contents
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
Table of Contents
Used for: Configuring and starting a transaction
Available: DSQL, 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 9.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 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.
The main parameters of a transaction are:
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.
The two database access modes for transactions are READ WRITE and READ ONLY.
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.
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.
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 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.
![[Note]](file:/C:/Program%20Files/Oxygen%20XML%20Editor%2016/frameworks/docbook/css/img/note.png) |
Autonomous Transactions |
|---|---|
|
Changes made by autonomous transactions are not seen in the context of the SNAPSHOT transaction that launched it. |
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 of data pending in any database table before a transaction with the SNAPSHOT TABLE STABILITY isolation level is started, trying to start a SNAPSHOT TABLE STABILITY transaction will result in an exception.
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.
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.
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.
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 |
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.
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 9.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:
![[Tip]](file:/C:/Program%20Files/Oxygen%20XML%20Editor%2016/frameworks/docbook/css/img/tip.png) |
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. |
Table of Contents
Used for: Committing a transaction
Available: DSQL, ESQL
Syntax:
COMMIT [WORK] [TRANSACTION tr_name]
[RELEASE] [RETAIN [SNAPSHOT]];
Table 9.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.
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 RETAIN [SNAPSHOT] clause is used for the “soft”, 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 also: SET TRANSACTION, ROLLBACK
Table of Contents
Used for: Rolling back a transaction
Available: DSQL, ESQL
Syntax:
ROLLBACK [WORK] [TRANSACTION tr_name]
[RETAIN [SNAPSHOT] | TO [SAVEPOINT] sp_name | RELEASE]
Table 9.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.
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 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 also: SET TRANSACTION, COMMIT
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:
See also: SAVEPOINT
Used for: Creating a savepoint
Available: DSQL
Syntax:
SAVEPOINT sp_name
Table 9.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 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 also: ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT
Used for: Erasing a savepoint
Available: DSQL
Syntax:
RELEASE SAVEPOINT sp_name [ONLY]
Table 9.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 also: SAVEPOINT
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 |
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:
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. |
|
| Firebird 2.5 Language Reference → Firebird 2.5 Language Reference → Transaction Control → Transaction Statements |