Will an UPDLOCK in Microsoft SQL Server prevent inserts on keys in an IF EXISTS query?

Question

I have a table similar to this one...

CREATE TABLE [Customer]
(
    [Id] BIGINT IDENTITY NOT NULL,
    [AccountName] CHARACTER VARYING(255),

    CONSTRAINT [PK_Customer_Id] PRIMARY KEY ([Id]),
    CONSTRAINT [UQ_Customer_AccountName] UNIQUE ([AccountName])
)

I want to execute this query concurrently from many applications...

IF NOT EXISTS(SELECT [AccountName] FROM [Customers] WITH (UPDLOCK, HOLDLOCK) WHERE [AccountName] = 'SuperCustomer') THEN
BEGIN
    INSERT INTO [Customers] ([AccountName]) VALUES ('SuperCustomer');
END

Would the WITH (UPDLOCK, HOLDLOCK) prevent concurrent execution of this query from attempting the insert with the same AccountName value even if the row does not exist yet by holding the update lock on the index of the non existent data? I want to avoid termination due to a unique constraint violation on AccountName in the Customer table in all cases if a user tries to submit the same customer for creation twice at the same time or in high volume maliciously for whatever reason. We're operating with SET XACT_ABORT ON and this will be inside a transaction that is at the READ COMMITTED isolation level.

Possible duplicate of [Only inserting a row if it's not already there](https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there) — GSerg, Apr 27 '18 at 19:40
@GSerg this is specific to whether or not my `UPDLOCK` will hold the guarantees I expect. I am not asking how to do it in any other way as your "duplicate" question does. It's specifically about if the `UPDLOCK` can hold a lock on a lock on the index for the unique constraint when the data does not exist in the table yet. — Michael J. Gray, Apr 27 '18 at 19:45
There are different techniques shown there in different answers. One of them involves an `updlock`, and yes, it requires a `holdlock`. — GSerg, Apr 27 '18 at 19:50
@GSerg That's fine but I want an answer specific to this technique, which is different. It's two statements in one transaction and the reason for that, which is not shown here to keep the core question in focus, is because I'll need to use the `OUTPUT` clause to extract the `Id` column in both cases. They're different. Your comment-answer also doesn't answer my question, which is whether or not an actual lock is held on the index. — Michael J. Gray, Apr 27 '18 at 20:17
Without specific experience of this, it's not possible for me to answer your question, but the following link has some good information in it which you may find useful: http://rusanu.com/2010/03/26/using-tables-as-queues/ — MJH, Apr 27 '18 at 22:22
My guess is the locks will be released before the insert runs, but I'm not sure. That said, why not make it a serializable transaction? — influent, Apr 27 '18 at 22:25
@influent I suppose if it's serializable and just the two statements, there's no decrease in performance. HOLDLOCK should have the same effect. But I am mostly curious how it handles the UPDLOCK part in this specific case. — Michael J. Gray, Apr 28 '18 at 04:19

score 1 · Accepted Answer · answered Apr 29 '18 at 14:35

1

Testing this using two connections, with a WAITFOR DELAY before the INSERT, indicates that it's an effective technique. The HOLDLOCK hint keeps the locks until the end of the transaction (the UPDLOCK acquires an U lock on a KEY resource, which is incompatible with another similar lock).

answered Apr 29 '18 at 14:35

Razvan Socol

5,426
2
20
32

This is the behavior I observed as well. I am just curious what key it's holding a lock on. Does it really lock on what it thinks the record will materialize as after the transaction commits or is it just holding a lock on the unique value before it's inserted? More of an internals question I guess, but interesting to me. – Michael J. Gray Apr 29 '18 at 21:54
1

See https://stackoverflow.com/questions/6012911/what-resource-does-a-key-lock-actually-lock – Razvan Socol May 02 '18 at 05:32
UPDLOCK applies until the end of the transaction as well: "UPDLOCK Specifies that update locks are to be taken *and held until the transaction completes*.."; HOLDLOCK is an alias for SERIALIZABLE (re: acquire exclusive locks) and not really "hold the update lock" -- https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15 – user2864740 Aug 19 '21 at 19:37
That said, UPDLOCK on a single compound statement *not in an explicit transaction* appears to be much less reliable than it's usage in an explicit transaction. – user2864740 Aug 19 '21 at 19:42

paparazzo · Answer 2 · 2018-04-29T18:22:21.933

0

The select is not in a tranaction so I think it will be released.

IF NOT EXISTS( SELECT [AccountName] 
               FROM [Customers] WITH (UPDLOCK, HOLDLOCK) 
               WHERE [AccountName] = 'SuperCustomer' ) 
THEN
BEGIN
    INSERT INTO [Customers] ([AccountName]) VALUES ('SuperCustomer');
END

This is a single statement so it is a transaction

insert into INSERT INTO [Customers] ([AccountName]) 
select 'SuperCustomer' 
where not exists ( select 1 
                   from [Customers] with (UPDLOCK)
                   where [AccountName] = 'SuperCustomer' )

edited Apr 29 '18 at 18:22

answered Apr 29 '18 at 18:21

paparazzo

44,497
23
105
176

The poster already specified "We're operating with SET XACT_ABORT ON and this will be inside a transaction that is at the READ COMMITTED isolation level." – Razvan Socol Apr 29 '18 at 18:22
@RazvanSocol OK missed that. Answer may still be of value to the OP. – paparazzo Apr 29 '18 at 18:23
You need to use the `HOLDLOCK` hint even if you use a single statement. Try running the code from https://pastebin.com/CpxySXdj in two SSMS windows. I got an PK error within a few minutes. – Razvan Socol May 01 '18 at 08:50
@RazvanSocol Not sure that is a valid test – paparazzo May 01 '18 at 12:28
@RazvanSocol In that presented code, the INSERT..WHERE NOT EXISTS is not inside an explicit transaction. When it is inside an explicit transaction the UPDLOCK holds until the end. That is, *if* the code was setup as "..will be inside a transaction.." then it appears to be sufficient with UPDLOCK. (I would expect there to be a single-statement *implicit* transaction, although that does not appear to be holding in context..) – user2864740 Aug 19 '21 at 19:32
@RazvanSocol That is, the minimal modification appears to be valid without the use of HOLDLOCK (aka SERIALIZABLE): `BEGIN TRAN; INSERT INTO ##T SELECT @x WHERE NOT EXISTS (SELECT * FROM ##T WITH (UPDLOCK) WHERE X=@x); COMMIT` (SQL Server 2017 with RCSI.) – user2864740 Aug 19 '21 at 19:34
@user2864740: If you use snapshot isolation, I believe your example should work. The code mentioned in my comment on 1 May 2018 assumes READ COMMITED isolation level (without snapshot). – Razvan Socol Aug 19 '21 at 19:55
@user2864740: I did another test with SNAPSHOT isolation here: https://pastebin.com/dP2Jr0TG. If I don't add HOLDLOCK after UPDLOCK, after a few seconds, I get the following error: "Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.T' directly or indirectly in database 'TestRCSI' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement." – Razvan Socol Aug 21 '21 at 10:33

Will an UPDLOCK in Microsoft SQL Server prevent inserts on keys in an IF EXISTS query?

2 Answers2