"fetch out of sequence" following failover to secondary replicated server

From: Will Potter <whpotter_at_bellsouth.net>
Date: Sat, 9 Oct 2004 02:26:55 -0500
Message-ID: <enM9d.60751$DV3.22519@bignews5.bellsouth.net>

The scenario: Oracle 9i replicated databases, same C++ server process runs on both boxes. Once a week, the primary box is rebooted and clients are redirected to the secondary. The next night, the secondary bounces and clients move back to primary.

The problem happens when a client performs an insert on the primary prior to the boot and then another client tries to retrieve the record from the secondary. This errors occurs infrequently (ie not every weekend) and only one record is ever effected. The last time this occurred there was a period of 13 minutes from the successful insert on the primary to the failed select on the secondary.

Here is the code that is getting the error:

        exec sql at :instancename declare d cursor for
        select * from data where key1=:KEYIOkey1:IND and 

key2=:KEYIOkey2:IND;

        exec sql open d;
        exec sql whenever not found do break;

        //set newval to empty string



        // Look at all records where key1 + key2 are found

        for (;;) {
                exec sql fetch d into
                :DUMMYSEQ, :DUMMYTS,
                :DATAIOuser_id:IND, :DATAIOkey1:IND, :DATAIOkey2:IND, 

:DATAIOvalue:IND;

                if (sqlca.sqlcode) {
                        // log error here !!!!!
                        exec sql close d;
                        throw DBException;
                }

                // if newval is still empty, set newval to DATAIOvalue

                // if field user_id matches user_id break out early
                if (user_id == TNIOuser_id){
                        break;
                }
        }



        // at this point we either have exact match on key1, key2 and 

user_id


        // or we have the first record that matches on key1 and key2 only


        sqlcode = sqlca.sqlcode;    // save the current sqlcode for error 
reporting
        exec sql close d;

        .


        // do some more work with data

        .

Received on Sat Oct 09 2004 - 02:26:55 CDT