FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 Context VariablesSecurity 
SNAPSHOT TABLE STABILITY Isolation Level

The SNAPSHOT TABLE STABILITY — or SNAPSHOT TABLE — 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 for this transaction.Other transactions can 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).

The SNAPSHOT TABLE STABILITY isolation level is also known as “consistency”.

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 needs to be executed again, whilst still in the uncommitted READ COMMITTED transaction.

Variants of READ COMMITTED

One of three modifying parameters can be specified for READ COMMITTED transactions, depending on the kind of conflict resolution desired: READ CONSISTENCY, RECORD_VERSION or NO RECORD_VERSION.When the ReadConsistency setting is set to 1 in firebird.conf (the default) or in databases.conf, these variants are effectively ignored and behave as READ CONSISTENCY.Otherwise, these variants are mutually exclusive.

  • NO RECORD_VERSION (the default if ReadConsistency = 0) 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.

    • with NO WAIT specified, it will throw a lock conflict error immediately

    • with WAIT specified, it will wait until the other transaction is either committed or 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.

  • With READ CONSISTENCY specified (or ReadConsistency = 1), the execution of a statement obtains a snapshot of the database to ensure a consistent read at the statement-level of the transactions committed when execution started.

    The other two variants can result in statement-level inconsistent reads as they may read some but not all changes of a concurrent transaction if that transaction commits during statement execution.For example, a SELECT COUNT(*) could read some, but not all inserted records of another transaction if the commit of that transaction occurs while the statement is reading records.

    This statement-level snapshot is obtained for the execution of a top-level statement, nested statements (triggers, stored procedures and functions, dynamics statements, etc.) use the statement-level snapshot created for the top-level statement.

Note

Obtaining a snapshot for READ CONSISTENCY is a very cheap action.
Caution

Setting ReadConsistency is set to 1 by default in firebird.conf.
Handling of Update Conflicts with READ CONSISTENCY

When a statement executes in a READ COMMITTED READ CONSISTENCY transaction, its database view is retained in a fashion similar to a SNAPSHOT transaction.This makes it pointless to wait for the concurrent transaction to commit, in the hope of being able to read the newly-committed record version.So, when a READ COMMITTED READ CONSISTENCY transaction reads data, it behaves similarly to a READ COMMITTED RECORD VERSION transaction: it walks the back versions chain looking for a record version visible to the current snapshot.

When an update conflict occurs, the behaviour of a READ COMMITTED READ CONSISTENCY transaction is different from READ COMMITTED RECORD VERSION.The following actions are performed:

  1. Transaction isolation mode is temporarily switched to READ COMMITTED NO RECORD VERSION.

  2. A write-lock is taken for the conflicting record.

  3. Remaining records of the current UPDATE/DELETE cursor are processed, and they are write-locked too.

  4. Once the cursor is fetched, all modifications performed since the top-level statement was started are undone, already taken write-locks for every updated/deleted/locked record are preserved, all inserted records are removed.

  5. Transaction isolation mode is restored to READ COMMITTED READ CONSISTENCY, a new statement-level snapshot is created, and the top-level statement is restarted.

This algorithm ensures that already updated records remain locked after restart, they are visible to the new snapshot, and could be updated again with no further conflicts.Also, due to READ CONSISTENCY nature, the modified record set remains consistent.

Note

  • This restart algorithm is applied to UPDATE, DELETE, SELECT WITH LOCK and MERGE statements, with or without the RETURNING clause, executed directly by a client application or inside a PSQL object (stored procedure/function, trigger, EXECUTE BLOCK, etc).

  • If an UPDATE/DELETE statement is positioned on an explicit cursor (using the WHERE CURRENT OF clause), then the step (3) above is skipped, i.e. remaining cursor records are not fetched and write-locked.

  • If the top-level statement is selectable and update conflict happens after one or more records were returned to the client side, then an update conflict error is reported as usual and restart is not initiated.

  • Restart does not happen for statements executed inside autonomous blocks (IN AUTONOMOUS TRANSACTION DO …​).

  • After 10 unsuccessful attempts the restart algorithm is aborted, all write locks are released, transaction isolation mode is restored to READ COMMITTED READ CONSISTENCY, and an update conflict error is raised.

  • Any error not handled at step (3) above aborts the restart algorithm and statement execution continues normally.

  • UPDATE/DELETE triggers fire multiple times for the same record if the statement execution was restarted and the record is updated/deleted again.

  • Statement restart is usually fully transparent to client applications and no special actions should be taken by developers to handle it in any way.The only exception is the code with side effects that are outside the transactional control, for example:

    • usage of external tables, sequences or context variables

    • sending e-mails using UDF or UDR

    • usage of autonomous transactions or external queries

    and so on.Beware that such code could be executed more than once if update conflicts happen.

  • There is no way to detect whether a restart happened, but it could be done manually using code with side effects as described above, for example using a context variable.

  • Due to historical reasons, error isc_update_conflict is reported as the secondary error code, with the primary error code being isc_deadlock.
NO AUTO UNDO

The NO AUTO UNDO option affects the handling of record versions (garbage) produced by the transaction in the event of rollback.With NO AUTO UNDO flagged, the ROLLBACK statement marks the transaction as rolled back without deleting the 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.

RESTART REQUESTS

According to the Firebird sources, this will

Restart all requests in the current attachment to utilize the passed transaction.

— src/jrd/tra.cpp

The exact semantics and effects of this clause are not clear, and we recommend you do not use this clause.

AUTO COMMIT

Specifying AUTO COMMIT enables auto-commit mode for the transaction.In auto-commit mode, Firebird will internally execute the equivalent of COMMIT RETAIN after each statement execution.

Caution

This is not a generally useful auto-commit mode;the same transaction context is retained until the transaction is ended through a commit or rollback.In other words, when you use SNAPSHOT or SNAPSHOT TABLE STABILITY, this auto-commit will not change record visibility (effects of transactions that were committed after this transaction was started will not be visible).

For READ COMMITTED, the same warnings apply as for commit retaining: prolonged use of a single transaction in auto-commit mode can inhibit garbage collection and degrade performance.
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.

Note
Historical Note

IGNORE LIMBO surfaces the TPB parameter isc_tpb_ignore_limbo, available in the API since InterBase times and is mainly used by gfix.
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.

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

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

Note

In Embedded SQL, the USING clause can be used to conserve system resources bylimiting the number of databases a transaction can access.USING is mutually exclusive with RESERVING.A USING clause in SET TRANSACTION syntax is not supported in DSQL.
See also

[fblangref50-transacs-commit], [fblangref50-transacs-rollback]

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]

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.