Insert where not exists but thread safe (I don't want duplicates)

Question

I need to insert values into a table if a value with a matching ID does not exist like in this thread: SQL - Insert Where Not Exists

But I need to make sure if an other thread executes a query in exactly the same time, I won't get two the same rows.

This is my table:

CREATE TABLE [dbo].[Localizations]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NOT NULL,
    [regionId] [int] NOT NULL
) ON [PRIMARY]

This my current query which inserts a new localization row if a localization row with regionId = x doesn't exist (unfortunately it works incorrectly - now I have duplicates in my database):

-- firstly I execute something like that (my real queries are more complex):    
DECLARE @id int = (SELECT [id] FROM [dbo].[Localizations] WHERE regionId = 1);
    
-- secondly I execute something like that (my real queries are more complex):
IF (@id IS NULL)
BEGIN
    INSERT INTO [dbo].[Localizations] 
    VALUES ('Test', 1);  
END

It caused that now I have MANY rows with the same regionId, I can't remove them now, they are used in different tables :( :( :( Because of that, I can't create the unique constraint on the regionId column because I have duplicates :( :(

Could you tell me if the following query doesn't create duplicates with the same regionId if many threads execute that query in the same time? I have read this thread: SQL - Insert Where Not Exists but I am not sure and I don't want to insert more duplicates :(

INSERT INTO [dbo].[Localizations] (name, regionId)
    SELECT 'Test', 1
    WHERE NOT EXISTS (SELECT * 
                      FROM [dbo].[Localizations] 
                      WHERE regionId = 1)

@Orkad I can't do that, because I have already duplicates in my table, because my current query is incorrect and isn't thread safe. :( — user2017341, Jul 02 '21 at 17:53
So you could try to remove duplicates first :) https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server — Orkad, Jul 02 '21 at 17:55
Does this answer your question? [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, Jul 02 '21 at 18:09

score 1 · Accepted Answer · answered Jul 02 '21 at 18:05

1

After you remove the duplicates and add a unique constraint you can change your batch to prevent sessions from attempting to insert duplicates like this:

BEGIN TRANSACTION;
DECLARE @id int = (SELECT [id] FROM [dbo].[Localizations] WITH (UPDLOCK,HOLDLOCK) WHERE regionId = 1);
    
-- secondly I execute something like that (my real queries are more complex):
IF (@id is null)
BEGIN
    INSERT INTO [dbo].[Localizations] VALUES('Test', 1);  
END
COMMIT TRANSACTION;

This will force the first query to take and hold an update lock on the row or empty range, which will ensure that the INSERT will succeed, and any other session running this code will block until the transaction is committed.

answered Jul 02 '21 at 18:05

David Browne - Microsoft

80,331
6
39
67

thank you very much. @GSerg suggested also a thread where is a solution similar to your with UPDLOCK, HOLDLOCK and a solution with MERGE. What is the difference between them, which is better in my case? – user2017341 Jul 02 '21 at 18:44
1

Merge is not atomic, on its own does not solve the concurrency issue. And did you see its buglist? Explicit locking would work, but it may be more restrictive than necessary. Why not just try/catch approach? You need the error handling anyway. – Antonín Lejsek Jul 02 '21 at 20:20
2

Yes. MERGE also requires lock hints on the “scan” clause. – David Browne - Microsoft Jul 02 '21 at 20:32

score 0 · Answer 2 · answered Jul 02 '21 at 18:00

0

You already know the answer, you should remove duplicities and add unique constraint. Until then, your data are broken.

If you want just a patch for new data, you can create unique filtered index on regionId, where you filter on regionId > lastDuplicitValue. But if you do not care about duplicities you already have, why care about the new ones?

answered Jul 02 '21 at 18:00

Antonín Lejsek

6,003
2
16
18

These duplicated records are used on a production right now, they are related to another tables (many tables). I want to prevent it from happening again in the future. – user2017341 Jul 02 '21 at 18:11

Insert where not exists but thread safe (I don't want duplicates)

2 Answers2