Re: Does using ROWNUM=1 gaurantee the same row is returned?
Date: Fri, 9 Jul 2010 01:30:59 -0700 (PDT)
Message-ID: <94fc13ce-46a1-489b-9ce3-0a9d7dc0a895_at_5g2000yqz.googlegroups.com>
On Jul 9, 2:22 am, 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.
ROWNUM without ORDER BY does not guarantee the same row would be returned on every query execution at default READ COMMITTED isolation level. Quick test case:
create table event_table
(evt_id number(10) primary key,
i_eai_flow_status varchar2(30)
)
/
Table created.
insert into event_table values(1, null); commit;
Session 1:
select * from event_table where i_eai_flow_status is null and rownum=1
ID I_EAI_FLOW_STATUS
------------ ----------------- 1 <NULL>
Session 2:
insert into event_table values (2, null); commit;
Session 1:
update event_table set i_eai_flow_status='In Progress' where i_eai_flow_status is null and rownum = 1;
commit;
select * from event_status where i_eai_flow_status is null and rownum=1;
ID I_EAI_FLOW_STATUS
------------ ----------------- 1 <NULL>
Oops... In this case (default statement level READ COMMITTED,) wrong row was updated (the most recently inserted one.) However, when I explicitly start a new SERIALIZABLE transaction in session 1 correct row is updated every time (because rows committed in other sessions are not visible to session 1 at this isolation level.) In explicit READ COMMITTED isolation level transaction wrong row is being updated in my database, too, but there's no guarantee it will happen everywhere all the time. Oracle simply does not guarantee the order of rows without explicit ORDER BY, though sometimes it is predictable.
Obviously, one might argue that SERIALIZABLE would do the trick here
and for the given scenario it will. It might cause concurrency issues
though. The easiest way to make sure the same row is updated is to
SELECT ROWID, T.* FROM EVENT_TABLE T
WHERE I_EAI_FLOW_STATUS IS NULL AND ROWNUM=1
FOR UPDATE -- lock the row and start the tx
followed by
UPDATE ... SET ... WHERE ROWID = ...
or use a PL/SQL cursor for SELECT ... FOR UPDATE
followed by UPDATE WHERE CURRENT OF <cursor>
Both will work correctly at any tx isolation level.
However, I would recommend that you look into Advanced Queuing: since you work with events AQ seems to be the natural choice for sending and processing them asynchronously.
Hth,
Vladimir M. Zakharychev Received on Fri Jul 09 2010 - 03:30:59 CDT