主页 / dba / 问题 / 36953
Accepted
squillman
Asked: 2013-03-19 10:50:39 +0800 CST

唯一索引键违规

  • 772

我有一个带有 PK 和唯一非聚集索引的表，如下所示：

CREATE TABLE Table1
(
    Id INT IDENTITY(1,1) NOT NULL,
    Field1 VARCHAR(25) NOT NULL,
    Field2 VARCHAR(25) NULL,
 CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (Id ASC)
)

CREATE UNIQUE NONCLUSTERED INDEX IX_Field1_Field2 ON Table1
(
    Field1 ASC,
    Field2 ASC
)
WITH
    (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        SORT_IN_TEMPDB = OFF,
        IGNORE_DUP_KEY = OFF,
        DROP_EXISTING = OFF,
        ONLINE = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    )

我有 2 个作业，我发现它们的执行时间相互重叠。它们都将相同的内容包含在此表中，并且最后开始的作业经常失败，因为它尝试使用重复的索引键值INSERT插入记录。Table1

INSERT Table1
SELECT Field1, Field2
FROM SomeOtherTable sot WITH (NOLOCK)
WHERE NOT EXISTS (
    SELECT 1
    FROM Table1 t1
    WHERE sot.Field1 = t1.Field1
    AND sot.Field2 = t1.Field2
)

据我所知，当评估来自 Job2 的 Job2 导致 Job2 尝试插入重复的键值时，Job1 中的INSERTin 仍在执行。NOT EXISTS在我看来，锁定Table1并没有按预期发生。

我不知道为什么会这样。NOLOCK这与 中使用的提示有什么关系INSERT？我不认为该提示将包含Table1在其范围内，仅SomeOtherTable.

IGNORE_DUP_KEY我知道我可以通过将索引设置为来减轻重复键错误，ON在这种情况下这对我们来说很好。不过，我想知道为什么重复项出现在第二次插入中。

sql-server-2008-r2 locking

1 个回答

  1. Best Answer

    如果您想完全理解 SQL Server 中锁定和隔离级别的所有复杂性，有很多东西需要掌握，而您的问题本质上是在引导您朝这个方向发展。

    所有信息都可以在http://msdn.microsoft.com/en-us/library/ms173763.aspx上找到，虽然它不是一个容易阅读的。http://databases.about.com/od/sqlserver/a/isolationmodels.htm上提供了更容易阅读的内容- 请特别注意“幻读”部分。

    对于您正在尝试做的事情，您需要以“SERIALIZABLE”隔离级别运行的两个事务。这基本上意味着它们将“好像”一个接一个地运行——它们实际上是序列化的。

    发生的事情是两个事务都决定需要插入相同的记录，然后两者都尝试插入相同的记录。测试逻辑获取的锁与插入获取的锁是分开的，这允许这种情况发生。SERIALIZABLE 将通过它的“其他事务不能插入新行，其键值将落在当前事务中的任何语句读取的键范围内，直到当前事务完成”子句来阻止这种情况的发生。另一种思考方式是，测试中的读锁需要一直保持到整个事务完成。这可以使用 HOLDLOCK 查询提示来指定；但我不得不说我没有亲自使用 HOLDLOCK，而只是使用“SET TRANSACTION ISOLATION LEVEL SERIALIZABLE”

    • 2

相关问题