Multiple database transactions.

    IB not only allows concurrent transaction but it allows transactions that span more than one database, provided that these databases are local to the IB server receiving the request.

This feature has been at least since IB 4.0 and it allows a kind of limited local distributed transaction control, so you can commit or rollback changes made to more than one database as if they were applied to a unique database. Interestingly, SqlServer only recently introduced multi database transactions, in the version 7, probably to support replication.

For that purpose, IB has a special system table named RDB$TRANSACTIONS that's only to keep track of multi-database transactions. ("Normal" one-database transactions are neither tracked here nor in any system table.) The idea is simple: a commit should succeed on all participating databases or it will be cancelled. Sometimes, due to a system crash, there may be debris transactions that weren't committed on all databases so they must be cleaned because they were aborted. The administration utility (named Server Manager up to version 5.X) has an option "Transaction recovery" or "Clear transactions in limbo" to help when multi database transactions go out of sync due to a system crash.

Under normal operations, a commit that succeeds on all databases covered by that transaction is marked as done but if at least one participant database rejects the commit, then all other affected databases are rolled back automatically. When a rollback is requested by the client, all databases under that transaction will rollback immediately. The system table mentioned above is to keep track of these intermediate steps, when there are databases that still need to apply or undo changes. After normal operations with multi-database transactions have finished, all databases have their rdb$transactions system table empty again. This technique is known as two-phase commit, because the server first prepares all databases involved to commit and when all are ready, it does the commit sequentially but logically as one unit, so if anything goes awry, a rollback happens on all those databases.

IB provides special API calls to deal with multi database operations. Only IBX, IBO and the raw API are able to work with these facilities. Keep in mind that these operations are directed by the user through a client application: IB cannot use declarative referential integrity spanning two or more databases and also, triggers and stored procedures are not allowed to make references to any database outside of the one where they are defined and compiled. In theory, an UDF can make such calls, but UDFs are discouraged from doing database connections; their purpose is mainly to extend the Spartan set of native functions and return quickly to the caller and not to fiddle with databases, transactions or persistent memory.

Please, don't confuse multi-database transactions with heterogeneous queries: in IB, you cannot produce a "select" statement involving tables in more than one database or an insert in a table that's fed by a select on a table in another database. You can make heterogeneous queries with the BDE but they are not updateable, they are read only. So, what's the usefulness of these multi database transactions? Simply put, the client application decides what data travels from one database to another through such client. Sometimes, it might be possible to work with two sets of data and to make changes using only the extant data plus new information entered interactively by the user. In this case, data from every db goes to the client, is used in a disjoint way (no interaction among each db's data) and receives interactive modifications. But the point might be that all data modified by the user should be accepted on all databases or discarded at all and here the multi-db transaction will ensure the proper result. Another case is moving data from one db to a second db: the data must be selected from the first db, acquired by the client, inserted in the second db and then deleted from the first one. That operation should be committed only if all selected data was inserted on the target db and all selected data was erased from the source db and all commits were accepted. Doing this is the own code is adding unnecessary complexity to the client application. A third example might be a selection of ranges or comboBoxes that are provided by one db while the other db feeds the information entered by client through the web. The interactive administrator then validates and corrects some data according to acceptable parameters that must be decided by a human operator. Sometimes, a new category is needed, so the reference database must be changed, too. At the end, changes to the reference data only have sense if the data from the web could be entered satisfactorily in the customers db, so a multi-db transaction can help.

This page was last updated on 2000-06-28 20:01:48