database 什么是数据库中的死锁？

What is a deadlock in a database?

提问by VenkatReddy.Ravu

What is a deadlock in SQL Server and when it arises?

什么是 SQL Server 中的死锁以及它何时出现？

What are the issues with deadlock and how to resolve it?

死锁有哪些问题以及如何解决？

回答by Pz.

In general, deadlock means that two or more entities are blocking some sources, and none of them is able to finish, because their 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 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 firstly, then locks A.

一个例子：假设我有表 A 和表 B，我需要在 A 和 B 中做一些更新，我决定在使用时锁定它们（这真的是愚蠢的行为，但现在它已经达到目的了） . 在同一时刻，其他人以相反的顺序做同样的事情——先锁定 B，然后锁定 A。

Chronologically, this happens:

按时间顺序，这会发生：

proc1: Lock A

proc1：锁定A

proc2: Lock B

proc2：锁B

proc1: Lock B - starts waiting until proc2 releases B

proc1：锁定 B - 开始等待直到 proc2 释放 B

proc2: Lock A - starts waiting until proc1 releases A

proc2：锁定 A - 开始等待直到 proc1 释放 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. 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.

死锁的一个真实例子是，当您将房钥匙锁在车内，而车钥匙则锁在房内。

回答by Paul

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.

死锁是当两个人需要多个资源来执行时发生的情况，并且每个人都锁定了一些资源。这导致了这样一个事实，如果没有 B 拥有的东西，A 就无法执行，反之亦然。

Lets say I have Person A and Person B. They both need to get two rows to run (Row1 and Row2).

假设我有 Person A 和 Person B。他们都需要运行两行（Row1 和 Row2）。

• Person A locks Row1 and tries to get Row2.
• Person B locks Row2 and tries to get Row1.

• 人 A 锁定 Row1 并尝试获取 Row2。
• 人 B 锁定 Row2 并尝试获取 Row1。

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.

A 不能运行，因为它需要 Row2，B 不能运行，因为它需要 Row1。任何人都无法执行，因为他们锁定了对方的需求，反之亦然。

---

---

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.

减少死锁的一种相当简单的方法是在所有复杂的事务中，您应该以相同的顺序执行操作。换句话说，以相同的顺序访问 Table1 然后 Table2。这将有助于减少发生死锁的次数。

回答by Vlad Mihalcea

What is a deadlock

什么是死锁

As I explained in this article, 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.

例如，如果两个或多个线程正在等待先前获取的锁，则多线程程序可能会死锁，因此没有线程可以取得任何进展。如果这种情况发生在 Java 应用程序中，JVM 不能只是强制线程停止执行并释放其锁。

Even if the Threadclass exposes a stopmethod, 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 an interruptmethod, which acts as a hint as a thread that gets interrupted can simply ignore the interruption and continue its execution.

即使Thread该类公开了一个stop方法，该方法自 Java 1.1 以来已被弃用，因为它可能导致对象在线程停止后处于不一致状态。相反，Java 定义了一个interrupt方法作为提示，因为被中断的线程可以简单地忽略中断并继续执行。

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.

因此，Java 应用程序无法从死锁情况中恢复，应用程序开发人员有责任以永远不会发生死锁的方式对锁获取请求进行排序。

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.

与 JVM 不同，数据库事务被设计为原子工作单元。因此，回滚使数据库处于一致状态。

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 rollback is the one whose statement will be rolled back.

根据Oracle 文档，检测到回滚的事务是其语句将被回滚的事务。

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.

SQL Server 允许您通过DEADLOCK_PRIORITY会话变量控制在死锁情况下哪个事务更有可能被回滚。

The DEADLOCK_PRIORITYsession can accept any integer between -10 and 10, or pre-defined values such as LOW (-5), NORMAL (0)or HIGH (5).

该DEADLOCK_PRIORITY会话可以接受任何整数-10和10之间，或预先定义的值，例如LOW (-5)，NORMAL (0)或HIGH (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.

如果发生死锁，当前事务将回滚，除非其他事务具有较低的死锁优先级值。如果两个事务具有相同的优先级值，则 SQL Server 将回滚成本最低的事务。

PostgreSQL

PostgreSQL

As explained in the documentation, PostgreSQL does not guarantee which transaction is to be rolled back.

如文档中所述，PostgreSQL 不保证回滚哪个事务。

MySQL

MySQL

MySQL tries to roll back the transaction that modified the leats number of records, as releasing fewer locks is less costly.

MySQL 尝试回滚修改 leas 记录数的事务，因为释放较少的锁成本较低。

For more details about this topic, check out this articleas well.

有关此主题的更多详细信息，请查看这篇文章。

回答by bibek khadgi

An impasse that may result when two (or more) transactions are each waiting for locks to be released that are held by the other.

当两个（或更多）事务都在等待释放对方持有的锁时，可能会导致僵局。