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;