可以添加主键标识列解决死锁问题吗？ [英] Can adding a primary key identity column solve deadlock issues?

问题描述

我在SQL服务器中有一个表，该表通过在不同会话中同时运行的存储过程同时进行CRUD编码：

| - -------------- | --------- |

| <一些列> | JobGUID |

| ---------------- | --------- |

程序工作原理如下：

1. 生成GUID。


2. 在上述共享表中插入一些记录，用第1步的GUID标记。


3. 执行





存储过程中的每个select / insert / update / delete语句都有一个 WHERE JobGUID = @jobGUID 子句，因此该过程仅适用于其插入的记录但是，有时当相同的存储过程在不同连接中并行运行时，共享表上会发生死锁。这是SQL Server Profiler的死锁图：








不会发生锁定升级。我尝试添加（UPDLOCK，ROWLOCK）锁定提示到所有DML语句和/或在事务中包装过程的主体，并使用不同的隔离级别，但它没有帮助。共享表上仍然有相同的RID锁。​​





之后，我发现共享表没有主键/标识列。一旦我添加它，死锁似乎已经消失：

  alter table< SharedTable>添加ID int not null标识（1，1）主键聚集
  

当我删除主键列，死锁回来了。当我添加它，我不能再生死锁了。





所以，问题是，是一个主键标识列真正能够解决死锁或者它只是一个巧合？





更新：为 @Catcall 建议，我已经尝试在现有列上创建一个自然的聚集主键（不添加标识列），但仍然遇到相同的死锁（当然，这次是一个键锁

$ p


解决方案

死锁解析的最佳资源（静态）如下： http://blogs.msdn.com/b/bartd/archive /2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx 。





Pt＃4说：




运行死锁中涉及的查询数据库调整
Advisor。在Management Studio查询窗口中敲击查询，将
db上下文更改为正确的数据库，右键单击查询文本和
选择DTA中的分析查询。不要跳过此步骤;我们看到的死锁问题的一半以上
只是通过添加一个
适当的索引来解决，以便其中一个查询运行更快，
具有更小的锁占用空间。如果DTA推荐索引（它会说
估计改进：％），创建它们并监视
，看看死锁是否仍然存在。您可以从操作下拉菜单中选择应用建议
立即创建索引，或
将CREATE INDEX命令保存为脚本，以在
维护窗口期间创建它们。请务必分别调整每个查询。




我知道这不会回答问题为什么必然，确实表明添加索引可以改变执行方式，使锁定占用更小或执行时间更快，这可以显着降低死锁的机会。




I have a table in SQL server that is CRUD-ed concurrently by a stored procedure running simultaneously in different sessions:

|----------------|---------|
| <some columns> | JobGUID |
|----------------|---------|


The procedure works as follows:

1. Generate a GUID.
2. Insert some records into the shared table described above, marking them with the GUID from step 1.
3. Perform a few updates on all records from step 2.
4. Select the records from step 3 as SP output.

Every select / insert / update / delete statement in the stored procedure has a WHERE JobGUID = @jobGUID clause, so the procedure works only with the records it has inserted on step 2. However, sometimes when the same stored procedure runs in parallel in different connections, deadlocks occur on the shared table. Here is the deadlock graph from SQL Server Profiler:

Lock escalations do not occur. I tried adding (UPDLOCK, ROWLOCK) locking hints to all DML statements and/or wrapping the body of the procedure in a transaction and using different isolation levels, but it did not help. Still the same RID lock on the shared table.

After that I've discovered that the shared table did not have a primary key/identity column. And once I added it, deadlocks seem to have disappeared:

alter table <SharedTable> add ID int not null identity(1, 1) primary key clustered

When I remove the primary key column, the deadlocks are back. When I add it back, I cannot reproduce the deadlock anymore.

So, the question is, is a primary key identity column really able to resolve deadlocks or is it just a coincidence?

Update: as @Catcall suggests, I've tried creating a natural clustered primary key on the existing columns (without adding an identity column), but still caught the same deadlock (of course, this time it was a key lock instead of RID lock).

解决方案

The best resource (still) for deadlock resolution is here: http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx.

Pt #4 says:

Run the queries involved in the deadlock through Database Tuning Advisor. Plop the query in a Management Studio query window, change db context to the correct database, right-click the query text and select "Analyze Query in DTA". Don’t skip this step; more than half of the deadlock issues we see are resolved simply by adding an appropriate index so that one of the queries runs more quickly and with a smaller lock footprint. If DTA recommends indexes (it'll say "Estimated Improvement: %"), create them and monitor to see if the deadlock persists. You can select "Apply Recommendations" from the Action drop-down menu to create the index immediately, or save the CREATE INDEX commands as a script to create them during a maintenance window. Be sure to tune each of the queries separately.

I know this doesn't "answer" the question to why necessarily, but it does show that adding indexes can change the execution in ways to make either the lock footprint smaller or execution time faster which can significantly reduce the chances of a deadlock.

这篇关于可以添加主键标识列解决死锁问题吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！