Re: Procedure - how to lock some records in table

On Aug 13, 8:12 am, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

How about SELECT...FOR UPDATE OF in your cursor select.

That would only lock the records you're selecting. I am thinking that you're not selecting all the rows in the table, unless your select criteria is true for all the rows.

Check this out.

Roger Gorden
Senior DBA John Wiley and Sons, Inc. Received on Wed Aug 13 2008 - 08:41:46 CDT