Re: multiple threads select rows with row locking

rgparkins_at_hotmail.com (richard) wrote:
> Hello,
>
> I am developing an application that has multiple threads accessing a
> single database table. The table is being populated at a high rate,
> therefore each thread will perfom a SELECT of a limited amount of
> rows.
>
> How do I get each thread to select rows that have not been locked. The
> first thread runs then any subsequent thread has the Exception
> generated to indicate that rows have been locked. Can I not presume
> that each thread will select rows that have not been locked without
> throwing an exception?

There is a (undocumented?) "skip locked" feature for select for update, but it probably won't work for what you want. That's because even skipped records are counted in the rownum. It sounds like your selects are using the same where criteria with a rownum cap. If the first select locks the first 1000 rows it sees, the next select will probably see the same thousand (and then stop because rownum is satisfied), but since it skipped those 1000 it will not actually return anything.

> Also can the locked row be deleted without performing a commit or
> rollback?

What on earth does that mean? You can't do much of anything to the database without a commit.

> Starting to annoy me. Thanks for any responses.

--claim your rows
update t1 set marker=:thread_id where marker='ready' and rownum<=100; commit;

select * from t1 where marker=:thread_id --do whatever time consuming stuff you want here, then mark them done update t1 set marker='finished' where marker=:thread_id; commit;

If a thread pukes, something has to clean up after it (by changing the marker column from the decedents thread_id back to 'ready')

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB