| Get (and lock) first unlocked row?! [message #119911] |
Mon, 16 May 2005 11:38  |
emirc
Messages: 7
Registered: May 2005
|
Junior Member |
|
|
Hi experts,
I need a query to fetch the first unlocked row from a table for update.
I have a table where an application inserts data for my service to process it and my service has several threads that each tries to process a different request. Basically, it should be primitive queueing.
How to do this in Oracle easily?
Is there any 'translator' out there that can translate this T-SQL (MS SQL Server) query to Oracle dialect?
SELECT TOP 1 * FROM TableXY WITH(UPDLOCK, READPAST);
I'm a little bit disappointed with Oracle lacking such a feature. They wanna make me use AQ or what?!
I've tried this:
SELECT * FROM TableXY WHERE ROWNUM=1 FOR UPDATE SKIP LOCKED;
Problem is that the first transaction locks the record and the second transaction running the same query doesn't see anything (no rows...) even though there are a lot of rows there... even if I try with rownum=2 (although it's useless in my scenario), I get nothing in second transaction...
Thank you for any help...
Regards,
Emir
|
|
|
|
|
|
| Re: Get (and lock) first unlocked row?! [message #119914 is a reply to message #119911] |
Mon, 16 May 2005 11:58  |
emirc
Messages: 7
Registered: May 2005
|
Junior Member |
|
|
Thank you for your prompt answer, but there's no way for me to know what number to use at rownum<=X in which transaction.
Basically, I have n parallel threads executing the same query and 'dequeing' the rows one by one. It seems that it's impossible to do it simply as with MS SQL...
Anyway, thank you...
|
|
|
|
| Re: Get (and lock) first unlocked row?! [message #119918 is a reply to message #119914] |
Mon, 16 May 2005 12:39 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
There is one work-around, may be it can help
in your particular case:
SQL> create or replace
2 FUNCTION LOCKME(rid in rowid)
3 return number
4 is
5 ret_id number := 0;
6 pragma autonomous_transaction;
7 begin
8 select 1 into ret_id from emp where rowid = rid for update nowait;
9 rollback;
10 return 1;
11 exception
12 when others then
13 if sqlcode = -54 then
14 rollback;
15 return 0;
16 else
17 raise;
18 end if;
19 end;
20 /
Function created.
The first transaction:
SQL> select ename from emp where lockMe(rowid) = 1 and rownum = 1 for update skip locked;
ENAME
----------
SMITH
The second one:
SQL> select ename from emp where lockMe(rowid) = 1 and rownum = 1 for update skip locked;
ENAME
----------
ALLEN
The third:
SQL> select ename from emp where lockMe(rowid) = 1 and rownum = 1 for update skip locked;
ENAME
----------
WARD
Now the first one:
SQL> rollback;
Rollback complete.
And the forth one:
SQL> select ename from emp where lockMe(rowid) = 1 and rownum = 1 for update skip locked;
ENAME
----------
SMITH
Rgds.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Get (and lock) first unlocked row?! [message #679368 is a reply to message #679363] |
Wed, 26 February 2020 06:54 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
P_K wrote on Wed, 26 February 2020 00:29Hi facing same problem here. Any luck ?
Did you notice that you are asking this of a thread that is fifteen years old?
FWIW, my take upon reading it is that the OP was willing to jump through all sorts of hoops just to avoid using the mechanism that oracle provided (advanced queue) - because he sees everything through is Microsoft goggles.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
