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.

AudioFanatic, 09.07.2010 00:22:
You are right. As you are not using an ORDER BY in the SELECT, the order of the rows is not guaranteed, and thus the row that is returned is not defined when using rownum = 1

If you have a criteria to sort by you could use something like this:

SELECT *
FROM (
select *
from EVENT_TABLE
where I_EAI_FLOW_STATUS is NULL
ORDER BY some_unique_column
)
WHERE ROWNUM=1


I don't think this relates to the isolation in any way. At least not the SELECT

Regards
Thomas

On Jul 8, 3:22*pm, AudioFanatic <boogerbotto... (AT) yahoo (DOT) com> wrote:
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
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...to-sow-unrest/