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. 数据库引擎对范围设置共享锁(s)。
3. 读取数据。
4. 释放共享锁。根据MSDN，共享锁在数据被读取后立即释放。
5. 如果行不存在，则在表中插入新行。
6. 使用独占锁(x)锁定新行

现在考虑以下情况：

1. 处理器A(SPID 1)执行上述查询。
2. 处理器B(SPID 2)执行相同的查询。
3. [SPID 1]数据库引擎设置了一个共享锁(s)
4. [SPID 1]子查询读取数据。现在返回了几行。
5. [SPID 1]共享锁被释放。
6. [SPID 2]数据库引擎设置了一个共享锁(s)
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

- yioann

Answer 1

如果你只在列上设置唯一约束，就永远不会有重复数据。

你所概述的技巧将避免你在第二个“同时”操作失败的情况下捕获错误或异常。

我想补充一点，依赖于“外部”代码（即使是T-SQL）来强制执行数据库一致性并不是一个好主意。在所有情况下，使用表级声明性引用完整性对于数据库确保一致性和匹配预期非常重要，无论应用程序代码写得好还是不好。就像安全性一样，你需要利用防御深度策略——约束、唯一索引、触发器、存储过程和视图都可以帮助实现多层次方法，以确保数据库向应用程序或系统呈现一致可靠的接口。

- Cade Roux

---

是的，唯一约束/索引是防止重复的首选且实际上也是唯一可靠的方法。其他任何方式在某些情况下都注定会失败。 - undefined

Answer 2

为了在多个语句之间保持锁定，它们必须被包含在一个事务中。 在您的示例中：

If (SELECT 1 FROM t3 with (updlock) where t3.a=-86)
    INSERT INTO T3 SELECT -86,-86

更新锁可以在插入执行之前释放。这样可以可靠地工作：

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

单个语句总是被包裹在事务中，所以这也可以工作：

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

（假设您已关闭“隐式事务”，就像默认的SQL Server设置一样。）

- Andomar

---

没错。这两个语句必须包含在一个事务中。我已经纠正了问题中的语句。 所以你是说如果我们不强制执行更新锁，INSERT INTO... WHERE NOT EXISTS (...) 语法将无法工作。这是我知道的，但在阅读了那么多文章后甚至没有提到这一点之后，我开始怀疑是否真的需要。谢谢你的澄清。 - undefined