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

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Sat, 10 Jul 2010 06:26:12 -0700 (PDT)
Message-ID: <ec251a39-ffce-4afa-8644-9ae5c3bc2ea2_at_k39g2000yqd.googlegroups.com>



On Jul 9, 4: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 -

Audio, you would however not want to use the SERIALIZABLE transaction isolation level as applications which require this isolation level do not scale well. If you want to make sure you update the same row select the PK or Rowid and use it in the update statement. That or perform a select for update.

The point would not be moot either if there were no concurrent updates possible. That is if ther are no updates while the first process is running there is no guarentee that Oracle will read the table blocks in the same order if one or more were already in the buffer pool when the process started. So even if Oracle seems to return the data in the same order every time there would be no guarentee that such behavior will not change with an upgrade. You want to code SQL so it will always work. Oracle says heads are unordered collection of rows so if you need a specific order specify an order by. If you need a specific row use the PK or Rowid. Note - Rowid's can change so testing that the row is the same row selected earlier may be necessary.

HTH -- Mark D Powell -- Received on Sat Jul 10 2010 - 08:26:12 CDT

Original text of this message