Example of Manual Recovery

Home | Previous Page | Next Page Distributed Data > Recovering Manually from Failed Two-Phase Commit >

Example of Manual Recovery

This example illustrates the kind of work that is involved in manual recovery. The following SQL statements were executed by user nhowe. Error -698 was returned.

dbaccess
CREATE DATABASE tmp WITH LOG;
CREATE TABLE t (a int);
CLOSE DATABASE;
CREATE DATABASE tmp@apex WITH LOG;
CREATE TABLE t (a int);
CLOSE DATABASE;
DATABASE tmp;
BEGIN WORK;
INSERT INTO t VALUES (2);
INSERT INTO tmp@apex:t VALUES (2);
COMMIT WORK;
### return code -698

The following excerpt is taken from the logical log at the current database server:

addr      len   type      xid      id   link      
.....
17018    16         CKPOINT  0       0     13018    0
 
18018    20         BEGIN    2       1     0        08/27/91 10:56:57
   3482     nhowe

1802c    32         HINSERT  2       0     18018    1000018  102     
   4
  
1804c    40         CKPOINT  0       0     17018    1   

   begin    xid    id addr     user

   1        2          1  1802c    nhowe

19018    72         BEGPREP  2       0     1802c   6d69    1

19060    16         COMMIT   2       0     19018   08/27/91 11:01:38

1a018    16         ENDTRANS 2       0     19060   580543

The following excerpt is taken from the logical log at the database server apex:

addr      len   type      xid      id   link      
.....
16018    20         BEGIN    2             1   0        08/27/91 
   10:57:07 3483     pault

1602c    32         HINSERT  2       0     16018    1000018  102   
   4  

1604c    68         PREPARE  2       0     1602c      eh

17018    16         HEURTX   2       0     1604c          1

17028    12         CLR      2       0     1602c   

17034    16         ROLLBACK 2       0     17018    08/27/91 11:01:22

17044    40         CKPOINT  0       0     15018    1       

      begin    xid      id addr     user
      1        2        1  17034    --------

18018    16         ENDTRANS 2       0     17034     8806c3
   ....

First, you would try to match the transactions in the current database server log with the transactions in the apex database server log. The BEGPREP and PREPARE log records each contain the GTRID. You can extract the GTRID by using onlog -l and looking at the data portion of the BEGPREP and PREPARE log records. The GTRID is offset 22 bytes into the data portion and is 68 bytes long. A more simple, though less precise, approach is to look at the time of the COMMIT or ROLLBACK records. The times should be close, although there is a slight delay because of the time taken to transmit the commit (or rollback) message from the coordinator to the participant. (This second approach lacks precision because concurrent transactions could commit at the same time although concurrent transactions from one coordinator would probably not commit at the same time.)

To correct this sample situation

Find all records that were updated.
Identify their type (insert, delete, update) using onlog and the table of record types.
Use the onlog -l output for each record to obtain the local xid, the tblspace number, and the rowid.
Map the tblspace number to a table name by comparing the tblspace number to the value in the partnum column of the systables system catalog table.
Using your knowledge of the application, determine what action is required to correct the situation.

In this example, the time stamps on the COMMIT and ROLLBACK records in the different logs are close. No other active transactions introduce the possibility of another concurrent commit or rollback. In this case, an insert (HINSERT) of assigned rowid 102 hex (258 decimal) was committed on the current database server. Therefore, the compensating transaction is as follows:

DELETE FROM t WHERE rowid = 258