SQL Server 锁 - 避免插入重复条目

Question

在阅读了很多与上述主题相关的文章和答案之后，我仍然想知道 SQL Server 数据库引擎在以下示例中是如何工作的:

假设我们有一个名为 t3 的表:

create table t3 (a int , b int);
create index test on t3 (a);

查询如下:

INSERT INTO T3
SELECT -86,-86
WHERE NOT EXISTS (SELECT 1 FROM t3 where t3.a=-86);

查询根据“a”列验证该行不存在后，在表 t3 中插入​​一行。

许多文章和答案表明使用上述查询不可能将一行插入两次。

对于上述查询的执行，我假设数据库引擎的工作方式如下:

1. 首先执行子查询。
2. 数据库引擎在范围内设置共享锁。
3. 读取数据。
4. 共享锁被释放。根据 MSDN 共享锁一旦数据被释放已阅读。
5. 如果一行不存在，它会在表中插入一个新行。
6. 新行被独占锁(x)锁定

现在考虑以下场景:

1. 上述查询由处理器 A (SPID 1) 执行。
2. 相同的查询由处理器 B (SPID 2)。
3. [SPID 1] 数据库引擎设置共享锁
4. [SPID 1] 子查询读取数据。现在返回行。
5. [SPID 1] 共享锁是发布。
6. [SPID 2] 数据库引擎设置了一个共享锁
7. [SPID 2] 子查询读取数据。没有返回任何行。
8. [SPID 2] 共享锁是发布。
9. 两个进程都进行了行插入(我们得到了一个重复的条目)。

我错过了什么吗？以上方法是否是避免重复条目的正确方法？

避免重复条目的安全方法是使用下面的代码，但我只是想知道上面的方法是否正确。

begin tran
    if (SELECT 1 FROM t3 with (updlock) where t3.a=-86)
    begin
        INSERT INTO T3
        SELECT -86,-86
    end
commit

Answer 1

如果您对列只有唯一约束，则永远不会有重复项。

您概述的技术将避免您在(第二个“同时”)操作失败的情况下必须捕获错误或异常。

我想补充一点，依靠“外部”代码(甚至 T-SQL)来强制执行数据库一致性并不是一个好主意。在所有情况下，无论应用程序代码编写得好与否，在表级别使用声明性参照完整性对于数据库确保一致性和匹配预期都很重要。与安全性一样，您需要利用纵深防御策略——约束、唯一索引、触发器、存储过程和 View 都可以帮助制定多层方法，以确保数据库为应用程序提供一致且可靠的接口(interface)或系统。