Two Phase Commit

Two phase commit is a mechanism that enables an application managing multiple connections to ensure that committal of a distributed transaction occurs in all concerned databases. This maintains database consistency and integrity.

Statements that Support Two Phase Commit

SQL provides the following two statements that support two phase commit functionality:

• Prepare to Commit

  The prepare to commit statement allows the coordinator application to poll each local DBMS to determine if the local DBMS is ready to commit the local transaction associated with the specified distributed transaction. Using this statement, the coordinator application can ensure that a distributed transaction is committed if and only if all of the local transactions that are part of the distributed transaction are committed. When the prepare to commit statement successfully completes, the local transaction is in a willing commit state.

• Connect

  The connect statement, when specified with the distributed transaction ID, provides the means for a coordinator application to re-connect to a local DBMS, if the original connection was severed for any reason, for the purposes of committing or aborting a local transaction associated with the specified distributed transaction. When a local transaction is in the willing commit state, the coordinator application controls further processing of that transaction.

Both the prepare to commit and the connect statements make use of the distributed transaction ID, an 8-byte integer that must be supplied by the coordinator application. The distributed transaction ID must be a unique number. The local DBMS returns an error to the coordinator application if a specified distributed transaction ID is not unique within the local DBMS.

VMS: Two phase commit is not supported for VMS cluster installations.

Coordinator Applications for a Two Phase Commit

To use a two phase commit, coordinator applications are used. The coordinator application is responsible for:

• Generating a unique distributed transaction ID for each distributed transaction, and passing this ID to each concerned local DBMS.
• Performing the necessary logging and recovery tasks to handle any failure occurring during the transaction processing. This includes logging the following information:
  • Distributed transaction IDs
  • The states of all the slave transactions

If the connection between a coordinator application and a local DBMS breaks while a distributed transaction is still open, the action taken by the local recovery process depends on the state of the local transaction associated with the distributed transaction:

• If the local transaction is not in a willing commit state, the local DBMS aborts the transaction.
• If the local transaction is in a willing commit state, the local transaction is not aborted until the connection is re-established by the coordinator application and the transaction is committed or rolled back.

If the connection between a coordinator application and a local DBMS breaks, use the connect statement to re-establish the connection with the local DBMS and transaction. If the local DBMS has rolled back the local transaction associated with the distributed transaction, the DBMS returns an error statement indicating this when issuing the connect statement. Otherwise, after reconnecting, a commit or a rollback statement can be issued to close the transaction.

If a local DBMS encounters a log file full condition and the oldest transaction is a local transaction that is associated with a distributed transaction and is in the willing commit state, the local logging system does not abort the local transaction. Normally the logging system aborts the oldest transactions first. For details about transaction logging, see the Database Administrator Guide.

Manual Termination of a Distributed Transaction

To terminate a local transaction associated with a distributed transaction, use the logstat utility to obtain the local transaction ID of the transaction. With this ID, use the utility lartool to manually terminate the transaction.

Lartool is a simple command-line utility that allows the commit or rollback of the transaction identified by the local transaction ID.

For more information about the logstat and lartool utilities, see the Database Administrator Guide.

Example: Using Two-Phase Commit

The following is an example of a two-phase commit used in a banking application. It illustrates the use of the prepare to commit and connect SQL statements.

exec sql begin declare section;

     from_account      integer;

     to_account        integer;

     amount            integer;

     high              integer;

     low               integer;

     acc_number        integer;

     balance           integer;

exec sql end declare section;

define               SF_BRANCH 1

define               BK_BRANCH 2

define               BEFORE_WILLING_COMMIT 1

define               WILLING_COMMIT 2

exec sql whenever sqlerror stop;

/* Connect to the branch database in S.F */

exec sql connect annie session :SF_BRANCH;

Program assigns value to from_account, to_account, and amount

/* Begin a local transaction on S.F branch to

** update the balance in the from_account. */

exec sql update account

     set balance = balance - :amount

     where acc_number = :from_account;

/* Connect to the branch database in Berkeley. */

exec sql connect aaa session :BK_BRANCH;

/* Begin a local transaction on Berkeley branch
** to update the balance in the to_account. */

exec sql update account
     set balance = balance + :amount
     where acc_number = :to_account;

/* Ready to commit the fund transfer transaction. */
/* Switch to S.F branch to issue the prepare to
** commit statement. */

exec sql set_sql (session = :SF_BRANCH);

/* Store the transaction's state information in a
** file */

store_state_of_xact(SF_BRANCH,
     BEFORE_WILLING_COMMIT, high, low, "annie");

exec sql prepare to commit with highdxid = :high,
     lowdxid = :low;

/* Store the transaction's state information in a
** file */
store_state_of_xact(SF_BRANCH, WILLING_COMMIT,
     high, low, "annie");

/* Switch to Berkeley branch to issue the prepare
** to commit statement. */

exec sql set_sql (session = :BK_BRANCH);

/* Store the transaction's state information in a
** file */

store_state_of_xact(BK_BRANCH,
     BEFORE_WILLING_COMMIT, high, low, "annie");

exec sql prepare to commit with highdxid = :high,
     lowdxid = :low;

/* Store the transaction's state information in a
** file */

store_state_of_xact(BK_BRANCH, WILLING_COMMIT,
     high, low, "annie");

/* Both branches are ready to COMMIT; COMMIT the
** fund transfer transaction. */
/* Switch to S.F branch to commit the local
** transaction. */

exec sql set_sql (session = :SF_BRANCH);

exec sql commit;

/* Switch to Berkeley branch to commit the local
** transaction. */

exec sql set_sql (session = :BK_BRANCH);

exec sql commit;

/* Distributed transaction complete */
/* Switch to S.F branch to verify the data. */

exec sql set_sql (session = :SF_BRANCH);

exec sql select acc_number, balance
     into :acc_number, :balance
     from account;
exec sql begin;

     print (acc_number, balance);

exec sql end;

/* Switch to Berkeley branch to verify the data. */

exec sql set_sql (session = :BK_BRANCH);

exec sql select acc_number, balance
     into :acc_number, :balance
     from account;
exec sql begin;

     print (acc_number, balance);

exec sql end;

/* Exit the S.F database. */

exec sql set_sql (session = :SF_BRANCH);
exec sql disconnect;

/* Exit the Berkeley database. */

exec sql set_sql (session = :BK_BRANCH);
exec sql disconnect;

This portion of the example shows how the information logged in the procedure store_state_of_xact is used for recovery after a system failure at either branch.

The first part of the recovery process is to read the state of each transaction from information logged by store_state_of_xact. If either state is in BEFORE_WILLING_COMMIT, the program connects to the specific transaction in both databases and executes a rollback. Although the local DBMS can roll back the transaction, the recovery process reconnects to the specific transaction. This occurs because a prepare to commit has been sent, received, and acted upon, but a crash occurred before the acknowledgment was received by the coordinator application.

If both states are in WILLING_COMMIT, the program connects to the specific transactions and commits them:

exec sql begin declare section;

     high               integer;

     low                integer;

     session1           integer;

     session2           integer;

     dbname1            character_string(25);

     dbname2            character_string(25);

exec sql end declare section;

/* Read information saved by store_state_of_xact */

read_from_file(address(session1),

     address(session2),

     address(dbname1), address(dbname2),

     address(high), address(low));

/* Assume that a global flag has been set to

** commit or rollback based on the information

** in the file */

if (flag = 'COMMIT') then

     exec sql connect :dbname1 session :session1

          with highdxid = :high, lowdxid = :low;

     exec sql commit;

     exec sql disconnect;

exec sql connect :dbname2 session :session2

          with highdxid = :high, lowdxid = :low;

     exec sql commit;

     exec sql disconnect;

else

     exec sql connect :dbname1 session :session1

          with highdxid = :high, lowdxid = :low;

     exec sql rollback;

     exec sql disconnect;

exec sql connect :dbname2 session :session2

          with highdxid = :high, lowdxid = :low;

     exec sql rollback;

     exec sql disconnect;

endif;