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?