Does using ROWNUM=1 gaurantee the same row is returned?
in [Oracle Help]
|
Prev: Triggers
Next: oracle,blobs,cluster big app
From: AudioFanatic on 8 Jul 2010 18:22 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.
From: Thomas Kellerer on 9 Jul 2010 02:30 AudioFanatic, 09.07.2010 00:22: > 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. 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 > If you can answer with respect to the database isolation level if it > relates to the answer, please be as specific as possible. I don't think this relates to the isolation in any way. At least not the SELECT Regards Thomas
From: Vladimir M. Zakharychev on 9 Jul 2010 04:30 On Jul 9, 2:22 am, AudioFanatic <boogerbotto...(a)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
From: joel garry on 9 Jul 2010 12:32 On Jul 8, 3:22 pm, AudioFanatic <boogerbotto...(a)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 -- @home.com is bogus. http://www.signonsandiego.com/news/2010/jul/09/china-group-says-us-uses-facebook-to-sow-unrest/
From: Mark D Powell on 10 Jul 2010 09:26
On Jul 9, 4:30 am, "Vladimir M. Zakharychev" <vladimir.zakharyc...(a)gmail.com> wrote: > On Jul 9, 2:22 am, AudioFanatic <boogerbotto...(a)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 -- |