Re: Does using ROWNUM=1 gaurantee the same row is returned?
From: joel garry <joel-garry_at_home.com>
Date: Fri, 9 Jul 2010 09:32:33 -0700 (PDT)
Message-ID: <2977d7ba-a261-4059-8b8d-174a1e212ce7_at_n19g2000prf.googlegroups.com>
On Jul 8, 3:22 pm, AudioFanatic <boogerbotto..._at_yahoo.com> wrote:
> 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.
Date: Fri, 9 Jul 2010 09:32:33 -0700 (PDT)
Message-ID: <2977d7ba-a261-4059-8b8d-174a1e212ce7_at_n19g2000prf.googlegroups.com>
On Jul 8, 3:22 pm, AudioFanatic <boogerbotto..._at_yahoo.com> wrote:
> 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.
Besides what you are asking, if the table is substantial, you will likely have a performance issue if you don't understand nulls and indices. It may show up as CPU thrashing, as blocks remain hot in memory because of scanning all the not null flow status to get to the stopkey. Or not, depending. Whatever you wind up with, be sure to test with a decent projected data set under load.
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2010/jul/09/china-group-says-us-uses-facebook-to-sow-unrest/Received on Fri Jul 09 2010 - 11:32:33 CDT