Transactions and MGA - Firebird

MGA, an acronym for multi-generational architecture, refers to the architectural model underlying Firebird’s database state management.

In the MGA model, each row stored in the database holds the unique transaction ID of the transaction that writes it. If another transaction posts a change to the row, the server writes a new version of the row to disk, with the new transaction ID, and converts an image of the older version into a reference (known as the delta) to this new version. The server now holds two “generations” of the same row.

Post vs. COMMIT

The term “post” has probably been borrowed from older desktop accounting software as an analogy to the posting of journals in accounting systems. The analogy is useful for distinguishing the two separate operations of writing a reversible change to the database (by executing a statement) and committing all of the changes executed by one or several statements. Posted changes are invisible beyond the boundaries of their transaction context and can be rolled back. Committed changes are permanent and become visible to transactions that are subsequently started or updated.

If any conflict occurs upon posting, the server returns an exception message to the client and the commit fails. The application must then deal with the conflict according to its kind. The solution to an update conflict is often to roll back the transaction, causing all of the work to be “undone” as an atomic unit. If there is no conflict, the application can proceed to commit the work when it is ready.


Rollback never fails. It will undo any changes that were requested during the transaction—the change that caused the exception as well as any that would have succeeded had the exception not occurred.

Some rolled-back transactions do not leave row images on the disk. For example, any rollbacks instigated by the server to undo work performed by triggers simply vanish, and the record images created by inserts are normally expunged during rollback, with reference to an auto-undo log that is maintained for inserts. If a single transaction inserts a huge number of records, the auto-undo log is abandoned, with the result that a rollback will leave the images on disk. Other conditions that may cause inserted record images to remain on disk include server crashes during the operation or use of a transaction setting that explicitly requests “no auto-undo.”

Row Locking

Under MGA, the existence of a pending new version of a row has the effect of locking it. In most conditions, the existence of a committed ewer version blocks a request to update or delete the row as well—a locking conflict.

On receiving a request to update or delete, the server inspects the states of any transactions that “own” newer versions of the row. If the newest of those owner transactions is active or has been committed, the server responds to the requesting transaction according to the context (isolation level and lock resolution parameters) of the requesting transaction.

If the newest version’s transaction is active, the requesting transaction will, by default, wait for it to be completed (committed or rolled back), and then the server will allow it to proceed. However, if NOWAIT was specified, it returns a conflict exception to the requesting transaction.

If the newest version’s transaction is committed and the requesting transaction is in SNAPSHOT (i.e., concurrency) isolation, the server refuses the request and reports a lock Conflict. If the transaction is in READ COMMITTED isolation, with the default RECORD_VERSION setting, the server allows the request and writes a new record version on behalf of the transaction.

Firebird does not use conventional two-phase locking at all for the most usual transaction contexts. Hence, all normal locking is at the row level and is said to be optimistic —each row is available to all read-write transactions until a writer creates a newer version of it.

Upon a successful commit, the old record version referenced by the delta image becomes an obsolete record.

  • If the operation was an update, the new image becomes the latest committed version and the original image of the record, with the ID of the transaction that last updated it, is made available for garbage collection.
  • If the operation was a deletion, a “stump” replaces the obsolete record. A sweep or a backup clears this stump and releases the physical space on disk that was occupied by the deleted row.

    In summary, under normal conditions:

  • Any transaction can read any row that was committed before it started.
  • Any read-write transaction can request to update or delete a row.
  • A request (post) will usually succeed if no other read-write transaction has already posted or committed a change to a newer version of the record. Read-committed transactions are usually allowed to post changes overwriting versions committed by newer transactions.
  • If a post succeeds, the transaction has a “lock” on the row. Other readers can still read the latest committed version, but none will succeed in posting an update or delete statement for that row.

Table-Level Locks

A transaction can be configured to lock whole tables. There are two acceptable ways to do this in DSQL: by isolating the transaction in SNAPSHOT TABLE STABILITY (aka consistency mode, forced repeatable read) or by table reservation. It should be emphasized that these configurations are for when unusually pre-emptive conditions are required. They are not recommended in Firebird for everyday use.

An unacceptable way to impose a table-level lock (Firebird 1.5 and later) is to apply a statement-level pessimistic lock that affects the whole table. A statement such as the following can do that:


It is not, strictly speaking, an issue of transaction configuration. However, the configuration of the transaction that owns sets retrieved with this explicit pessimistic lock is important.


There are no deltas or locks for inserts. If another transaction has not pre-empted inserts by a table-level lock, an insert will always succeed if it does not violate any constraints or validation checks.

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

Firebird Topics