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

Trouble is in doing a select and then insert then normally there is a read lock for the select and then a write lock on the insert. Without a transaction then the timing of many updates will often allow multiple inserts to happen as you see. In a transaction the first read lock will stop other processes getting a write lock and if more than one process gets a read lock then none can get a write lock and so you get a deadlock.

In this case I would alter the insert code so that the indices only allow one insert to work ie you have a unique key and only one process will ne able to insert the data so getting no duplicates. The process for updating is then in a transaction either

1) do the insert first and deal with an exception or error if it tries inserting a duplicate

or 2) do a HOLD LOCK (Sybase and SQL Server)on doing the select first - thus the first to lock gets the full permission to insert if needed

or 3) Possibly use the merge command if the RDBMS allows it. This does the check and insert all in one command but will always change the database.

EDIT: I think there is no real alternative to 1 if you need to make sure there is one and only one record inserted as the test for that has to be in a transaction.

the cost could be cut by checking for existence forst in one transaction and then secondly doing the insert and check in another transaction. Thus in most cases you just have a select and in the other cases you get the full slow insert and check but this should occur less often.

Answer 2

Not sure if SQL Server has it. But in MySQL and in oracle you can get write lock while doing a select using for update syntax.

select * 
from table 
for update

Since other threads also need write lock while doing select, they will wait till first thread completes the transaction.

Answer 3

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.

Answer 4

This is a very common problem. I have a canned answer with full analysis. It begins as follows: Neither

UPDATE … IF (@@ROWCOUNT = 0) INSERT

nor

IF EXISTS(...) UPDATE ELSE INSERT

patterns work as expected under high concurrency. Both may fail. Both may fail very frequently. MERGE is the king - it holds up much better.Let us do some stress testing and see for ourselves.

the rest of the article is here.