This question already has an answer here:
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?
CREATE TABLE dbo.test ( id INT PRIMARY KEY IDENTITY(1,1) , RecordType INT ); CREATE UNIQUE INDEX UIX_dbo_test_RecordType ON dbo.test (RecordType);
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
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