Selecting unlocked rows? [message #282376] |
Wed, 21 November 2007 13:53  |
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 #282393 is a reply to message #282377] |
Wed, 21 November 2007 20:49   |
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 #283713 is a reply to message #283700] |
Wed, 28 November 2007 00:38   |
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  |
 |
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
|
|
|