0

I have a SQL query as below, running within REPEATABLE READ transaction.

IF NOT EXISTS (SELECT * FROM TableName WHERE Column1Name = @Column1Value)
    INSERT INTO TableName VALUES (@Column1Value, @Column2Value);

The above query gets run within multiple processes. I see a possibility when process 1 does a SELECT and then process 2 also does as SELECT (as there is shared lock) before either of the processes get chance to do an INSERT, there would be a deadlock. I would like to avoid this. What is the correct table hint to use for this situation? Note that it is not possible to enable READ_COMMITTED_SNAPSHOT on the database.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Jatin Sanghvi
  • 1,928
  • 1
  • 22
  • 33
  • 1
    Does this answer your question? [Insert if not exists avoiding race condition](https://stackoverflow.com/questions/50688410/insert-if-not-exists-avoiding-race-condition) – SMor Aug 02 '22 at 11:48
  • See also https://sqlperformance.com/2020/09/locking/upsert-anti-pattern. essentially you want a `SET XACT_ABORT ON; BEGIN TRAN; IF EXISTS (... WITH (SERIALIZABLE, UPDLOCK)...) INSERT ... ; COMMIT;` You **must** have the `UPDLOCK` hint regardless of isolation level, because of deadlocking issues, and if you are not using `SERIALIZABLE` but `REPEATABLE READ` or lower then also because an unmodified row is never locked. – Charlieface Aug 02 '22 at 14:05
  • Yes @SMor, it answers my question. I found https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide helpful in understanding by SERIALIZABLE isolation level in combination with UPDLOCK works. – Jatin Sanghvi Aug 04 '22 at 11:14
  • For my notes, SERIALIZABLE isolation level (unlike REPEATABLE READ) also acquires range-locks which helps with locking of non-existing rows. so that INSERT statements in other transactions are blocked if those had caused a different query result of already executed SELECT statements in current transaction. UPDLOCK is the most appropriate locking mode as only one of the transactions can acquire update lock at a time, and unlike exclusive locks, update locks do not get blocked if another transaction has shared lock acquired, which is fine as it is just a SELECT statement. – Jatin Sanghvi Aug 04 '22 at 11:35

1 Answers1

-1

I would not use a hint but instead do this in one operation. Check out Davide's article and try and apply his method with the single merge statement. In my opinion the preferred method.

Peter L - MSFT
  • 167
  • 4
  • There are frequent suggestions to avoid using MERGE because of many issues - [one example](https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/). It would be great if MS could make an official post that addresses these issues - acknowledging the accurate ones and debunking the inaccurate ones. – SMor Aug 02 '22 at 13:24
  • You are right, looks like a lot of issues that needs to be avoided and/or checked to be confident with a solution based on MERGE. – Peter L - MSFT Aug 03 '22 at 12:02