2

We have some sort of logging table where a record gets inserted whenever a thread is working on a certain topic. It's possible that two separate threads try to start working on the same topic. Therefore, the threads have to verify if a record of the same type already exists in the table. As soon as the threads finishes its work, it deletes the record so other threads can start working again. In other words, a transaction issues a select followed by an insert if no record was returned.

When running under Read Committed or Repeatable Read it's possible that 2 transactions select the same (not existing) record at almost the same time, don't get any record returned and try to insert a record which results in a duplicate key error.

When using Serializable, it's possible to place an update range lock and block the second transaction that checks for the record using WITH(UPDLOCK, HOLDLOCK) . Although I've noticed that whenever the table is empty, each other transaction will be blocked, including transactions that verify the existence of other records. The transactions try to get place a RangeS-U lock on resource (ffffffffffff) which represents the infinity key.

Does anyone know another way to prevent these duplicate inserts or prevent the read statement from locking the complete table?

Many thanks!

enter image description here

Demo setup

CREATE TABLE dbo.test
(
id INT PRIMARY KEY IDENTITY(1,1)
, RecordType INT
);

CREATE UNIQUE INDEX UIX_dbo_test_RecordType ON dbo.test (RecordType);

Transaction 1

BEGIN TRANSACTION 

SELECT * FROM dbo.test WITH(UPDLOCK, HOLDLOCK, ROWLOCK) WHERE RecordType = 1

-- if no record gets returned insert
-- INSERT INTO dbo.test(RecordType) VALUES(1)

-- COMMIT

Transaction 2

BEGIN TRANSACTION 

SELECT * FROM dbo.test WITH(UPDLOCK, HOLDLOCK, ROWLOCK) WHERE RecordType = 2

-- if no record gets returned insert
-- INSERT INTO dbo.test(RecordType) VALUES(2)

-- COMMIT

marked as duplicate by Erik Darling sql-server Jun 14 at 11:41

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

Browse other questions tagged or ask your own question.