What is two phase commit?

By: Borland Staff

Abstract: Two phase commit refers to a transaction that can span two or more databases (which may be on different servers).

What is two phase commit?

Note: The information in this article applies to
            InterBase v5.x

Two phase commit refers to a transaction that can span 
two or more databases (which may be on different servers).  
Work done in the transaction of a multi-database transaction
is guaranteed to be committed in all databases or in none.

For example, if your app updates data in two databases, and 
then you commit, the two-phase commit prevents situations 
such that if one database becomes unavailable, your changes 
in one database are committed but in the other database the 
commit failed.

If any of the databases in the multi-database transaction 
fails to commit (roll back) the transaction, the transaction 
is left in a "limbo" state in all databases, and you can use 
GFIX or InterBase Server Manager for Windows to resolve 
the limbo transactions later.

Yes,  InterBase can support two-phase commits.

There are a number of ways they can be implemented.

You commit (or roll back) a multi-database transaction like 
you would a simple transaction.  The InterBase client library 
takes care of the two-phase commit transparently.

Can I implement two phase commit  via Delphi VCL / BDE 

BDE does not surface the InterBase feature of cross-database
transactions.  BDE manages a given transaction in the context 
of one database only.  Therefore, Delphi/JBuilder/C++Builder 
do not offer any means to use two-phase commits.

In BDE-based clients, you might have two data sources, 
each with their own transaction.  You can perform operations 
on both databases, but the transaction for each is 
independant of the other.

Can I access this capability via
     - Delphi , using InterBase API Calls? 
     - embedded SQL programs?

When you start a transaction with the API, you specify a list of open
database handles instead of just one.  With ESQL, by default all
transactions you start are multi-database, using all of the open
databases, unless you limit the list with the USING clause.

Refer to the documentation.  There are very complete sections and
examples on using cross-database transactions.  See ch. 5 of the API
Guide and ch. 4 of the Programmer's Guide.

source: Bill Karwin   
Manager of  Technical Publications 
InterBase Software Corporation

Server Response from: ETNASC04