Cause of a process being a deadlock victim

I have a process with a Select which takes a long time to finish, on the order of 5 to 10 minutes.
I am currently not using NOLOCK as a hint to the MS SQL database engine.
At the same time we have another process doing updates and inserts into the same database and same tables.
The first process has started, recently to end prematurely with a message

SQLEXCEPTION: Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim.

This first process is running at other sites in identical conditions but with smaller databases and thus the select statement in question takes a much shorter period of time (on the order of 30 seconds or so). In these other sites, I don't get the deadlock message in these other sites. I also did not get this message at the site that is having the problem initially, but, I assume, as the database has grown, I believe I must have crossed some threshold. Here are my questions:

  1. Could the time it takes for a transaction to execute make the associated process more likely to be flagged as a deadlock victim.
  2. If I execute the select with a NOLOCK hint, will this remove the problem?
  3. I suspect that a datetime field that is checked as part of the WHERE clause in the select statement is causing the slow lookup time. Can I create an index based on this field? Is it advisable?
246800 次浏览

Q1:Could the time it takes for a transaction to execute make the associated process more likely to be flagged as a deadlock victim.

没有。SELECT 是受害者,因为它只读取数据,因此事务具有与之关联的 降低成本,因此被选为受害者:

默认情况下,数据库引擎选择 运行 回滚成本最低事务的会话。 或者,用户可以在 使用 SET DEADLOCK_PRIORITY语句的死锁情况。 可以将 DEADLOCK _ PRIORITY 设置为 LOW、 NORMAL 或 HIGH,也可以选择其他设置 可以设置为范围(-10到10)内的任何整数值。

如果我用一个 NOLOCK 提示执行选择,这会消除问题吗?

不,有几个原因:

问3。我怀疑在 select 语句中作为 WHERE 子句的一部分检查的 datetime 字段导致了缓慢的查找时间。我可以根据这个字段创建索引吗?明智吗?

也许吧。死锁的原因很可能是一个索引不好的数据库。10分钟的查询在如此狭窄的条件下是可以接受的,我100% 确定在您的情况下 没有是可以接受的。

我以99% 的信心声明,您的死锁是由于大型表扫描与更新冲突造成的。首先捕获 死锁图死锁图死锁图来分析原因。您很可能必须优化数据库的模式。在进行任何修改之前,请阅读本主题 设计索引和子文章。

下面是这个特殊的死锁问题实际上是如何发生的,以及它实际上是如何解决的。这是一个相当活跃的数据库,每天发生130K 事务。该数据库中的表中的索引最初是聚集的。客户机要求我们使索引不聚集。一旦我们这样做了,僵局就开始了。当我们将索引重新建立为聚集索引时,死锁停止了。

这里的答案值得一试,但是您也应该检查您的代码。具体阅读一下 Polyfun 的回答: 如何在 SQLServer2005和 C # 应用程序中消除死锁?

它解释了并发性问题,以及在查询中使用“ with (updlock)”如何纠正死锁情况——这确实取决于代码正在做什么。如果您的代码确实遵循这种模式,那么在使用脏读操作之前,这可能是一个更好的修复方法。

虽然@Remus Rusanu已经是一个很好的答案,但是如果有人希望更好地了解 SQL Server's Deadlock的起因和追踪策略,我建议你阅读 Brad McGehee如何使用 SQLServer2005事件探查器跟踪死锁