Re: multiple threads select rows with row locking

"richard" <rgparkins_at_hotmail.com> wrote in message news:84dcd46.0304101250.140df948_at_posting.google.com...
> 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.

Hi Richard

It's not clear (to me anyway) why/what you are trying to do, so bear with me.

Am I right in assuming each of these "threads" represents a 'SELECT FOR UPDATE' which subsequently updates it's allotted set of rows. You're definitely not performing unnecessary locking with these reads, right ?

>
> 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?

No. If these threads run in separate sessions then they will be locked out if accessing rows that are already locked. I however have a strange feeling that unnecessary locking is taking place. Let's just confirm a few fundamentals here. Normal "selects" (without the for update) are non locking, do not prevent writes from occurring and are guaranteed to see a consistent view of the data. Writes only lock out (by default) other writes *on the same row* and do not lock out these normal, non-locking selects.

That being the case, why are these threads locking each other out. Why can't the predicates (or the conditions that determine the set of data to be manipulated by a thread) be such that they don't "overlap".

Like I said, I don't quite understand what's going on but I sense a design flaw here somewhere.

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

A "locked" row can be (is) deleted from the point of view of the session performing the delete but it will always be "visible" to other sessions until the commit is performed. Oracle will not allow "dirty reads" as such so a select will only look at committed data unless the session itself has made the modification.

Again, I question the question. Why would you not want to "see" a row that has not yet been deleted.

>
> Starting to annoy me. Thanks for any responses.

Don't get annoyed, get even. Make a cup of tea and have a good read of Oracle's locking and read consistency model in the Concepts manual. Once you fully understand how this works, I'm sure the solution to these issues of yours will fall out with a thud.

Good Luck

Richard Received on Fri Apr 11 2003 - 07:26:20 CDT