CODEWITHSUNDEEP
sqlsql-server-2017isolation-levelread-committed-snapshot
Avi
Avi

Reputation: 1193

Parallel insert not existing rows under RCSI

I try to load some rows to a target table but only if they are not there yet. A classic left join query solves this:

    INSERT INTO dbo.Target(SomeData)
    SELECT DISTINCT SomeData
    FROM #Temp s
    LEFT JOIN dbo.Target d ON d.SomeData = s.SomeData
    WHERE d.SomeData IS NULL;

This insert runs multiple times parallel from different users, who try to insert the same data most of the time.

I see a lot of duplicates in the table (no unique constraint on the SomeData column yet) from different users which is strange since the left join should detect this and prevent inserting which is already in the table.

I investigated and saw that the db is on RCSI (read committed snapshot isolation) level. My idea is that all parallel sessions thinks that they are the first inserting the data and all successfully do that.

How could I do that insert under RCSI? I want each session to wait if there is one session already doing its left join insert stuff.

Any help is appreciated, thank you! P.S.: I am not allowed to change db isolation level unfortunately.

Upvotes: 0

Views: 59

Answers (1)

Avi
Avi

Reputation: 1193

Table hint READCOMMITEDLOCK is the answer, but still has some issue with it, will post another question about it.

Upvotes: 0

Related Questions