为什么 SQL Server 会发生死锁？

Why do deadlocks happen in SQL Server?

提问by tsilb

So as I understand it, SQL deadlocks happen when a SPID is busy processing another query and it can't be bothered to run another one because it's so busy right now. The SQL Server "randomly" picks one of the queries to deadlock out of the resources asked for and fails it out, throwing an exception.

因此，据我所知，当 SPID 忙于处理另一个查询时，就会发生 SQL 死锁，并且由于它现在很忙，因此无法运行另一个查询。SQL Server“随机”从请求的资源中选择一个要死锁的查询，然后失败，抛出异常。

I have an app running ~ 40 instances and a back-end Windows Service, all of which are hitting the same database. I'm looking to reduce deadlocks so I can increase the number of threads I can runs simultaneously.

我有一个运行大约 40 个实例的应用程序和一个后端 Windows 服务，所有这些都访问同一个数据库。我希望减少死锁，这样我就可以增加可以同时运行的线程数。

1. Why can't SQL Server just enqueue the new query and run it when it has time and the resources are available? Most of what I'm doing can wait a few seconds on occasion.
2. Is there a way to set Transaction Isolation Levelglobally without having to specify it at the onset of each new connection/session?

1. 为什么 SQL Server 不能将新查询加入队列并在有时间和资源可用时运行它？我正在做的大部分事情有时会等待几秒钟。
2. 有没有办法全局设置事务隔离级别，而不必在每个新连接/会话开始时指定它？

回答by Tom H

Your understanding of deadlocks is not correct. What you've described is blocking. It's a common mistake to equate the two.

你对死锁的理解是不正确的。你所描述的是阻塞。将两者等同是一个常见的错误。

A deadlock occurs when two separate transactions each want different resources and neither will release the one that they have so that the other can run. It's probably easier to illustrate:

当两个独立的事务各自需要不同的资源并且都不会释放它们拥有的一个以便另一个可以运行时，就会发生死锁。可能更容易说明：

SPID #1 gets a lock on resource A SPID #2 gets a lock on resource B SPID #1 now needs a lock on resource B in order to complete SPID #2 now needs a lock on resource A in order to complete

SPID #1 获得对资源 A 的锁定 SPID #2 获得对资源 B 的锁定 SPID #1 现在需要对资源 B 的锁定才能完成 SPID #2 现在需要对资源 A 的锁定才能完成

SPID #1 can't complete (and therefor release resource A) because SPID #2 has it SPID #2 can't complete (and therefor release resource B) because SPID #1 has it

SPID #1 无法完成（因此释放资源 A），因为 SPID #2 拥有它 SPID #2 无法完成（因此释放资源 B），因为 SPID #1 拥有它

Since neither SPID can complete one has to give up (i.e. be chosen by the server as the deadlock victim) and will fail.

由于两个 SPID 都无法完成，因此必须放弃（即被服务器选择为死锁受害者）并且会失败。

The best way to avoid them is to keep your transactions small (in number of resources needed) and quick.

避免它们的最佳方法是保持您的交易小（需要的资源数量）和快速。

回答by Schroedingers Cat

Deadlock is where two threads of processing are both being held up by the other ( it can be more, but two is sufficiently complex ). So one thread locks a table, then requests a lock on another table. the other table is locked by the second thread, which cannot progress because it is waiting for a lock on the first table.

死锁是两个处理线程都被另一个阻塞的地方（它可以更多，但两个已经足够复杂了）。因此，一个线程锁定一个表，然后请求另一个表上的锁定。另一个表被第二个线程锁定，由于它正在等待第一个表上的锁定，因此无法继续进行。

The reason that one of these has to be thrown out is that in a deadlock, they will never end - neither thread can progress at all. The only answer is for one to be stopped to allow the other to complete.

必须抛出其中之一的原因是，在死锁中，它们永远不会结束——两个线程根本无法进行。唯一的答案是停止其中一个以允许另一个完成。

The solution to reducing deadlocks in the sort of situation you are talking about may be to redesign the solution. If you can make sure that less locking occurs, you will have less deadlocks.

在您所谈论的那种情况下减少死锁的解决方案可能是重新设计解决方案。如果您可以确保发生更少的锁定，那么死锁就会更少。

回答by Adilson de Almeida Jr

Deadlocks occurs because, two concurrent transactions may overlap e lock different resources, both required by the other transaction to finish.

发生死锁的原因是，两个并发事务可能会重叠锁定不同的资源，而这两个事务都需要另一个事务才能完成。

Let's imagine: 1 - Transaction A locks row1 2 - Transaction B locks row2 3 - Transaction A tries to lock row1, and, because of the previous lock, SQL server waits 4 - Transaction B tries to lock row2, and, because of the previous lock, SQL server waits

让我们想象一下： 1 - 事务 A 锁定 row1 2 - 事务 B 锁定 row2 3 - 事务 A 尝试锁定 row1，并且，由于先前的锁定，SQL Server 等待 4 - 事务 B 尝试锁定 row2，并且，由于先前的锁定锁定，SQL 服务器等待

So, SQL server must choose on transaction, kill it, and allow the other to continue.

因此，SQL 服务器必须选择事务，杀死它，并允许其他事务继续。

This image ilustrates this situation very well: http://www.eupodiatamatando.com/wp-content/uploads/2008/01/deadlocknajkcomafarialibh3.jpg

这张图片很好地说明了这种情况：http: //www.eupodiatamatando.com/wp-content/uploads/2008/01/deadlocknajkcomafarialibh3.jpg