Avoid inserting duplicate records in SQL Server

Question

I haven't been able to find an answer to this. Suppose I have the following table/query:

The table:

create table ##table
(
   column1 int,
   column2 nvarchar(max)
)

The query (in a real life scenario the condition will be more complex):

declare @shouldInsert bit
set @shouldInsert = case when exists(
    select * 
    from ##table
    where column2 = 'test') then 1 else 0 end

--Exaggerating a possible delay:
waitfor delay '00:00:10'

if(@shouldInsert = 0)
   insert into ##table
   values(1, 'test')

If I run this query twice simultaneously then it's liable to insert duplicate records (enforsing a unique constraint is out of the question because the real-life condition is more involved than the mere "column1" uniqueness across the table)

I see two possible solutions:

I run both concurrent transactions in serializable mode, but it will create a deadlock (first a shared lock in select then an x-lock in insert - deadlock).
In the select statement I use the query hints with(update, tablock) which will effectively x-lock the entire table, but it will prevent other transactions from reading data (something I'd like to avoid)

Which is more acceptable? Is there a third solution?

Thanks.

Can't you just use LEFT JOIN insert into ##table SELECT t.col1, t.col2 FROM (SELECT 1 as col1, 'test' as col2) t LEFT JOIN ##table temp ON t.col1 = temp.col1 WHERE temp.col1 IS NULL? — Lukasz Szozda
– Lukasz Szozda, Commented Sep 25, 2015 at 5:40
Did you consider combining the shouldInsert test and the actual insert into a single SQL statement. Something along the lines of "insert into ##table (select 1,'test' from dual where not exists ..." — Martin Drautzburg
– Martin Drautzburg, Commented Sep 25, 2015 at 6:40
I would opt for the second solution, but using the INSERT...SELECT...WHERE NOT EXISTS syntax with the locking hint in the subquery. I wouldn't expect concurrency to be an issue unless you have a very high insert rate, assuming a unique index on column2. — Dan Guzman
– Dan Guzman, Commented Sep 25, 2015 at 11:53

marc_s · Accepted Answer · 2015-09-25 06:42:19Z

1

If you can, you should put a UNIQUE constraint (or index) on whatever column(s) it is that is defining the uniqueness.

With this, you might still get the "OK, doesn't exist yet" response for your initial check for two separate processes - but one of the two will be first and get his row inserted, while the second will get a "unique constraint violated" exception back from the database.

answered Sep 25, 2015 at 6:42

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sten Petrov · Accepted Answer · 2015-09-25 07:24:47Z

0

Regardless how "involved" your "real-life condition" is you have two options: enforce UNIQUE or deal with multiple records. Any work-around will likely be fragile.

For example your delay hack is pretty useless if you need to add another DB server or overwhelming load slows down the execution of individual threads

One of the ways you could allow for multiple copies of a should-be-unique value is to create another table that can act as a queue and doesn't enforce uniqueness and a serial worker to dequeue it. Or change the data structure to allow for 1-to-many and pick the first one when querying. Still a hack but at least not terribly "creative" and it can't break

answered Sep 25, 2015 at 7:24

Sten Petrov

11.1k1 gold badge45 silver badges63 bronze badges

Comments

Dinesh Gaud · Accepted Answer · 2022-03-01 18:20:54Z

0

declare @shouldInsert bit
set @shouldInsert = case when exists(
    select * 
    from ##table
    where column2 = 'test') then 1 else 0 end

--Exaggerating a possible delay:
waitfor delay '00:00:10'

truncate table #temp

if(@shouldInsert = 0)
   insert into #temp
   values(1, 'test')

--if records is not available in ##table then data will be inserted from #temp table to ##table

insert into ##table
select * from #temp
except
select * from ##table

answered Mar 1, 2022 at 18:20

Dinesh Gaud

1411 silver badge11 bronze badges

Collectives™ on Stack Overflow

Avoid inserting duplicate records in SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related