Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Posts
    7

    Unanswered: Lock specific row in procedure

    Sybase 15.0.3
    AIX 5.2

    Hi.

    I had this in a procedure... to get the next id.. ( very old code )
    BEGIN
    SELECT @_NewId = (SELECT DISTINCT Value FROM NextId WHERE
    CounterKey = @_Key)
    UPDATE NextId SET Value = @_NewId + 1 WHERE CounterKey = @_Key
    END

    But this was not so save as diff threads could still get the same value.
    So it was changed to ....
    BEGIN TRANSACTION
    SELECT @_NewId = (SELECT DISTINCT Value FROM NextId WHERE
    CounterKey = @_Key)
    UPDATE NextId SET Value = @_NewId + 1 WHERE CounterKey = @_Key
    COMMIT TRANSACTION

    But I don't think this will solve the problem... still some threads could get the same value.

    I think I will have to select for update.. But my question is can I select for update and only lock one row. ??

    Will this do it ????

    BEGIN TRANSACTION
    SELECT * FROM NextId where CounterKey = @_Key FOR UPDATE
    SELECT @_NewId = (SELECT DISTINCT Value FROM NextId WHERE
    CounterKey = @_Key)
    UPDATE NextId SET Value = @_NewId + 1 WHERE CounterKey = @_Key
    COMMIT TRANSACTION

  2. #2
    Join Date
    Apr 2012
    Posts
    7
    OK, so the select for update does not work in sybase. 15.0.3

    So I did this...

    BEGIN TRANSACTION
    LOCK TABLE NextId IN EXCLUSIVE MODE
    SELECT @_NewId = (SELECT DISTINCT Value FROM NextId WHERE
    CounterKey = @_Key)
    UPDATE NextId SET Value = @_NewId + 1 WHERE CounterKey = @_Key
    COMMIT TRANSACTION

    Here I get a lock on the table.. can I specify that I only want to have the lock on a specific row ???

Posting Permissions

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