Locking Problem

[CHALAVADI]

I have a pl/sql code which runs a cursor looking for a particular status_cd column in table A.
In order to speed up response time we are running same code
from 4 java threads . In the code we open the cursor and fetch a row and and retreiving the primary key and we are querying the same table that was used in the cursor and we are locking that row by using SELECT FOR UPDATE NOWAIT.

By this we want to ensure that all 4 processes run in parallel and pick up a seperate row from the result set and so that we can finish processing fairly quick.

We are commiting only after the processing the locked row, so that status_cd will be changed and lock will be released.

But for some reason eventhough I am locking the row the other processes are picking up the same row at exactly same time. How can ensure that no other process picks up the same row which I have locked using SELECT FOR UPDATE NOWAIT.

Please Advise

[svk]

make sure that your cursor definition is not doing a full scan...

[CHALAVADI]

My cursor is selecting only 50 rows at a time.

[svk]

"How many" is not important. "How" is important.

Can you please post you cursor def ? Also the indexes on your table ?

[gpsingh]

select for update makes the process to run serially and not parallel,
until the first process is commited or rolled back the next process shall not run.
How r u making sure that each thread is accessing different set of rows, and i think since u r
updating the primary key , the differentitation shall be dificult.