question

seregak76 avatar image
seregak76 asked

Is SELECT WITH(UPDLOCK) secure?

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?
locking
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
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)
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
paste error or use of quirky update?
0 Likes 0 ·
seregak76 avatar image seregak76 commented ·
What if I want to do "SET @amount = {some crazy computation in enother sp}" and not simple "SET @amount = @amount + 1" (which is only example)? My question was: how duplicates can accure in this situation?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
if you have a more complex computation to perform, you may want to look at application locks. You haven't stated which version of SQL, but here's the links for 2008 R2 `sp_getapplock` `sp_releaseapplock`
0 Likes 0 ·
seregak76 avatar image seregak76 commented ·
Thanks! I tested sp_getapplock and it seems the way to go. I will do tests and post the results.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Fatherjack - no - that's not quirky update - that's documented syntax for modifying a row and reading the modified value...
0 Likes 0 ·
Show more comments
Kev Riley avatar image
Kev Riley answered
The UPDLOCK would only be in force for the duration of the select, it wouldn't last until you've completed the subsequent update.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Yes it is true - the select in itself is a transaction - it is an implicit transaction. Just because the only transaction you can **see** is the one explicitly defined by the client code, there are multiple transactions going on here. The outer transaction is explicitly defined, then (depending on your settings of XACT_ABORT) the stored procedure is a transaction, that contains (nests) a select and an update, both of which are implicit transactions. If this wasn't true and UPDLOCK did persist for the whole of your transaction, then you wouldn't get duplicates, but you do!
1 Like 1 ·
seregak76 avatar image seregak76 commented ·
This is not true. From BOL: UPDLOCK Specifies that update locks are to be taken and held until the transaction completes.
0 Likes 0 ·
seregak76 avatar image seregak76 commented ·
Thanks! I do not see the point with UPDLOCK if it does not persist for the whole of client transaction.
0 Likes 0 ·
Kevin Feasel avatar image
Kevin Feasel answered
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...).
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

seregak76 avatar image seregak76 commented ·
Thanks! The trick is to run in window 2 the same code as in window 1, then second select with updlock must wait for the first to release updlock regardless of isolation level i belive.
0 Likes 0 ·
Kevin Feasel avatar image Kevin Feasel commented ·
If you do run them in the same window, then yeah, you shouldn't have a problem with update locking. But then again, I don't believe that you would need update locking at all in that particular scenario: you're running one operation and then the next after the first one completes. At that point, you don't really have a concurrency problem or an issue of two independent threads trying to access the same piece of information.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.