Insert Concurrency Issue - Multithreaded Environment

Question

I have an issue that the same stored procedure is being invoked at exactly the same time with exactly the same paramenters.

The purpose of the stored procedure is to fetch a record if it exists or to create and fetch the record if it does not exist.

The problem is that both threads are checking the existence of the record and reporting false and then both are inserting a new record, creating a duplicate in the database.

I tried sticking the operations inside a transaction but this merely produced hundreds of deadlocks.

Is there any way that I can check for the existence of the record in a thread safe manner so that the second thread won't do its read until the first has finished its insert? I have no control over the threads themselves, only over the stored procs they are executing.

Any help would be appreciated,

Thanks.

Answer 1

The trick is to add a WHERE to your INSERT statement so that INSERT only works if the item does not exist, followed by the SELECT statement. Supposing that the record can be identified by an ID column you would write:

INSERT INTO MyTable (ID,Col1,Col2,...) 
SELECT @IDValue,@Col1Value,@Col2Value, ...
WHERE NOT EXISTS (SELECT ID  
              FROM MyTable 
              WHERE ID=@IDValue) 

SELECT *  
FROM MyTable 
Where ID=@IDValue 

You don't need to put the statements in a transaction because each statement is executed in its own implicit transaction. Thus, there is no way that two INSERTS will succeed at the same time.

EDIT: The INSERT ... SELECT syntax is necessary because TSQL doesn't allow a VALUES and a WHERE part in the INSERT statement.