插入并发问题 - 多线程环境 [英] Insert Concurrency Issue - Multithreaded Environment

问题描述

我有一个问题，同一个存储过程正在同一时间调用完全相同的paramenters。

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

存储过程的目的是抓取

问题是两个线程都检查记录的存在，并且报告为false，如果不存在则创建和获取记录。

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.

任何帮助将不胜感激，

谢谢。

推荐答案

诀窍是在INSERT语句中添加WHERE，不存在，后跟SELECT语句。假设记录可以通过ID列标识，您可以这样写：

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 

您不需要将语句放在事务中，因为每个语句都在其自己的隐式事务中执行。因此，两个INSERTS不能同时成功。

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 ：INSERT ... SELECT语法是必要的，因为TSQL不允许INSERT语句中的VALUES和WHERE部分。

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

这篇关于插入并发问题 - 多线程环境的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！