防止SQL Server中的死锁 [英] Preventing deadlocks in SQL Server

问题描述

我有一个连接到SQL Server 2014数据库的应用程序，该数据库将几行合并为一个。在应用程序运行时，没有与此数据库的其他连接。

首先，选择特定时间范围内的一行行。此查询使用与群集查找合并的非群集搜索（时间列）。

  select ... 
从FOO 
开始，其中TIME> = @from，而TIME< @to and ... 
  

然后，我们在c＃中处理这些行并将更改作为单个更新写入以及多次删除，每个块多次发生。这些也使用非聚集索引搜寻。

 开始tran 
 
更新FOO设置... 
其中NON_CLUSTERED_ID = @id 
 
删除FOO其中NON_CLUSTERED_ID in（@ id1，@ id2，@ id3，...）
 
 commit 
  

在使用多个并行块运行此程序时，出现死锁。我尝试使用 ROWLOCK 进行更新和删除，但这

然后由于某些原因，导致死锁比以前更多。

然后我尝试了 TABLOCKX，HOLDLOCK 上的 ，但这意味着我不能并行执行 select ，所以我输了

有什么主意可以避免死锁，但仍然可以处理多个并行块吗？

会在这种情况下，由于块之间没有行重叠，在我的 select 上使用 NOLOCK 是安全的吗？然后 TABLOCKX，HOLDLOCK 只会阻止更新和删除

还是应该接受死锁并在应用程序中重试查询？

更新（其他信息）：到目前为止，所有死锁都发生在 update 和 delete 阶段，在 select 中都没有。如果今天无法解决（我之前未启用正确的跟踪标志），我将尝试获取一些死锁日志。

更新：这是 ROWLOCK 发生的两种死锁排列，它们都仅引用 delete 语句及其使用的非聚集索引。我不确定这些是否与在没有任何表提示的情况下发生的死锁相同，因为我无法重现其中的任何一个。

问一下.xdl是否需要其他内容，我是

解决方案

有关死锁的一般建议：确保以相同的顺序进行所有操作，即

您可以在microsoft.com上的此技术文章中找到关于最小化死锁。

• 以相同的顺序访问对象。
  strong>


• 避免交易中的用户交互。


• 使交易保持简短且成批进行。


• 使用较低的隔离级别。


• 使用基于行版本控制的隔离级别。


• 将READ_COMMITTED_SNAPSHOT数据库选项设置为ON，以使读取提交的事务能够使用行


• 使用快照隔离。


• 使用绑定连接。

---

问题在Cato提出后更新：

如何以相同的顺序获取锁在这里适用？您是否对他将如何更改其SQL提出任何建议？

无论何种环境，死锁始终相同：两个进程（例如 A 和 B ）获得多个锁（例如 X 和 Y ），以便 A 正在等待 Y 和 B 正在等待 X ，而 A 持有 X ，而 B 持有 Y 。

此处适用，因为 DELETE 和 UPDATE 语句隐式获取行或索引范围或表上的锁（取决于引擎认为合适的锁）。

您应该分析您的过程，看看是否存在可以以不同顺序获取锁的情况。如果没有显示任何内容，则可以使用SQL Server Profiler分析死锁：

要跟踪死锁事件，请将死锁图事件类添加到跟踪中。此事件类使用有关死锁中涉及的进程和对象的XML数据填充跟踪中的TextData数据列。 SQL Server Profiler可以将XML文档提取到死锁XML（.xdl）文件，您可以稍后在SQL Server Management Studio中查看。您可以配置SQL Server Profiler来将死锁图事件提取到包含所有死锁图事件的单个文件中，也可以将文件分离。

I have an application connected to a SQL Server 2014 database that combines several rows into one. There are no other connections to this database while the application is running.

First, select a chunk of rows within a specific time span. This query uses a non-clustered seek (TIME column) merged with a clustered lookup.

select ...
from FOO
where TIME >= @from and TIME < @to and ...

Then, we process these rows in c# and write changes as a single update and multiple deletes, this happens many times per chunk. These also use non-clustered index seeks.

begin tran

update FOO set ...
where NON_CLUSTERED_ID = @id

delete FOO where NON_CLUSTERED_ID in (@id1, @id2, @id3, ...)

commit

I am getting deadlocks when running this with multiple parallel chunks. I tried using ROWLOCK for the update and delete but that caused even more deadlocks than before for some reason, even though there are no overlaps between chunks.

Then I tried TABLOCKX, HOLDLOCK on the update, but that means I can't perform my select in parallel so I'm losing the advantages of parallelism.

Any idea how I can avoid deadlocks but still process multiple parallel chunks?

Would it be safe to use NOLOCK on my select in this case, given there is no row overlap between chunks? Then TABLOCKX, HOLDLOCK would only block the update and delete, correct?

Or should I just accept that deadlocks will happen and retry the query in my application?

UPDATE (additional information): All deadlocks so far have happened in the update and delete phase, none in the select. I'll try to get some deadlock logs up if I can't get this solved today (the correct trace flags weren't enabled before).

UPDATE: These are the two arrangements of deadlocks that occur with ROWLOCK, they both refer only to the delete statement and the non-clustered index it uses. I'm not sure if these are the same as the deadlocks that occur without any table hints as I wasn't able to reproduce any of those.

Ask if there's anything else needed from the .xdl, I'm a bit weary of attaching the whole thing.

解决方案

The general advice regarding deadlocks: make sure you do everything in the same order, i.e. acquire locks in the same order, for different processes.

You can find the same advice in this technical article on microsoft.com regarding Minimizing Deadlocks. There's a good reason it is listed first.

• Access objects in the same order.
• Avoid user interaction in transactions.
• Keep transactions short and in one batch.
• Use a lower isolation level.
• Use a row versioning-based isolation level.
• Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
• Use snapshot isolation.
• Use bound connections.

---

Update after question from Cato:

How would acquiring locks in the same order apply here? Have you got any advice on how he would change his SQL to do that?

Deadlocks are always the same, no matter what environment: two processes (say A & B) acquire multiple locks (say X & Y) in a different order so that A is waiting for Y and B is waiting for X while A is holding X and B is holding Y.

It applies here because DELETE and UPDATE statements implicitely acquire locks on the rows or index range or table (depending on what the engine deems appropriate).

You should analyze your process and see if there are scenarios where locks could be acquired in a different order. If that doesn't reveal anything, you can analyze deadlocks using the SQL Server Profiler:

To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. You can configure SQL Server Profiler to extract Deadlock graph events to a single file that contains all Deadlock graph events, or to separate files.

这篇关于防止SQL Server中的死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！