什么是数据库死锁？
SQL Server 中什么是死锁以及何时会出现死锁？
死锁有哪些问题以及如何解决？
What is a deadlock in SQL Server and when it arises?
What are the issues with deadlock and how to resolve it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
一般来说，死锁意味着两个或多个实体正在阻塞某些源，并且它们都无法完成，因为它们以循环方式阻塞源。
一个例子：假设我有表 A 和表 B，我需要在 A 中进行一些更新，然后在 B 中进行一些更新，我决定在使用时锁定它们（这确实是愚蠢的行为，但它现在达到了目的）。与此同时，其他人以相反的顺序执行相同的操作 - 首先锁定 B，然后锁定 A。
按时间顺序，会发生这种情况：
proc1: Lock A
proc2: Lock B
proc1: Lock B - 开始等待，直到 proc2 释放 B
proc2: Lock A - 开始等待，直到 proc1 释放 A
他们都不会完成。这就陷入了僵局。实际上，这通常会导致超时错误，因为不希望任何查询永远挂起，并且底层系统（例如数据库）将终止未及时完成的查询。
现实世界中的一个僵局例子是，当你把房子钥匙锁在车里，又把车钥匙锁在房子里时。
In general, deadlock means that two or more entities are blocking some sources, and none of them is able to finish, because they are blocking sources in a cyclic way.
One example: Let's say I have table A and table B, I need to do some update in A and then B and I decide to lock both of them at the moment of usage (this is really stupid behaviour, but it serves it's purpose now). At the same moment, someone else does the same thing in opposite order - locks B first, then locks A.
Chronologically, this happens:
proc1: Lock A
proc2: Lock B
proc1: Lock B - starts waiting until proc2 releases B
proc2: Lock A - starts waiting until proc1 releases A
Neither of them will ever finish. That's a deadlock. In practice this usually results in timeout errors since it is not desired to have any query hanging forever, and the underlying system (e.g. the database) will kill queries that don't finish in time.
One real world example of a deadlock is when you lock your house keys in your car, and your car keys in your house.
什么是死锁
当两个并发事务因等待对方释放锁而无法取得进展时，就会发生死锁，如下图所示。
因为两个事务都处于锁获取阶段，所以没有一个事务在获取下一个锁之前释放锁。
从死锁情况中恢复
如果您使用依赖于锁的并发控制算法，那么始终存在在死锁情况下运行的风险。死锁可能发生在任何并发环境中，而不仅仅是数据库系统中。
例如，如果两个或多个线程正在等待先前获取的锁，从而导致任何线程都无法取得任何进展，则多线程程序可能会死锁。如果这种情况发生在 Java 应用程序中，JVM 无法强制线程停止执行并释放其锁。
即使
Thread类公开stop方法，该方法自 Java 1.1 起已被弃用，因为它可能导致线程停止后对象处于不一致的状态。相反，Java 定义了一个interrupt方法，该方法充当一个提示，因为被中断的线程可以简单地忽略中断并继续执行。因此，Java 应用程序无法从死锁情况中恢复，应用程序开发人员有责任以永远不会发生死锁的方式对锁获取请求进行排序。
但是，数据库系统无法强制执行给定的锁获取顺序，因为无法预见某个事务将进一步获取哪些其他锁。保持锁定顺序成为数据访问层的责任，数据库只能协助从死锁情况中恢复。
数据库引擎运行一个单独的进程，该进程扫描当前冲突图以查找锁等待周期（由死锁引起）。
当检测到循环时，数据库引擎选择一个事务并中止它，导致其锁被释放，以便另一个事务可以取得进展。
与 JVM 不同，数据库事务被设计为原子工作单元。因此，回滚使数据库保持一致状态。
死锁优先级
当数据库选择回滚被卡住的两个事务之一时，并不总是能够预测哪一个将被回滚。根据经验，数据库可能会选择以较低的回滚成本回滚事务。
Oracle
根据 Oracle 文档，检测到的事务死锁是指其语句将被回滚的死锁。
SQL Server
SQL Server 允许您通过
DEADLOCK_PRIORITY会话变量。DEADLOCK_PRIORITY会话可以接受 -10 到 10 之间的任何整数，或预定义值，例如LOW (-5)、NORMAL (0)或高 (5)。如果发生死锁，当前事务将回滚，除非其他事务具有较低的死锁优先级值。如果两个事务具有相同的优先级值，则 SQL Server 将回滚回滚成本最小的事务。
PostgreSQL
正如文档中所述，PostgreSQL 不保证哪个事务将被执行被回滚。
MySQL
MySQL 尝试回滚修改最少记录数的事务，如下释放更少的锁成本更低。
What is a deadlock
A deadlock happens when two concurrent transactions cannot make progress because each one waits for the other to release a lock, as illustrated in the following diagram.
Because both transactions are in the lock acquisition phase, neither one releases a lock prior to acquiring the next one.
Recovering from a deadlock situation
If you're using a Concurrency Control algorithm that relies on locks, then there is always the risk of running in a deadlock situation. Deadlocks can occur in any concurrency environment, not just in a database system.
For instance, a multithreading program can deadlock if two or more threads are waiting on locks that were previously acquired so that no thread can make any progress. If this happens in a Java application, the JVM cannot just force a Thread to stop its execution and release its locks.
Even if the
Threadclass exposes astopmethod, that method has been deprecated since Java 1.1 because it can cause objects to be left in an inconsistent state after a thread is stopped. Instead, Java defines aninterruptmethod, which acts as a hint as a thread that gets interrupted can simply ignore the interruption and continue its execution.For this reason, a Java application cannot recover from a deadlock situation, and it is the responsibility of the application developer to order the lock acquisition requests in such a way that deadlocks can never occur.
However, a database system cannot enforce a given lock acquisition order since it's impossible to foresee what other locks a certain transaction will want to acquire further. Preserving the lock order becomes the responsibility of the data access layer, and the database can only assist in recovering from a deadlock situation.
The database engine runs a separate process that scans the current conflict graph for lock-wait cycles (which are caused by deadlocks).
When a cycle is detected, the database engine picks one transaction and aborts it, causing its locks to be released, so that the other transaction can make progress.
Unlike the JVM, a database transaction is designed as an atomic unit of work. Hence, a rollback leaves the database in a consistent state.
Deadlock priority
While the database chooses to rollback one of the two transactions being stuck, it's not always possible to predict which one will be rolled back. As a rule of thumb, the database might choose to roll back the transaction with a lower rollback cost.
Oracle
According to the Oracle documentation, the transaction that detected the deadlock is the one whose statement will be rolled back.
SQL Server
SQL Server allows you to control which transaction is more likely to be rolled back during a deadlock situation via the
DEADLOCK_PRIORITYsession variable.The
DEADLOCK_PRIORITYsession can accept any integer between -10 and 10, or pre-defined values such asLOW (-5),NORMAL (0)orHIGH (5).In case of a deadlock, the current transaction will roll back, unless the other transactions have a lower deadlock priority value. If both transactions have the same priority value, then SQL Server rolls back the transaction with the least rollback cost.
PostgreSQL
As explained in the documentation, PostgreSQL does not guarantee which transaction is to be rolled back.
MySQL
MySQL tries to roll back the transaction that modified the least number of records, as releasing fewer locks is less costly.
死锁是指当两个人需要多个资源来执行并且某些资源被每个人锁定时发生的情况。这导致 A 没有 B 所拥有的东西就无法执行，反之亦然。
假设我有 A 和 B。他们都需要运行两行（Row1 和 Row2）。
人 A 无法运行，因为它需要 Row2，人 B 无法运行，因为它需要 Row1。任何人都无法执行，因为他们锁定了对方的需求，反之亦然。
减少死锁的一种相当简单的方法是在所有复杂事务中，您应该以相同的顺序执行操作。换句话说，以相同的顺序访问 Table1，然后访问 Table2。这将有助于减少发生死锁的数量。
Deadlock is what happens when two people need multiple resources to execute, and where some of the resources are locked by each of the people. This leads to the fact that A can't execute without something B has and vice versa.
Lets say I have Person A and Person B. They both need to get two rows to run (Row1 and Row2).
Person A can't run because it needs Row2, Person B can't run because it needs Row1. Neither person will ever be able to execute because they're locking what the other needs and vice versa.
One reasonably simple way to reduce deadlock is in all your complex transactions, you should do operations in the same order. In other words, access Table1 then Table2 in the same order. This will help reduce the number of deadlocks that occur.
当两个（或多个）交易各自进行时可能会导致僵局
等待对方持有的锁被释放。
An impasse that may result when two (or more) transactions are each
waiting for locks to be released that are held by the other.
死锁是指一个进程或线程由于所请求的系统资源被另一个等待进程占用而进入等待状态
Deadlock is when a process or thread enters a waiting state because a requested system resources in held by another waiting process