Avoid Violation of PRIMARY KEY in delete\insert transaction?

Question

I have pretty simple T-SQL query

DELETE FROM my_table
WHERE [id] = @Id

INSERT INTO my_table
    ([id], [payload])
VALUES
    (@Id, @Payload)

A lot of these queries executing in different threads. INSERT and DELETE are in transaction with type Read Commited. Everything works good 99.9% of time, but there is an edge case when it fails with:

Violation of PRIMARY KEY constraint 'PK_my_table'. Cannot insert duplicate key in object 'dbo.my_table'. The duplicate key value is (9).
The statement has been terminated.

The problem appears when:

1. We have 2 transactions with the same @Id
2. We don't have a record with @Id in the table my_table

So the 1st and the 2nd transactions start with DELETE. Both transactions delete nothing because there wasn't record in the table my_table. Both start to INSERT and BOOM - Violation of PRIMARY KEY.

The question is how to avoid this case with Read Commited transaction type and without using MERGE statement?

Answer 1

We have 2 transactions with the same @Id We don't have a record with @Id in the table my_table

This is the scenario that Range Locking is intended to address. Under SERIALIZABLE or with the HOLDLOCK hint you will take key range locks on empty key ranges.

eg

drop table if exists tt
go
create table tt(id int primary key, a int)

begin transaction
    delete from tt 
    where id = 1
    select resource_type, request_mode, request_status
    from sys.dm_tran_locks
    where request_session_id = @@spid 
commit transaction

go

begin transaction
    delete from tt with (holdlock) 
    where id = 1
    select resource_type, request_mode, request_status
    from sys.dm_tran_locks
    where request_session_id = @@spid 
commit transaction

outputs

(0 rows affected)
resource_type                                                request_mode                                                 request_status
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
OBJECT                                                       IX                                                           GRANT

(1 row affected)


(0 rows affected)
resource_type                                                request_mode                                                 request_status
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
KEY                                                          RangeX-X                                                     GRANT
OBJECT                                                       IX                                                           GRANT

(2 rows affected)