Re: Does using ROWNUM=1 gaurantee the same row is returned?

From: AudioFanatic <boogerbottom68_at_yahoo.com>
Date: Tue, 13 Jul 2010 09:23:32 -0700 (PDT)
Message-ID: <4582b5c3-677d-4f98-9366-8144a995837e_at_s9g2000yqd.googlegroups.com>

On Jul 9, 1:30 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Thanks to all who responded. This is exactly the information that I needed. Received on Tue Jul 13 2010 - 11:23:32 CDT