January 3, 2011 at 8:14 am
Hi,
I have a stored procedure which reads a counter from a control table, updates the counter during the process and then reupdated the control table with the new counter value
BEGIN TRANSACTION
DECLARE @iCnt As BigInt
SELECT @iCnt = Counter
FROM CR_CONTROL
WHILE (1=1)
BEGIN
SET @iCnt = @iCnt + 1
-- In this loop I do my work and is broken at some point
END
UPDATE CR_CONTROL SET Counter = @iCnt
COMMIT TRANSACTION
If some one else runs the same procedure at the same time is there a chance that the second user will get the same counter as the first use who ran the procedure. In other word does commit transaction lock the table CR_CONTROL or not?
Ivan
January 3, 2011 at 8:39 am
you could tackle it it two ways.
since you KNOW you are going to update the table to teh next counter value (unless the transaction rolls back) update the table to the new counter immediately before even starting your while loop...the transaction will lock that row of the table(you are using just one row int he table anyway...so it's nearly the same as an exclusive table lock)
otherwise, i think you can lock the table until your transaction completed by using the WITH(TABLOCKX) hint:
SELECT @iCnt = Counter
FROM CR_CONTROL WITH(TABLOCKX)
Lowell
January 3, 2011 at 9:02 am
How much work are you doing in there? As written, you are not locking the table immediately and it won't be until the end during the update that the table is locked and prevents other users from changing the value. There could be duplicate values as you have it.
January 3, 2011 at 9:20 am
I have quite a lot of updates in the while loop and I already had issues with different users getting the same counter number that is why I want to know how I can eleminate this change does the tablockx lock the table even on a select statement?
10x
January 3, 2011 at 9:45 am
i would update it to an arbitrary number so that SQL's locking mechanism locks the row, then complete the work:
BEGIN TRANSACTION
DECLARE @iCnt As BigInt
SELECT @iCnt = Counter
FROM CR_CONTROL
--arbitrary update to enforce the transaction lock on the row.
UPDATE CR_CONTROL SET Counter = 0
WHILE (1=1)
BEGIN
SET @iCnt = @iCnt + 1
-- In this loop I do my work and is broken at some point
END
UPDATE CR_CONTROL SET Counter = @iCnt
COMMIT TRANSACTION
Lowell
January 3, 2011 at 9:52 am
There is still a small chance here that two people would run the SELECT at the same time, then one would get the update lock.
Ultimately, what are you trying to achieve here? If it is lock this table, and that won't cause issues with the application, update another field in that table so that a lock is held and no one else can run this. But what does that mean for your application? If others cannot access this table, will your application break?
January 4, 2011 at 2:09 am
Answer to your question is: yes, parallel execution of that procedure will give you the same control number.
In default transaction isolation level, transaction will not lock the selected row.
The key question is: What are you trying to achieve ?
Monitor the progress of the loop, prevent parallel execution of procedure or something else ?
From SQL2005 and up there is a technique that may be very valuable to you: updating and selecting the value in single command, step and (implicit) transaction. You do not have to use explicit transactions.
It does not prevent parallel execution, but definitively prevents parallel threads to get the same counter value. Here it is:
--BEGIN TRANSACTION -- not required anymore!
DECLARE @iCnt As BigInt
DECLARE @tCnt TABLE( counter BIGINT )
-- Increase counter and get counter value in single command. No explicit transaction required.
UPDATE CR_CONTROL
SET COUNTER = ISNULL(COUNTER,0) + 1
OUTPUT INSERTED.COUNTER INTO @tCnt -- inserts new counter value into @tCnt table
SELECT @iCnt = COUNTER FROM @tCnt -- if you need that value in scalar variable
WHILE (1=1)
BEGIN
-- In this loop I do my work and is broken at some point
END
--COMMIT TRANSACTION -- not required anymore!
You can place that UPDATE inside the loop if you want.
Hope this helps.
January 4, 2011 at 3:36 am
Simple solution could be applying UPDLOCK at the time of selection, which will not allow other sessions to lock the same row.
January 4, 2011 at 3:49 am
harinerella (1/4/2011)
Simple solution could be applying UPDLOCK at the time of selection, which will not allow other sessions to lock the same row.
You also have to apply a key range lock by making the read serializable.
SELECT @iCnt = [Counter]
FROM CR_CONTROL WITH (UPDLOCK, SERIALIZABLE)
If you provide the whole routine along with DDL, sample data and expected results from the sample data, I suspect someone will be able to provide you with a more efficient solution.
January 4, 2011 at 1:25 pm
I've used this type of counter/sequence table logic for years and never had any contention problems. Explicit transactions aren't needed because everything is done in the update (quirky update) statement. It's easier if the counter in the table represents the next counter to use.
DECLARE
@Counter INT
UPDATE CounterTable
SET @Counter = Counter
, Counter = Counter + 1
-- Do processing using @Counter
You can't re-use the counter, however, if the transaction needs to be rolled back. It gets way to messy
Todd Fifield
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply