Home » SQL & PL/SQL » SQL & PL/SQL » Get (and lock) first unlocked row?!
Get (and lock) first unlocked row?! [message #119911] Mon, 16 May 2005 11:38 Go to next message
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 #119913 is a reply to message #119911] Mon, 16 May 2005 11:52 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
The first transaction:

SQL> select ename from emp where rownum = 1 for update skip locked;

ENAME
----------
SMITH


The second one:
SQL> select ename from emp where rownum <= 2 for update skip locked;

ENAME
----------
ALLEN


Rgds.
Re: Get (and lock) first unlocked row?! [message #119914 is a reply to message #119911] Mon, 16 May 2005 11:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
icon14.gif  Re: Get (and lock) first unlocked row?! [message #119939 is a reply to message #119911] Mon, 16 May 2005 16:48 Go to previous messageGo to next message
emirc
Messages: 7
Registered: May 2005
Junior Member
Thank you very very much!
This helps a lot.
I have some more questions.
I will try this, but maybe you already now.
Since this lockMe() procedure is in an autonomous transaction could I have some issues with real concurrent access to records?
And another question is - it seems that FOR UPDATE is not allowed on queries with ORDER BY.
Even if I ORDER BY rows in an inner query, I still get ORA-something that I can not lock these rows.
I'll post exact query and message tomorrow, when I' back at work...

Anyway, thanks a lot!

BTW, it's very sad that 'almighty' Oracle doesn't provide for convenient way to do this...
Probably their experts will say that this you don't need this and if you need - buy Oracle Advanced Queueing.
I don't need anything fancy, I just need what can be done with a single simple query in MS SQL Server...
I always had a great respect for Oracle, but this is really disappointing.

Re: Get (and lock) first unlocked row?! [message #119996 is a reply to message #119939] Tue, 17 May 2005 02:51 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
FOR UPDATE clause has certain restrictions in using. See there:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2065648

Rgds.
Re: Get (and lock) first unlocked row?! [message #679363 is a reply to message #119939] Wed, 26 February 2020 00:29 Go to previous messageGo to next message
P_K
Messages: 1
Registered: February 2020
Junior Member
Hi facing same problem here. Any luck ?
Re: Get (and lock) first unlocked row?! [message #679364 is a reply to message #679363] Wed, 26 February 2020 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67138
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Create a new topic instead of hijacking a very old topic: go to http://www.orafaq.com/forum/f/1/ and click on "New topic" button up-right.
Specify clearly and in details what is your actual problem.
Provide code and a test case we can reproduce.

[Updated on: Wed, 26 February 2020 01:38]

Report message to a moderator

Re: Get (and lock) first unlocked row?! [message #679368 is a reply to message #679363] Wed, 26 February 2020 06:54 Go to previous message
EdStevens
Messages: 1206
Registered: September 2013
Senior Member
P_K wrote on Wed, 26 February 2020 00:29
Hi 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.
Previous Topic: Single update to get one column value using another column value
Next Topic: regexp_replace (merged 2)
Goto Forum:
  


Current Time: Sat May 30 17:43:12 CDT 2020