+ Reply to Thread
Results 1 to 8 of 8

Procedure - how to lock some records in table

  1. 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

  2. Re: Procedure - how to lock some records in table

    On Aug 13, 4:47*am, PiotreK 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.

  3. Re: Procedure - how to lock some records in table

    On Aug 13, 8:12*am, hpuxrac wrote:
    > On Aug 13, 4:47*am, PiotreK 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.

  4. Re: Procedure - how to lock some records in table

    On 13 Sie, 15:41, "rogergorden@....gmail.com"
    wrote:

    > 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.


    The decision is not to use any lock... The next attempt will be
    probably SELECT ... FOR UPDATE, but now I wrote something like this:
    Niestety, decyzja na razie jest taka, żeby nie używać blokad, o ile to
    możliwe. Zastosowałem zatem coś takiego:

    --################################################################

    --user_id is set.
    my_rec_group_id := NULL;

    --condition 1
    BEGIN
    EXECUTE IMMEDIATE
    'SELECT REC_GROUP_ID FROM table_name WHERE ROWNUM = 1 AND
    TAKEN=0 AND' || some_conditions_string
    INTO
    my_rec_group_id;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN chain_id := NULL;
    END;

    IF ( my_rec_group_id IS NOT NULL ) THEN
    EXECUTE IMMEDIATE
    'UPDATE table_name SET TAKEN = 1, USER_ID = :1 WHERE
    REC_GROUP_ID = :2'
    USING
    user_id, my_rec_group_id;
    COMMIT;
    END IF;

    IF ( my_rec_group_id IS NULL )
    --condition 2 (just like condition one but with other
    some_conditions_string)
    END IF;

    --next conditions....

    --final
    IF ( my_rec_group_id IS NOT NULL ) THEN
    alreadyStolen := 1;
    EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM table_name WHERE TAKEN=1 AND USER_ID <> :
    1'
    USING
    user_id;

    IF ( alreadyStolen= 0 ) THEN
    --load the cursor
    OPEN my_cursor FOR 'SELECT * FROM table_name WHERE REC_GROUP_ID=
    ' || TO_CHAR(my_rec_group_id);

    --check again
    alreadyStolen:= 1;
    EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM table_name WHERE TAKEN=1 AND USER_ID
    <> :1'
    USING
    user_id;

    IF ( alreadyStolen= 0 ) THEN
    --cursor goes to the application
    RETURN;
    ELSE
    CLOSE my_cursor;
    --no return - in real procedure this means next execution of
    the whole code (WHILE loop).
    END IF;

    END IF;
    ELSE
    -- REC_GROUP_ID is not found at all
    RETURN;
    END IF;

    --################################################################

    Again in some performance test by grinder the same group sometimes is
    taken by two different users... I don't have a clue why it is like
    this...

    Thank you in advance for comments,
    PiotreK

  5. Re: Procedure - how to lock some records in table

    On Aug 13, 7:49*am, PiotreK wrote:
    > On 13 Sie, 15:41, "rogergorden@....gmail.com"
    > wrote:
    >
    > > 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.

    >
    > The decision is not to use any lock... The next attempt will be
    > probably SELECT ... FOR UPDATE, but now I wrote something like this:
    > Niestety, decyzja na razie jest taka, żeby nie używać blokad, o ileto
    > możliwe. Zastosowałem zatem coś takiego:
    >
    > --################################################################
    >
    > --user_id is set.
    > my_rec_group_id := NULL;
    >
    > --condition 1
    > BEGIN
    > * *EXECUTE IMMEDIATE
    > * * * 'SELECT REC_GROUP_ID FROM table_name WHERE ROWNUM = 1 AND
    > TAKEN=0 AND' || some_conditions_string
    > * *INTO
    > * * * my_rec_group_id;
    > EXCEPTION
    > * *WHEN NO_DATA_FOUND THEN chain_id := NULL;
    > END;
    >
    > IF ( my_rec_group_id IS NOT NULL ) THEN
    > * *EXECUTE IMMEDIATE
    > * * * 'UPDATE table_name SET TAKEN = 1, USER_ID = :1 WHERE
    > REC_GROUP_ID = :2'
    > * *USING
    > * * * user_id, my_rec_group_id;
    > * *COMMIT;
    > END IF;
    >
    > IF ( my_rec_group_id IS NULL )
    > --condition 2 (just like condition one but with other
    > some_conditions_string)
    > END IF;
    >
    > --next conditions....
    >
    > --final
    > IF ( my_rec_group_id IS NOT NULL ) THEN
    > * *alreadyStolen := 1;
    > * *EXECUTE IMMEDIATE
    > * * * 'SELECT COUNT(*) FROM table_name WHERE TAKEN=1 AND USER_ID <> :
    > 1'
    > * *USING
    > * * * user_id;
    >
    > * *IF ( alreadyStolen= 0 ) THEN
    > * * * --load the cursor
    > * * * OPEN my_cursor FOR 'SELECT * FROM table_name WHERE REC_GROUP_ID=
    > ' || TO_CHAR(my_rec_group_id);
    >
    > * * * --check again
    > * * * alreadyStolen:= 1;
    > * * * EXECUTE IMMEDIATE
    > * * * * *'SELECT COUNT(*) FROM table_name WHERE TAKEN=1 AND USER_ID
    > <> :1'
    > * * * USING
    > * * * * *user_id;
    >
    > * * * IF ( alreadyStolen= 0 ) THEN
    > * * * * *--cursor goes to the application
    > * * * * *RETURN;
    > * * * ELSE
    > * * * * *CLOSE my_cursor;
    > * * * * *--no return - in real procedure this means next execution of
    > the whole code (WHILE loop).
    > * * * END IF;
    >
    > * *END IF;
    > ELSE
    > * *-- REC_GROUP_ID is not found at all
    > * *RETURN;
    > END IF;
    >
    > --################################################################
    >
    > Again in some performance test by grinder the same group sometimes is
    > taken by two different users... I don't have a clue why it is like
    > this...


    Please read the concepts manual about when a transaction begins. You
    have two different users taking the group because the two users each
    have a read consistent view of the database as it exists when their
    transaction starts, which says the group is available. If you select
    for update, the second one will not be able to update because he will
    be locked out. Your code needs to handle that.

    >
    > Thank you in advance for comments,
    > PiotreK


    jg
    --
    @home.com is bogus. "If you notice, since Britney started wearing
    clothes and behaving; Paris is out of town not bothering anybody
    anymore, thank God, and evidently, Lindsay Lohan has gone gay, we
    don't seem to have much of an issue." - Los Angeles Police Chief
    William Bratton



  6. Re: Procedure - how to lock some records in table

    On 13.08.2008 18:27, joel garry wrote:
    > On Aug 13, 7:49 am, PiotreK wrote:
    >> On 13 Sie, 15:41, "rogergorden@....gmail.com"
    >> wrote:


    >> Again in some performance test by grinder the same group sometimes is
    >> taken by two different users... I don't have a clue why it is like
    >> this...

    >
    > Please read the concepts manual about when a transaction begins. You
    > have two different users taking the group because the two users each
    > have a read consistent view of the database as it exists when their
    > transaction starts, which says the group is available. If you select
    > for update, the second one will not be able to update because he will
    > be locked out. Your code needs to handle that.


    Another remark: I don't see the need for all this dynamic SQL. Also, it
    might be that the whole thing can be solved with one (a single UPDATE)
    or two (SELECT ... FOR UPDATE and then UPDATE) SQL statements doing set
    operations.

    Btw, I spotted a missing "THEN" - so that code seems to not even be
    syntactically correct.

    Kind regards

    robert

  7. Re: Procedure - how to lock some records in table

    On 13 Sie, 23:01, Robert Klemme wrote:
    > On 13.08.2008 18:27, joel garry wrote:
    >
    > > On Aug 13, 7:49 am, PiotreK wrote:
    > >> On 13 Sie, 15:41, "rogergorden@....gmail.com"
    > >> wrote:
    > >> Again in some performance test by grinder the same group sometimes is
    > >> taken by two different users... I don't have a clue why it is like
    > >> this...

    >
    > > Please read the concepts manual about when a transaction begins. *You
    > > have two different users taking the group because the two users each
    > > have a read consistent view of the database as it exists when their
    > > transaction starts, which says the group is available. *If you select
    > > for update, the second one will not be able to update because he will
    > > be locked out. *Your code needs to handle that.

    >
    > Another remark: I don't see the need for all this dynamic SQL. *Also, it
    > might be that the whole thing can be solved with one (a single UPDATE)
    > or two (SELECT ... FOR UPDATE and then UPDATE) SQL statements doing set
    > operations.
    >
    > Btw, I spotted a missing "THEN" - so that code seems to not even be
    > syntactically correct.
    >
    > Kind regards
    >
    > * * * * robert


    The whole code is simplified version and not tested - the 'INTO' part
    is also missing from some of the EXECUTE IMMEDIATE statements. Dynamic
    language is also needed (that's right - you can't see it on this
    example...). I will try to do my best and I will write the effects
    tomorrow. I think proper use of SELECT ... FOR UPDATE will succeed.

    Thanks for instructions,
    PiotreK

  8. Re: Procedure - how to lock some records in table

    SELECT ... FOR UPDATE OF ... NOWAIT with proper EXCEPTION (WHEN
    USER_DEFINED_ORA_00054 THEN result := 'rows locked'; - something like
    this) handle it well.

    This action is moved to the subprocedure, and the conditions and
    result manageing is in the main procedure.
    Thanks for hints ;)

    PiotreK

+ Reply to Thread