| 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.
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
