Skip to main content
Database Administrators

Return to Question

Tweeted twitter.com/StackDBAs/status/1300402968404070406
edited tags
Link
Martin Smith
Martin Smith
  • 86.5k
  • 15
  • 252
  • 342
added 76 characters in body
Source Link
Panzercrisis
Panzercrisis
  • 167
  • 1
  • 6

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.

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.

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.

Source Link
Panzercrisis
Panzercrisis
  • 167
  • 1
  • 6

In SQL Server, in layman's terms, how do you lock resources well enough to perform an INSERT-IF-NOT-EXISTS transaction?

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.