Re: Does using ROWNUM=1 gaurantee the same row is returned?
From: Thomas Kellerer <OTPXDAJCSJVU_at_spammotel.com>
Date: Fri, 09 Jul 2010 08:30:22 +0200
Message-ID: <89ntvuFcvaU1_at_mid.individual.net>
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.
Date: Fri, 09 Jul 2010 08:30:22 +0200
Message-ID: <89ntvuFcvaU1_at_mid.individual.net>
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
Received on Fri Jul 09 2010 - 01:30:22 CDT