Procedure - how to lock some records in table

Hello,

I've got an PL/SQL (Oracle) problem.
I've got a procedure that logic is to search for clean record (let it be TAKEN=0) that fulfits some kind of criteria. Easy. There is 5 sets of conditions checked one by one to get the record group. When the record is find (if one criteria success - rest of the criterias are skipped) the record group ID is taken (let's name it REC_GROUP_ID). Then all of the records with REC_GROUP_ID are updated (no one else can take these records now, let's say UPDATE table_name SET TAKEN=1 WHERE REC_GROUP_ID = found_id) and loaded into cursor (and then go to the application...).

I've done the performace tests with grinder and I came out that the same sets of records (with the same REC_GROUP_ID) are taken by different threads (one thread is one user). How can I preserve the founded records (with REC_GROUP_ID) in my procedure? Locking of a whole table is bad attempt (procedure called by application would be too slow). How can I do it to save good performace of the application?

I'm not sure how to construct some kind of a good transaction within procedure - the things I've tested failed...

Thank you in advance,
PiotreK Received on Wed Aug 13 2008 - 03:47:55 CDT