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
SELECT @retval = POID FROM OMS_SYSDATA
UPDATE OMS_SYSDATA SET POID=POID+1
Or do I need to start using things like HOLDLOCK/UPDLOCK within statements?
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.
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?
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.