Multi-Database Transactions - Firebird

Firebird supports operations across multiple databases under the control of a single transaction. It implements two-phase commit (2PC) automatically, to ensure that the transaction will not commit the work in one database unless it is possible to commit it in the others. Data is never left partly updated.

In the first phase of a two-phase commit or rollback, Firebird prepares to commit (or roll back) the work for each database by splitting the transaction into sub-transactions, one for each database, and posting the changes to each. At this point, the sub-transactions are all in a “transient” state. If the first phase completes, then the second phase flags each sub-transaction for committing or rolling back, as the case may be, in the order in which the parts were prepared.

  • If it is a commit and any sub-transaction cannot be committed, an exception occurs. Any sub-transactions so far flagged for commit revert to “transient” and database state is unchanged in all cases.
  • If the commit succeeds throughout, then all sub-transactions go into “committed” state and changes become permanent.
  • If it is a rollback, the sub-transactions go into rolled-back state.

Limbo Transactions

If network interruption or a disk crash makes one or more databases unavailable, causing the two-phase commit to fail during the second phase, sub-transactions left behind will be in a transient state, flagged as neither committed nor rolled back. Within each individual database, these sub-transactions that never completed the second phase (became committed or rolled back) are recognized as being in a “limbo” state.

Because rows in a database sometimes become inaccessible because of their association with a transaction that is “in limbo,” it is important to resolve these transactions.

Recovery

Until a limbo transaction is finished (by being committed or rolled back) it remains “interesting” to Firebird, which keeps statistics on unfinished transactions. Recovering a limbo transaction means committing it or rolling it back. The gfix tool can recover limbo transactions and let you deal with them interactively.

Restricting Databases

Cross-database transactions can use a lot of server resources. In Embedded SQL (ESQL), Firebird provides language support in the form of the USING clause, for optionally limiting the databases a transaction is permitted to access. DSQL does not provide language support. DSQL interfaces can use special API structures in the transaction parameter block for limiting multi-database transactions in various ways. Some data access component classes provide access to these options through properties.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics