Re: Procedure - how to lock some records in table

On Aug 13, 4:47 am, PiotreK <piotr.kna..._at_gmail.com> wrote:
> 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

Sounds potentially like more of a database design problem than a plsql problem. Certainly locking a whole table is not a scalable approach.

Can you include in your reply the details ( DDL ) of the tables that are involved and the procedures you are using.

Also please give us some more details of how the application is coded, what run time environment is being used, any middle ware complications/ etc. Received on Wed Aug 13 2008 - 07:12:38 CDT