Topic
  • 2 replies
  • Latest Post - ‏2014-03-17T15:35:34Z by Junius
Junius
Junius
21 Posts

Pinned topic Update fails with SQL0100 but row is in the table

‏2014-03-13T18:23:41Z |

UPDATE fails about 1% of the time with a "row not found" (SQLCODE = 100).

Here's the scenario;  program A is writting a row to a table (a log file) every few seconds and at roughly the same time program B is updating the same rows (probably a fews seconds later).  99% of the time the update is successful.  In the cases where the update fails, we've verified the "row not found" IS in the table. Each row inserted will get updated twice (in rapid succession).  It's only the first UPDATE that fails. Therefore, I'm wondering if it could be some sort of locking issue.  We do not use any commitment control at all. 

Our machine is on 7.1 with the latest PTFs.  Both programs are RPG ILE.  The commitment control parm of the CRTSQLRPGI command is *NONE.

Here's the INSERT statement in program A:

Exec SQL Insert intoMQToDomLog (Msg_ID, Q_name, Put_time, Arrival_Time, Delivery_Time, Msg_Type, Exception_Code)

                      values(:MDMID, :qname, :put_timest, :Initial_tstmp,

                                      :Initial_tstmp, :DOMMsgType, ' ');

 

Here's the UPDATE statement in program B:

Exec SQL Update MQTODOMLOG

                         set ARRIVAL_TIMESTAMP = :wk_timest

                    whereMQ_MESSAGE_ID = :MDCID;

The table has an index built over the MQ_MESSAGE_ID field.

What are the possible reasons for the UPDATE failing in this scenario?

Thanks,

Jay

 

 

  • krmilligan
    krmilligan
    450 Posts
    ACCEPTED ANSWER

    Re: Update fails with SQL0100 but row is in the table

    ‏2014-03-14T14:34:51Z  

    Assuming the index is being used for the searched Update, I'm guessing it's just a timing issue where the Insert hasn't completed yet.  How are your programs guaranteeing that the Insert completes before the Update?  Is it possible to use an After Insert Trigger to do the Update processing instead of relying on a separate job?  

    If timing is the cause seems like you should add retry logic to the first Update. 

  • krmilligan
    krmilligan
    450 Posts

    Re: Update fails with SQL0100 but row is in the table

    ‏2014-03-14T14:34:51Z  

    Assuming the index is being used for the searched Update, I'm guessing it's just a timing issue where the Insert hasn't completed yet.  How are your programs guaranteeing that the Insert completes before the Update?  Is it possible to use an After Insert Trigger to do the Update processing instead of relying on a separate job?  

    If timing is the cause seems like you should add retry logic to the first Update. 

  • Junius
    Junius
    21 Posts

    Re: Update fails with SQL0100 but row is in the table

    ‏2014-03-17T15:35:34Z  

    Assuming the index is being used for the searched Update, I'm guessing it's just a timing issue where the Insert hasn't completed yet.  How are your programs guaranteeing that the Insert completes before the Update?  Is it possible to use an After Insert Trigger to do the Update processing instead of relying on a separate job?  

    If timing is the cause seems like you should add retry logic to the first Update. 

    Thank you Kent,  we were also thinking along the lines of a retry.  I put in a 2 second delay (when the problem occurs) followed by a retry of the UPDATE and it seems to be working just fine.

    We couldn't go the route of a after-insert trigger since the updates are based on a notification from another process (a separate event).

    Thanks,

    Jay