我正在尝试在SQL Server表中插入/更新行(取决于它是否存在(。我正在多台计算机上从多个线程执行 SQL，我想避免出现重复的键错误。
我在网上找到了很多解决方案，但所有这些解决方案都会导致交易死锁。这是我一直在使用的一般模式：
BEGIN TRANSACTION
UPDATE TestTable WITH (UPDLOCK, SERIALIZABLE)
SET Data = @Data
WHERE Key = @Key
IF(@@ROWCOUNT = 0)
BEGIN
INSERT INTO TestTable (Key, Data)
VALUES (@Key, @Data)
END
COMMIT TRANSACTION
我试过：
-
WITH XLOCK而不是UPDLOCK -
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE开头的UPDLOCK -
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE，没有表格提示
我还尝试了上述所有组合的以下模式：
BEGIN TRANSACTION
IF EXISTS (SELECT 1 FROM TestTable WITH (UPDLOCK, SERIALIZABLE) WHERE Key=@Key)
BEGIN
UPDATE TestTable
SET Data = @Data
WHERE Key = @Key
END
ELSE
BEGIN
INSERT INTO TestTable (Key, Data)
VALUES (@Key, @Data)
END
COMMIT TRANSACTION
我可以让它在没有死锁的情况下工作的唯一方法是使用 WITH (TABLOCKX) .
我使用的是SQL Server 2005，SQL是在运行时生成的，因此它不在存储过程中，并且某些表使用组合键而不是主键，但我可以在具有整数主键的表上重现它。
服务器日志如下所示：
waiter id=processe35978 mode=RangeS-U requestType=wait
waiter-list
owner id=process2ae346b8 mode=RangeS-U
owner-list
keylock hobtid=72057594039566336 dbid=28 objectname=TestDb.dbo.TestTable indexname=PK_TestTable id=lock4f4fb980 mode=RangeS-U associatedObjectId=72057594039566336
waiter id=process2ae346b8 mode=RangeS-U requestType=wait
waiter-list
owner id=processe35978 mode=RangeS-U
owner-list
keylock hobtid=72057594039566336 dbid=28 objectname=TestDb.dbo.TestTable indexname=PK_TestTable id=lock2e8cbc00 mode=RangeS-U associatedObjectId=72057594039566336
根据所使用的表提示，模式显然不同(但进程始终在等待它们已经拥有的模式(。我见过RangeS-U，RangeX-X和U。
我做错了什么？
如何先在表上使用连接进行插入以检查它是否存在：
BEGIN TRANSACTION
WITH ToInsert AS(
SELECT @Key AS Key, @Data AS Data
)
INSERT INTO TestTable (Key, Data)
SELECT ti.Key, ti.Data
FROM ToInsert ti
LEFT OUTER JOIN TestTable t
ON t.Key = ti.Key
WHERE t.Key IS NULL
IF(@@ROWCOUNT = 0)
BEGIN
UPDATE TestTable WITH (UPDLOCK, SERIALIZABLE)
SET Data = @Data
WHERE Key = @Key
END
COMMIT TRANSACTION
这样，您的 UPDATE 语句就可以确保始终存在一条记录，并且您的 INSERT 和 INSERT-check 位于同一个原子语句中，而不是两个单独的语句。
我今天又看了一遍，发现我有点麻木了。我实际上在跑步：
BEGIN TRANSACTION
IF EXISTS (SELECT 1 FROM TestTable WITH (UPDLOCK, SERIALIZABLE) WHERE Key=@Key)
BEGIN
UPDATE TestTable
SET Data = @Data, Key = @Key -- This is the problem
WHERE Key = @Key
END
ELSE
BEGIN
INSERT INTO TestTable (Key, Data)
VALUES (@Key, @Data)
END
COMMIT TRANSACTION
我自己锁了钥匙。咄！
您的死锁在索引资源上。
在执行计划中查找书签/键查找并创建一个覆盖这些字段的非聚集索引 - 这样，UPDATE 数据的"读取"就不会与 INSERT 的"写入"冲突。