在 tsql 中，就并发而言，带有 Select 语句的 Insert 是安全的吗? [英] In tsql is an Insert with a Select statement safe in terms of concurrency?

问题描述

在我对这个问题的回答中我建议使用单个插入语句，并带有一个递增值的选择，如下所示.

In my answer to this SO question I suggest using a single insert statement, with a select that increments a value, as shown below.

Insert Into VersionTable 
(Id, VersionNumber, Title, Description, ...) 
Select @ObjectId, max(VersionNumber) + 1, @Title, @Description 
From VersionTable 
Where Id = @ObjectId 

我建议这样做是因为我相信这个语句在并发方面是安全的，因为如果同一对象 ID 的另一个插入同时运行，则没有机会重复版本号.

I suggested this because I believe that this statement is safe in terms of concurrency, in that if another insert for the same object id is run at the same time, there is no chance of having duplicate version numbers.

我说得对吗?

推荐答案

正如 Paul 所写:不，它不安全，为此我想添加经验证据:创建表格 Table_1 有一个字段 ID 和一个值为 0 的记录.然后在两个 Management Studio 查询窗口中同时执行以下代码:

As Paul writes: No, it's not safe, for which I would like to add empirical evidence: Create a table Table_1 with one field ID and one record with value 0. Then execute the following code simultaneously in two Management Studio query windows:

declare @counter int
set @counter = 0
while @counter < 1000
begin
  set @counter = @counter + 1

  INSERT INTO Table_1
    SELECT MAX(ID) + 1 FROM Table_1 

end

然后执行

SELECT ID, COUNT(*) FROM Table_1 GROUP BY ID HAVING COUNT(*) > 1

在我的 SQL Server 2008 上，一个 ID (662) 被创建了两次.因此，应用于单个语句的默认隔离级别不够.

On my SQL Server 2008, one ID (662) was created twice. Thus, the default isolation level applied to single statements is not sufficient.

显然，用 BEGIN TRANSACTION 和 COMMIT 包装 INSERT 不会修复它，因为事务的默认隔离级别是仍然 READ COMMITTED，这还不够.请注意，将事务隔离级别设置为 REPEATABLE READ 也 还不够.使上述代码安全的唯一方法是添加

Clearly, wrapping the INSERT with BEGIN TRANSACTION and COMMIT won't fix it, since the default isolation level for transactions is still READ COMMITTED, which is not sufficient. Note that setting the transaction isolation level to REPEATABLE READ is also not sufficient. The only way to make the above code safe is to add

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

在顶部.然而，这在我的测试中时不时地导致死锁.

at the top. This, however, caused deadlocks every now and then in my tests.

我发现的唯一安全并且不会产生死锁(至少在我的测试中)的解决方案是显式地以独占方式锁定表(此处默认事务隔离级别就足够了).不过要小心；此解决方案可能会杀死性能:

The only solution I found which is safe and does not produce deadlocks (at least in my tests) is to explicitly lock the table exclusively (default transaction isolation level is sufficient here). Beware though; this solution might kill performance:

...loop stuff...
    BEGIN TRANSACTION

    SELECT * FROM Table_1 WITH (TABLOCKX, HOLDLOCK) WHERE 1=0

    INSERT INTO Table_1
      SELECT MAX(ID) + 1 FROM Table_1 

    COMMIT
...loop end...

这篇关于在 tsql 中，就并发而言，带有 Select 语句的 Insert 是安全的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！