对未提交的数据强制执行检查约束

Question

注意：我知道这个例子的更好做法是使用唯一索引，但这只是更复杂情况的简化例子

我有一张有两个值的表。我有一个检查约束来防止这两个值重复：

Value1    Value2
------    ------
1         1
1         2
1         3
2         1
2         2
2         3

鉴于上述数据，这应该（通常也确实）失败：

INSERT INTO Table1 VALUES(1,1)

消息547，级别16，状态0，第15行INSERT语句与CHECK约束"chkTable1_DuplicateValues"冲突。冲突发生在数据库"MyDB"的表"dbo.Table1"中。语句已终止。

然而，在竞争条件下——两个事务试图同时插入相同的数据——检查约束会失败，并允许数据。

以下是如何复制它：

CREATE TABLE Table1 (Value1 INT, Value2 INT); 
GO
CREATE FUNCTION CheckDuplicateValues(@value1 INT, @value2 INT) RETURNS INT AS 
BEGIN 
    RETURN (
        SELECT COUNT(*) FROM Table1 
        WHERE Value1 = @value1 
          AND Value2 = @value2
    );
END; 
GO
ALTER TABLE Table1 
    ADD CONSTRAINT chkTable1_DuplicateValues 
    CHECK (dbo.CheckDuplicateValues(Value1, Value2) = 1);
GO

然后，在两个独立的窗口（连接）中，在这两个窗口中运行以下操作：

BEGIN TRANSACTION;
INSERT INTO Table1 VALUES(1,1);

在两个窗口中，您都会看到：

（1行受影响）

我理解为什么会发生这种情况——基本上，CheckDuplicateValues函数中的查询只读取提交的数据，加上当前连接上新插入的数据，所以在这两种情况下，它都只计算当前连接中新插入的行。

我只是不确定修复它的最佳方法。我应该在我的UDF查询上抛出WITH (NOLOCK)提示吗：

SELECT COUNT(*) FROM Table1 WITH (NOLOCK)

我也不知道为什么在第二个查询发生COMMIT TRANSACTION时不强制执行检查约束。

有更好的方法来处理这个问题吗？同样，我知道对于这个特定的情况，唯一索引是最好的方法，但我的检查约束实际上涉及第二个表，所以我认为检查约束是确保数据完整性的最安全的方法。

Answer 1

因此，在您的示例中，只有当事务隔离级别设置为snapshot或数据库上的读取提交快照设置设置为on时，才会出现您描述的行为。如果未设置这些选项，则第一次插入将起作用，第二次插入将被阻止，直到您提交第一次，在该点上，第二插入将如预期的那样以违反约束的方式失败。

如果出于任何原因需要对数据库进行快照隔离，您可以修改检查约束中的函数，通过使用readcommittedlock表提示来复制在读提交隔离级别中会经历的行为，如下所示：

CREATE FUNCTION CheckDuplicateValues(@value1 INT, @value2 INT) RETURNS INT AS 
BEGIN 
    RETURN (
        SELECT COUNT(*) FROM Table1 WITH (READCOMMITTEDLOCK)
        WHERE Value1 = @value1 
          AND Value2 = @value2
    );
END; 
GO