"Willing-to-wait" locks
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: "Willing-to-wait" locks

  1. #1
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    Hello,

    If two sessions execute the following procedure at the same micro-second, will one of them be "willing to wait"? If not, how does one code a willing-to-wait lock?

    CREATE OR REPLACE PROCEDURE
    get_next_number_reference (IN_requested_type in out char, OUT_result_out in out number) AS
    --
    CURSOR c1 IS
    SELECT big_sequence_nbr + 1
    FROM number_reference
    WHERE record_id = IN_requested_type FOR UPDATE;
    BEGIN
    OPEN C1;
    FETCH C1 INTO OUT_result_out;
    UPDATE number_reference
    SET big_sequence_nbr = OUT_result_out
    WHERE CURRENT OF c1;
    CLOSE c1;
    COMMIT;
    END get_next_number_number;


    We originally did not go with sequences because of advanced replication (but we have worked that out).

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    All locking, by default, is what you call 'willing to wait'.

    If you don't want to wait for a lock, you must explicitely add NO_WAIT (or something like that) to your statement.

    However, as far as sequence-replacing code goes, you should note that your COMMIT may be problematic. Obviously, you will not be able to call this in the middle of a transaction because of the COMMIT. To alleviate this, you should look into autonomous transactions.

    However, if you've fixed your sequence problems, you should really go back to sequences because they are faster.

    If not, you should at least use NDS instead of CURSORs because NDS is faster than CURSORs, although still slower than sequences.

    HTH,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    Thanks Chris,

    I immediately implemented the AUTONOMOUS_TRANSACTION.

    The problem that we had with sequences is with Advanced Replication. We fixed that in some cases with:

    Start 1 increment by 2 at site1 (odd keys)
    Start 2 increment by 2 at site2 (even keys)

    but order number mattered in some PK cases.

    -----
    But what is NDS? - I could not find in doc.

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    NDS = Native Dynamic SQL.

    Actually, however, you don't need either. My bad - you don't have any dynamic SQL.

    You should just use the returning clause in your case:

    Code:
    CREATE OR REPLACE PROCEDURE   
    GET_NEXT_NUMBER_REFERENCE   
    (
       IN_REQUESTED_TYPE IN OUT   CHAR,   
       OUT_RESULT_OUT    IN OUT   NUMBER
    )   AS
    BEGIN
    
       UPDATE
          NUMBER_REFERENCE   
       SET
          BIG_SEQUENCE_NBR   =   BIG_SEQUENCE_NBR + 1
       WHERE
          RECORD_ID   =   IN_REQUESTED_TYPE
       RETURNING
          BIG_SEQUENCE_NBR
       INTO   
          OUT_RESULT_OUT   ;
    
       COMMIT;   
    END GET_NEXT_NUMBER_REFERENCE;
    HTH,

    - Chris

    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width