4

If someone asks how to perform an INSERT-IF-NOT-EXISTS operation in SQL Server, they'll typically get an answer like this back:

IF NOT EXISTS(SELECT 1 FROM [TheTable] WHERE [ColumnX] = @valX)
    INSERT [TheTable] ([ColumnX]) VALUES (@valX)

The problem I'm seeing with this is that in between the SELECT statement and the INSERT statement, the situation could change externally. Another process could insert the ColumnX value after the SELECT statement, but before the INSERT statement, resulting in an error being raised.

I've worked in software for a while, but am not a DB specialist, and when I search for an answer to this problem in SQL Server, the results I'm seeing are either irrelevant or quite difficult to really apply (because they're either answering a different question or are written in terms tailored to DB specialists).

So in layman's terms, how do you resolve this problem? I did get a little bit rusty with SQL in recent history, but am thinking that there really should be a pragmatic locking mechanism to use for this (whether there is or isn't). As a fallback, maybe error handling can specifically determine whether an error raised matches this exact issue, ignoring it in that specific case.

Preferably this doesn't involve just locking the whole table every time.

Improve this question

1 Answer 1

Reset to default
5

Option 1: take a lock out which locks at least the range in the index that the row would exist in.

SET XACT_ABORT ON;
BEGIN TRAN
IF NOT EXISTS(SELECT 1 FROM [TheTable] WITH (UPDLOCK, HOLDLOCK) WHERE [ColumnX] = @valX)
    INSERT [TheTable] ([ColumnX]) VALUES (@valX)
COMMIT

HOLDLOCK will give serializable semantics and lock the range between existing keys in the index where the value would fit (if there is a suitable index, otherwise it will lock the whole table). UPDLOCK reduces the probability of deadlocks in this pattern as two concurrent queries can't take out the same range lock in the reading phase.

Option 2: You can just add a unique constraint on ColumnX and try the insert anyway and catch the error raised from duplicate key violation.

Given that Option 1 needs an index with leading column ColumnX anyway to meet your preference of not "locking the whole table every time" you might as well add one and define it as unique. The index will speed up the existence check anyway. With that in place I would select between option 1 and 2 on the basis of how frequently I expect attempts to insert duplicates.

Improve this answer
9
  • Thank you. As far as Option 1 is concerned, in one of my tables, ColumnX is of type nvarchar(max) NOT NULL, and so SQL Server was a little funny about sticking a unique constraint on there. But if I'm understanding you right, Option 1 will still work just fine; the only difference is that it'll pretty much search the whole table, instead of an index. Is that so?
    – Panzercrisis
    Commented Aug 14, 2020 at 18:06
  • 1
    If you don't have a useful index that a range lock can be taken out on for the range where [ColumnX] = @valX then it will also have to lock the whole table. i.e. you will be only able to do one of these inserts at a time. Does the column have to be nvarchar(max)? Can it be made short enough to be used in an index key? If not you can hash the value and make an index on the hashed column and check for duplicates using that
    – Martin Smith
    Commented Aug 14, 2020 at 18:50
  • It's supposed to represent a fairly general-purpose "payload" object, and even though I wouldn't necessarily mind setting it to maybe something like nvarchar(2500) initially, my understanding is that indexes have a much stricter quota, something like 300 bytes or 300 characters. In that case, I might be willing to use a hash, but am just worried about the hassle from potential collisions making things complicated. Maybe that's the way to go though...
    – Panzercrisis
    Commented Aug 14, 2020 at 18:54
  • 1
    I wouldn't worry about that. For any of the non deprecated HASHBYTES algorithms probability of hash collisions among rows in a single table is so low as to be basically zero stackoverflow.com/a/4014407/73226 - but you do need to consider normalising the string first. e.g. trimming white space and converting to upper case (assuming you want case insensitive semantics for detecting duplicate strings)
    – Martin Smith
    Commented Aug 14, 2020 at 18:58
  • Wow, that's interesting. Quite different from the typical academic statement about hash functions. Thank you!
    – Panzercrisis
    Commented Aug 14, 2020 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.