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

Jerad Rose 发表于 Dev

杰拉德·罗斯（Jerad Rose）

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

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

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函数中的查询仅读取已提交的数据，以及当前连接上新插入的数据，因此，在两种情况下，它都仅计算当前连接中新插入的行。

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

SELECT COUNT(*) FROM Table1 WITH (NOLOCK)

我也不确定为什么COMMIT TRANSACTION第二个查询发生后为什么不强制执行检查约束。

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

偷窃

因此，在您的示例中，仅当将事务隔离级别设置为快照，或者将数据库上的已读提交快照设置设置为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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。