Re: Procedure - how to lock some records in table

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

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