Concurrency Insertion Problem - Multithreaded Environment

Question

I have a problem that the same stored procedure is called exactly at the same time exactly with the same parasites.

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

The problem is that both threads check for the existence of the record and report false, and then both insert a new record, creating a duplicate in the database.

I tried to stick with operations inside a transaction, but it just caused hundreds of deadlocks.

Is there any way to check for a record in streaming safe mode so that the second stream does not read it until the first has completed its insertion? I do not control the threads themselves, only on the stored processes that they execute.

Any help would be appreciated

Thanks.

Answer 1

The trick is to add WHERE to your INSERT statement so that INSERT only works if the item does not exist, followed by a SELECT statement. Suppose a record can be identified by an identifier column that you must 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 do not need to put statements in a transaction, because each statement is executed in its own implicit transaction. Thus, it is not possible that two INSERTS will be successful at the same time.

EDIT : INSERT ... SELECT syntax is needed because TSQL does not allow VALUES and the WHERE part in an INSERT statement.

Answer 2

The problem is to make a selection and then insert, then there is usually a read lock for selection and then a write lock for insert. Without a transaction, many updates often allow multiple inserts to appear, as you can see. In a transaction, the first read lock stops other processes that receive a write lock, and if more than one process gets a read lock, then no one can get a write lock, and therefore you get a deadlock.

In this case, I would change the insertion code so that the indices allow only one insertion to work, i.e. you have a unique key, and only one process will not be able to insert data, so there will be no duplicates. The update process is then performed in a transaction either

1) insert the tab first and handle the exception or error if it tries to insert a duplicate

or 2) do HOLD LOCK (Sybase and SQL Server) at the first choice - thus, the first one for blocking gets full permission to insert if necessary

or 3) Perhaps use the merge command if RDBMS allows it. This checks and inserts everything into one command, but will always modify the database.

EDIT: I think there is no real alternative to 1, if you need to make sure that it included one and only one record, since the test should be in a transaction.

cost can be reduced by checking forst in one transaction and then inserting and checking another transaction a second time. Thus, in most cases you have a choice, and in other cases you get a complete slow insertion and validation, but this should happen less often.

Answer 3

Not sure if it has SQL Server. But in MySQL and oracle, you can get write locks by making selections using the syntax for updating .

 select * from table for update 

Since other threads also require write locks when making selections, they will wait until the first thread completes the transaction.

Answer 4

I assume that you are using C # to communicate with the SQL server, then you can try to study the parallelism task and the task library for multithreading stored procedures.