6. Working with Transactions and Handling Errors : Two Phase Commit : Example: Using Two-Phase Commit
 
Share this page                  
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 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;