The Logical Context - Firebird

The simple way to look at a transaction between START TRANSACTION and COMMIT or ROLLBACK is to view it as a series of client operations and client/ server interactions that map exactly to a task. It is a very useful model for understanding how a transaction wraps a unit of work. It does not necessarily reflect the reality of how users perform the actual task.

From the user’s point of view, the “task” is not bounded by START TRANSACTION and COMMIT. Her task has a beginning, a middle, and an end that may involve multiple transactions to reach completion. For example, a failure to post or to commit a statement will entail a rollback to end the physical transaction. Some form of intervention follows that may cause the logical task to end or, in the normal case, will require another physical transaction in order to complete it.

A single physical transaction may involve a number of discrete user tasks forming a logical “whole” that requires the atomicity of a single physical transaction. Another variant—the typical “batch data entry” task—encompasses many repetitions of a similar task stacked up inside a single physical transaction to reduce keystrokes and comply with the user’s workflow requirements.

In summary, the logical task—the one we as developers must address and design for—almost always extends beyond the boundaries of START TRANSACTION and COMMIT. The physical transaction is but one facet of the logical context in which it is conducted.

Two key factors of concern in the logical context of transaction workflow are

  • How to retain the physical context of the initial transaction after ROLLBACK so that users’ work does not disappear when the server refuses to accept it
  • What to do if the flow is interrupted by an exception—how to diagnose the exception and how to correct it

To address these problems, we look at the COMMIT and ROLLBACK operations and the pros and cons of options available to preserve logical context between transactions. Following on from there, we examine the issue of diagnosing the exceptions that give rise to the need to “rerun” transactions.

Ending Transactions

A transaction ends when the client application commits it or rolls it back. Unless the COMMIT statement, or the call to the equivalent API function isc_commit_transaction, succeeds, the transaction is not committed. If a transaction that cannot be committed is not rolled back by an explicit client call to ROLLBACK (or the API function isc_rollback_transaction) the transaction is not rolled back. These statements are not the syllogism that they appear to be. Failure to terminate transactions is an all-too-common feature of problem scenarios in the support lists!

The COMMIT Statement

The syntax of the COMMIT statement is simple:


A simple COMMIT—sometimes referred to as a hard commit, for reasons that will become evident—makes changes posted to the database permanent and frees up all of the physical resources associated with the transaction. If COMMIT fails for some reason and returns an exception to the client, the transaction remains in an uncommitted state. The client application must handle the failure to commit by explicitly rolling back the transaction or, where possible, by fixing the problems and resubmitting the statement.

COMMIT with the RETAIN Option

The optional RETAIN [SNAPSHOT] extension to the COMMIT statement causes the server to retain a “snapshot” of the physical transaction’s context at the time the statement is executed and start a new transaction as a clone of the committed one. If this so-called soft commit is used on a SNAPSHOT or SNAPSHOT TABLE STABILITY transaction, the cloned transaction preserves the same snapshot of the data as the original transaction had when it started.

Although it does commit the work permanently and thus change the state of the database, COMMIT RETAIN (Commit Retaining) does not release resources. In the life-span of a logical task that comprises many repetitions of a similar operation, cloning the context reduces some of the overhead that would be incurred by clearing resources each time with COMMIT, only to allocate the identical resources all over again when a new transaction is started. In particular, it preserves the cursors on sets selected and currently “open.”

The same TID remain active in the TSB and never appears there as “committed.” For this reason, it is often referred to as soft commit, in contrast with the “hard” commit performed by an unmodified COMMIT statement.

Each soft commit is like a savepoint with no return. Any subsequent ROLLBACK reverses only the changes that have been posted since the last soft commit.

The benefit of the soft commit is that it makes life easy for programmers, especially those using components that implement “scrolling dataset” behavior. It was introduced to support the data grid user interface favored by many users of the Borland Delphi development environment. By retaining the transaction context, the application can display a seamless before -to-after transition that reduces the effort the programmer would otherwise need to invest in starting new transactions, opening new cursors, and resynchronizing them with row sets buffered on the client.

Data access implementations frequently combine posting a single update, insert or delete statement with an immediate COMMIT RETAIN in a mechanism that is dubbed “Autocommit.” It is common for interface layers that implement Autocommit capability to “dumb out” explicit control of transactions by starting one invisibly in situations where the programmer -written code attempts to pass a statement without first starting a transaction itself.

Explicit transaction control is worth the extra effort, especially if you are using a connectivity product that exploits the flexible options provided by Firebird. In a busy environment, the COMMIT RETAIN option can save time and resources, but it has some serious disadvantages:

  • A snapshot transaction continues to hold the original snapshot in its view, meaning the user does not see the effects of committed changes from other transactions that were pending at the start of the transaction.
  • As long as the same transaction continues being committed with RETAIN, resource “housekeeping” on the server is inhibited, resulting in excessive growth of memory resources consumed by the TSB. This growth progressively slows down performance, eventually “freezing” the server and, under adverse operating system conditions, even causing it to crash.
  • No old record versions made obsolete by operations committed by a COMMIT RETAIN transaction can be garbage collected as long as the original transaction is never “hard committed.”

The ROLLBACK Statement

Like COMMIT, ROLLBACK frees resources on the server and ends the physical context of the transaction. However, the application’s view of database state reverts to the way it would be if the transaction had never started. Unlike COMMIT, it never fails.

In the logical context of “the task” on the client, after rollback your application will provide the user with the means to resolve the problem that caused the exception and to try again in a new transaction.


Firebird SQL does not implement a RETAIN syntax for ROLLBACK as it does for COMMIT. However, a similar cloning mechanism is implemented at the API with the function isc_rollback_retaining(). It restores the application’s database view to the state it was in when the transaction handle was acquired or, in a ReadCommitted transaction, to the state it was in the last time isc_rollback_retaining was called. System resources allocated to the transaction are not released and cursors are retained.

Rollback Retaining has the same traps as COMMIT RETAIN—and one more. Since any rollback call is done in response to an exception of some sort, retaining the context of the transaction will also retain the cause of the exception. Rollback Retaining should not be used if there is any chance that your subsequent exception-handling code will not find and fix the inherent exception. Any failure subsequent to Rollback Retaining should be handled with a full rollback, to release the resources and clear the problem.

Diagnosing Exceptions

Common causes for failure to post or commit work include

  • Lock conflicts
  • “Bad data” that slipped through the user interface: arithmetic overflows, expressions that divide by zero, nulls in non-nullable fields, mismatched character sets, and so on
  • “Good data” that fails a CHECK constraint or other validation
  • Primary and foreign key violations and others!

    Firebird recognizes a comprehensive (nay, mind-boggling!) range of exceptions and provides error codes to identify them at two levels.

  • At the higher-level is the SQLCODE, defined (more or less, with emphasis on “less”) by the SQL standards.
  • At the more detailed level is the GDSCODE, a larger and more precisely targeted, vendor-specific range of exception types that are grouped beneath the SQLCODE types.


Table shows the values that the SQL-89 and SQL-92 standards define for SQLCODE.

SQLCODE Values and Definitions

SQLCODE Values and Definitions

The mapping of negative SQLCODEs to actual errors is not defined by the standards. Firebird’s negative SQLCODEs are quite generalized and provide groupings that are largely coincidental and often bemusing. For example, while it is possible to guess that an SQLCODE of –204 means “something unknown,” the code on its own tells nothing about what is unknown.


The second-level GDSCODEs provide much better targeting of the actual exception. Each GDSCODE is a signed integer that is mapped to a constant in iberror.h (in your /include subdirectory) and also to a message text string in firebird.msg (or interbase.msg if you are using v.1.0.x). As a rule, the GDSCODE message is quite precise about what occurred.

Firebird 1.5 delivered significant improvements in the information returned by messages. Nevertheless, the GDSCODE provides a highly useful diagnostic mechanism for applications, and you can map the constants to custom messages in your own host-code module, for use by exception handlers.

Receiving Exceptions

In the following example, an application makes an attempt to insert a row into a table that does not exist:


The following set of error information is returned to the application (the IB_SQL admin utility, in this case):

Dynamic SQL Error <- corresponding text from firebird.msg
SQL error code = -204 <- SQLCODE
Table unknown <- corresponding text from firebird.msg

Where does the application get the error codes and messages from? The answer is found in the error status vector, an array that is passed as a parameter in most of the API functions. These functions return status and error codes to the client, along with the corresponding strings from the Firebird message file. The API also provides client applications with utility functions to read the contents of the error status vectors into local buffers. Exception handlers can then parse the contents of these buffers and use the information to decide what to do about the exception and to deliver a friendly message to the user.


The header file iberror.h, in your /include directory, contains the declarations that associate each SQLCODE and GDSCODE with a symbolic constant. For example, here are the constant declarations for the two error codes from the preceding example:

... #define isc_dsql_error 335544569L ... #define isc_dsql_relation_err 335544580L <- an SQLCODE -204 error

Most of the established high-level language and scripting host interfaces already have translations of the constant declarations, although some pre-date Firebird and may not be open source. If you need a translation, it is recommended that you inquire in the support lists. The full list of SQLCODE and GDSCODE codes and the standard English messages can be found.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics