FirebirdSQL logo
 Context VariablesSecurity 

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]

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.