Re: Procedure - how to lock some records in table

From: PiotreK <piotr.knapik_at_gmail.com>
Date: Wed, 13 Aug 2008 07:49:01 -0700 (PDT)
Message-ID: <839a4036-59e1-4e38-8339-80707f938c13@i76g2000hsf.googlegroups.com>


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

Thank you in advance for comments,
PiotreK Received on Wed Aug 13 2008 - 09:49:01 CDT

Original text of this message