Home » SQL & PL/SQL » SQL & PL/SQL » Selecting unlocked rows? (Oracle 9i and 10g/Linux)
Selecting unlocked rows? [message #282376] Wed, 21 November 2007 13:53 Go to next message
gthiruva
Messages: 9
Registered: November 2007
Junior Member
Well, my previous post on 'SELECT...FOR UPDATE' turned out to be quite exciting.

So here's another:

Can I create a SELECT statement WHERE clause that can select based on a row's lock status? In pseudocode:

SELECT col1, col2, col3 from mytable
  WHERE lock_status = unlocked AND ROWNUM <= 10
  FOR UPDATE OF col2, col3;


In my previous post, I had a stored function that ran 'SELECT ... FOR UPDATE' to lock some rows for update by a subsequent 'UPDATE' statement. If two processes call the stored function at about the same time, the first callee instance will lock the rows it needs. But the second callee instance will either abort when NOWAIT is used or be forced to WAIT until the first callee commits its changes.

If the first callee instance locks rows 1-10, I don't see why the second callee has to wait before it can get rows 11-20.

Now, after hunting around, I found an undocumented feature called "SKIP LOCKED" which will look something like:

SELECT col1, col2, col3 from mytable
  WHERE lock_status = unlocked AND ROWNUM <= 10
  FOR UPDATE OF col2, col3 NOWAIT SKIP LOCKED;


But apparently "SKIP LOCKED" is an undocumented feature that is not reccommended for use. Any thoughts or ideas for a workaround?

[Updated on: Wed, 21 November 2007 13:54]

Report message to a moderator

Re: Selecting unlocked rows? [message #282377 is a reply to message #282376] Wed, 21 November 2007 14:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please provide the business case where such a "requirement" is the only & best solution.

Just because something can be done, that does not mean it should be done.
Re: Selecting unlocked rows? [message #282392 is a reply to message #282376] Wed, 21 November 2007 20:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
"Skip locked" is documented as part of the "for update clause" in 11g:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#i2066346
Re: Selecting unlocked rows? [message #282393 is a reply to message #282377] Wed, 21 November 2007 20:49 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I would argue that this is a very tidy and terse solution to load-management in a multi-threaded batch job.

The "best" solution might be for a master thread to push rows onto a queue to be dequeued by different processing threads, but the code is more complex, difficult to test, and problematic in the event of failure.

The problem with undocumented features is not that they are likely to be changed without notice (they rarely do), its that Oracle won't look at an SR that uses them.

In the interests of thought-provoking discussion (not because I think it is a great idea), you could create a function that accepts a ROWID as a param and interface to the DBMS_LOCK package creating exclusive locks on resources named using the rowid passed.

The resulting SQL would look like:
SELECT a.*
FROM   my_table a
WHERE  lockit(a.rowid) = 'SUCCESS'


You could also throw in a FOR UPDATE clause to be tidy, but it's not necessary.

Ross Leishman
Re: Selecting unlocked rows? [message #283646 is a reply to message #282392] Tue, 27 November 2007 17:01 Go to previous messageGo to next message
gthiruva
Messages: 9
Registered: November 2007
Junior Member
Awesome. Then "SKIP LOCKED" it is!
Re: Selecting unlocked rows? [message #283700 is a reply to message #283646] Wed, 28 November 2007 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Internal only before 11g.
Don't use it in previous versions.

Regards
Michel
Re: Selecting unlocked rows? [message #283713 is a reply to message #283700] Wed, 28 November 2007 00:38 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Alternatively, use it, but make sure your Client / Employer is aware of the risks (to their support contract) of doing so.

Ross Leishman
Re: Selecting unlocked rows? [message #283723 is a reply to message #283713] Wed, 28 November 2007 00:55 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

to their support contract

And to their unreliability, unaccuracy, unavaibility, un... of their application.

In prior versions, code was written (and so tested) for one specific internal case and no other ones.

Regards
Michel
Previous Topic: Condition in query
Next Topic: Ora-0604
Goto Forum:
  


Current Time: Tue Feb 11 20:26:07 CST 2025