Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    32

    Unanswered: Transaction/Locking question

    Hi,
    Very new to locking/transactions, so any help appreciated with the following:

    I want to create a stored procedure with statements that read an Integer value in a single row table, return this as output from the procedure to my web form, and increment it incremented by 1 (for the next call of the procedure).

    I need to be absolutely sure that while the value is being read and updated, another call to the procedure cannot read/update this value until the current is call completed, i.e some sort of locking on the value.

    Question - If I wrap my SELECT and UPDATE statements in a BEGIN TRAN and COMMIT TRAN, will this be enough? e.g Is this sufficient:

    CREATE PROCEDURE sp_test
    @retval int output
    AS

    BEGIN TRAN

    SELECT @retval = POID FROM OMS_SYSDATA
    UPDATE OMS_SYSDATA SET POID=POID+1

    COMMIT TRAN
    GO

    Or do I need to start using things like HOLDLOCK/UPDLOCK within statements?

    Cheers
    Greg

  2. #2
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    ok,

    I think I know the answer to your question. If you don't want the row to change between your select and update, you can call your select like this...

    SELECT @retval = POID FROM OMS_SYSDATA WITH(UPDLOCK,HOLDLOCK)

    Holdlock means that the lock you've just specified will be held until you commit or rollback your tran. If you don't use holdlock, it will only hold that lock until the end of your statement.

    Which means that you could wait forever until that process does the update and the value would still be the same because the lock is still held. The update lock is only compatible with the shared lock (for reading). The lock will only be released when you commit or rollback your tran. Aborting will *not* release it.

    Check out the table at the bottom of this page to get an idea of compatibility.
    http://databasejournal.com/features/...le.php/3289661

    Does that answer your question ?

    Regards,
    -Kilka

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest using:
    Code:
    CREATE PROCEDURE pTest
       @piRetval INT OUTPUT
    AS
    
    BEGIN TRANSACTION
    
    UPDATE OMS_SYSDATA
       SET POID = 1 + POID
    
    SELECT @retval = POID FROM OMS_SYSDATA
    
    COMMIT TRANSACTION
    RETURN
    This takes the update lock before it gets the value, so it should be safe in SQL 2000 and prior releases too.

    -PatP

  4. #4
    Join Date
    Feb 2004
    Posts
    32
    Hi,
    Thanks both for your answers, but I'm not sure that they quite achieve what I need. Effectively I need the sp to prevent any calls to update or read the value until the current call has completed. The next call should always read an incremented value, and then update it, even if initiated a split second after the current call.

    My understanding from your answer Kilka101 is that the lock won't be in place when the UPDATE statement is called - Or am I misunderstanding something?

    Pat, not sure how your answer is different to my own, apart from the order switch in statements - are you implying the BEGIN/COMMIT TRANS are sufficient?

    Thanks in advance
    Greg

  5. #5
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    I think you're misunderstanding me. The update lock will be in place when the select statement is called. Normally it wouldn't, but since we're giving it the hint, it will cause an update lock. Since the update statement is going to hold the same lock, it just uses the existing lock.

    You can try testing this by updating that row with an update statement from another window, and it will hang there until you commit your transaction.

    An update with the connection will still work. IE, this will only block all other @@spid's.


    make sense ?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Posts
    32
    Great, will give these a quick test. Thanks for your help - much appreciated.

    Greg

Posting Permissions

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