If a transaction does a select with (UPDLOCK), can another transaction successfuly do exactly the same select with (UPDLOCK) before the first is completed? I have the following example setup: IF OBJECT_ID('Account','U') IS NOT NULL DROP TABLE Account GO CREATE TABLE Account (accountID INT, amount int) GO IF OBJECT_ID('Visits','U') IS NOT NULL DROP TABLE Visits GO CREATE TABLE Visits (accountID INT, amount INT, theTime DATETIME DEFAULT GETDATE()) GO INSERT Account (accountID,amount) SELECT 1,0 GO IF OBJECT_ID('visitAccount','P') IS NOT NULL DROP PROCEDURE visitAccount GO CREATE PROCEDURE visitAccount @accountID INT AS BEGIN SET NOCOUNT ON; -- Wait for max 1 sec if locked. SET LOCK_TIMEOUT 1000; DECLARE @amount INT BEGIN TRY SELECT @amount = amount FROM Account WITH(UPDLOCK, ROWLOCK) WHERE accountID=@accountID END TRY BEGIN CATCH IF ERROR_NUMBER()=1222 BEGIN -- Lock request time out period exceeded. RAISERROR (N'[timeout]', 17,1)WITH NOWAIT; END SET LOCK_TIMEOUT -1; RETURN; -- always return here to client END CATCH -- Reset LOCK_TIMEOUT to default SET LOCK_TIMEOUT -1; SET @amount = @amount + 1 UPDATE Account SET amount=@amount OUTPUT inserted.accountID, inserted.amount INTO Visits (accountID,amount) OUTPUT inserted.amount WHERE accountID=@accountID AND @amount IS NOT NULL END My clients run the following code: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET LOCK_TIMEOUT -1; BEGIN TRANSACTION BEGIN TRY EXEC visitAccount @accountID = 1 --WAITFOR DELAY '00:00:02' COMMIT END TRY BEGIN CATCH SELECT 'TIMEOUT' ROLLBACK END CATCH After testing with many concurrent clients (approximately 100,000),I have discovered that there are duplicates in the Visits table. How can this happen with UPDLOCK in first select?
Would this not be much simpler? DECLARE @newAmount int UPDATE Account SET @newAmount = amount = amount + 1 WHERE accountID = @accountID INSERT INTO Visits (accountID,amount) VALUES (@AccountID, @newAmount)
I do believe it's your read level which is causing your issue. UPDLOCK doesn't work if you're using a transaction isolation level of READ UNCOMMITTED or READ COMMITTED. Here's an experiment that I did: First, I set up a second tally table, one without an identity column (so I could update it). I'm using a [pre-created Tally table](
http://www.sqlservercentral.com/articles/T-SQL/62867/). create table Tally2 ( N int ); insert into Tally2 select * from Tally; In SSMS window 1, I have the following query: set transaction isolation level read committed begin transaction select * from Tally2 s1 cross join Tally2 s2 with(updlock) update Tally2 set N = N + 1 rollback In SSMS window 2, I have the following query: set transaction isolation level read committed select * from Tally2 Results: - Read Uncommitted for both: window 2 selects. - Read Committed for both: window 2 selects. - Repeatable Read or Serializable for Window 1, Repeatable Read or Read Committed for Window 2: blocking. - Repeatable Read or Serializable for Window 1, Read Uncommitted for Window 2: window 2 selects. So it looks like, in order to have window 2 be blocked--to have the UPDLOCK query hint do what you want it to do--you need to have the command performing the UPDLOCK be in a transaction isolation level of Repeatable Read or Serializable and for the second command to be at least Read Committed. Read Uncommitted ignores the locks placed via UPDLOCK and causes the update anomalies you experienced. So for your particular setup, try changing the client isolation level to Repeatable Read. That should cause sufficient blocking (and almost certainly make things slower because now the other threads aren't blowing through the blocks like Reggie White in a hurry...).