Does using ROWNUM=1 gaurantee the same row is returned?
Date: Thu, 8 Jul 2010 15:22:25 -0700 (PDT)
Message-ID: <486a1e11-2658-4055-98cf-1ac996221828_at_s9g2000yqd.googlegroups.com>
We are using an integration broker (SAP PI) to poll an event table, then update the status of the polled event row. Currently, the following statements are being used:
Query to retrieve events: select * from EVENT_TABLE where
I_EAI_FLOW_STATUS is NULL AND ROWNUM=1
SQL to update status: update EVENT_TABLE set I_EAI_FLOW_STATUS = 'In
Process' where I_EAI_FLOW_STATUS is NULL AND ROWNUM=1
I don't think that this method would gaurantee that the same row originally queried is the same one that gets updated by the second statement. The two statements are allegedly in one transaction.
If you can answer with respect to the database isolation level if it relates to the answer, please be as specific as possible. If you can give examples of precisely when this would not work (like an earlier row is deleted from the table during the transaction), that would be helpful as well. I have the burden of proof to convince others that these statements are not sufficient if that is the case. In theory, no rows would be deleted from this table except during an archiving process, but new rows would be added at any time. Received on Thu Jul 08 2010 - 17:22:25 CDT