FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 Context VariablesSecurity 

COMMIT

Commits a transaction

Available in

DSQL, ESQL

Syntax
COMMIT [TRANSACTION tr_name] [WORK]
  [RETAIN [SNAPSHOT] | RELEASE];
Table 1. 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 TRANSACTION and RELEASE clauses are only valid in ESQL.

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 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 a new transaction is restarted transparently with the same Transaction ID.The state of row caches and cursors remains 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 does not update to reflect changes by other transactions, and the user of the application instance continues to have the same view as when the original transaction started.Changes made during the life of the retained transaction are visible to that transaction, of course.

Note
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

[fblangref50-transacs-settransac], [fblangref50-transacs-rollback]

docnext count = 7

ROLLBACK

Rolls back a transaction or to a savepoint

Available in

DSQL, ESQL

Syntax
  ROLLBACK [TRANSACTION tr_name] [WORK]
    [RETAIN [SNAPSHOT] | RELEASE]
| ROLLBACK [WORK] TO [SAVEPOINT] sp_name
Table 1. 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 TRANSACTION and RELEASE clauses are only valid in ESQL.The ROLLBACK TO SAVEPOINT statement is not available in ESQL.

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 keyword RETAIN keyword specifies that, although all 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

[fblangref50-transacs-settransac], [fblangref50-transacs-commit]

ROLLBACK TO SAVEPOINT

The ROLLBACK TO SAVEPOINT 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 specified 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 are not notified and will 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 also

[fblangref50-transacs-savepoint], [fblangref50-transacs-releasesp]

SAVEPOINT

Creates a savepoint

Syntax
SAVEPOINT sp_name
Table 1. SAVEPOINT Statement Parameter
Parameter Description

sp_name

Savepoint name.Available only in DSQL

The SAVEPOINT statement creates an SQL-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.Savepoints are sometimes called “nested transactions”.

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

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

Note
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 [fblangref50-transacs-releasesp] 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

[fblangref50-transacs-rollback-tosavepoint], [fblangref50-transacs-releasesp]

RELEASE SAVEPOINT

Releases a savepoint

Syntax
RELEASE SAVEPOINT sp_name [ONLY]
Table 1. 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

[fblangref50-transacs-savepoint]

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, you can set this with the TPB flag isc_tpb_no_auto_undo.

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