Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    1

    Question Unanswered: Concurrent Users

    Hello all,

    sorry this is going to be a somewhat basic question, i have searched and have not quite found the answer.

    In our system we have a rule master table that holds "counters" for many unique keys to be used in our tables.

    We currently

    select the ruleno
    add 1 to it
    update the rulemast table to increment the number
    then select the updated value
    then perform a Commit(Hold)

    were running into a problem where 2 instances are getting the same number.

    I was reading in a different post about the "FOR UPDATE" phrase. Is this the best way to lock the record until were done with it?

    is there a better way to lock it?

    Any help would be appreciated.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Declare C1 cursor for
    select last_key
    from rulemast
    where...
    for update of last_key;

    Open C1;

    Fetch C1 into :last_key;

    update rulemast
    set last_key = last_key +1
    where current of C1;

    or:

    (have the application add 1 to the host variable last_key)
    update rulemast
    set last_key = :last_key
    where current of C1;

    close C1; (not needed since the commit will close the cursor)

    commit; (this will close all cursors not defined with the Hold Option)

    ------------------------------------
    I am not sure why you are selecting the updated value. Can't your program just add 1 to the value originally selected, or use the host variable which is already incremented?

    Also, not sure what commit(hold) is, but that may be my ignorance.
    ---------------------------------------------------------------------

    Alternative way:

    update rulemast
    set last_key = last_key +1
    where ....;

    Select last_key
    from rulemast
    where ....;

    Commit;

    (not sure if this is faster than cursor when using static SQL)

Posting Permissions

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