Re: multiple threads select rows with row locking

Hi Richard

Thanks for your reply, (and everyone else), as usual time was of the eseence and consequently to sort out the problem, I created a procedure which every thread called to select rows from the table. This procedure updated a flag to indicate that the row had been "acquired", and therefore any subsequent selects do not select rows that have already been acquired. Although this does not quite fall into the "fudge" category.. it works!!

I will take your advice and look at the Oracle manual with regards locking as I am by no means an Oracle expert as you can see.

To summarise what I was trying to acheive:

1. 1 Process populating a table at 200 rows a second.
2. Multiple processes selecting rows from the table, at a rate of 10 a second.
3. There must be no duplicate rows selected between threads.
4. Each thread has its own connection to the database.

What I found:

1. Using the "select for update nowait" worked if I set autocommit to false, however there were duplicate rows selected.
2. Using the "select for update nowait" with autocommit on saw the connection hanging, with an exception thrown "no more data.."

Again thanks for the advice, I shall be spending Easter in the garden with a beer and "Oracle for the afflicted".

As an aside, I am also trying to increase the rate at which I can process these messages (10 a second is not good enough, thus the multiple threading). The process of each row calls a procedure which has multiple triggers which call other procedures. It seems that processing each row takes a LONG time (100 msec for each insert), and batching does not help. I am booked to go on a Oracle database tuning course to see if I can increase performance, I hope this will point me in the right direction.

Cheers

Richard

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<4jyla.11926$1s1.187913_at_newsfeeds.bigpond.com>...
> "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 Tue Apr 15 2003 - 03:24:39 CDT