MySQL和PostgreSQL中的死锁_process waits for sharelock

并发控制是与数据库一起使用的概念，可确保并发执行数据库事务而不会破坏数据完整性。

关于此概念及其实现方法有很多理论和不同方法，但是我们将简要介绍PostgreSQL和MySQL（使用InnoDB时）处理它的方式，以及在高并发系统中可能出现的常见问题：死锁（deadlock）。

这些引擎通过使用称为MVCC（多版本并发控制）的方法来实现并发控制。在此方法中，当更新项目时，更改不会覆盖原始数据，而是将创建该项目的新版本（包含更改）。因此，我们将存储该项目的多个版本。

该模型的主要优点之一是为查询（读取）数据而获取的锁与为写入数据而获取的锁不冲突，因此读取永远不会阻止写入，而写入永远不会阻止读取。

但是，如果存储了同一项目的多个版本，那么交易将看到哪个版本？为了回答这个问题，我们需要回顾事务隔离的概念。事务指定隔离级别，该级别定义一个事务必须与其他事务进行的资源或数据修改相隔离的程度，此程度与事务生成的锁定直接相关，因此可以在事务中指定级别，它可以确定一个正在运行的事务对其他正在运行的事务的影响。

这是一个非常有趣且冗长的主题，尽管我们不会在此博客中介绍太多细节。我们建议使用PostgreSQL和MySQL官方文档以进一步阅读该主题。

那么，在处理死锁时为什么要进入上述主题？因为sql命令将自动获取锁以确保MVCC行为，并且获取的锁类型取决于定义的事务隔离。

有几种类型的锁（关于PostgreSQL和MySQL的另一个漫长而有趣的主题），但是，关于锁的重要一点是锁之间如何相互作用（最准确地说，是如何发生冲突）。这是为什么？因为两个事务不能同时在同一对象上持有冲突模式的锁。一个非次要的细节，一旦获得，通常会一直持有锁直到交易结束。

这是PostgreSQL示例，说明锁定类型如何相互冲突：

对于MySQL：

那么，当我有两个正在运行的事务要同时对同一个对象持有冲突锁时会发生什么？其中一个将获得锁，而另一个将必须等待。

因此，现在我们可以真正了解僵局期间发生的情况。

那么什么是死锁？您可以想象，数据库死锁有几个定义，但是我喜欢以下内容以简化操作。

数据库死锁是一种情况，其中两个或多个事务正在等待彼此放弃锁。

因此，例如，以下情况将导致我们陷入死锁：

在此，应用程序A锁定表1第1行以进行更新。

同时，应用程序B锁定表2第2行。

现在，应用程序A需要在表2行2上获得锁，以继续执行并完成事务，但是由于它由应用程序B持有，因此它无法获得该锁。应用程序A需要等待应用程序B释放它。 。

但是应用程序B需要获得对表1第1行的锁定，以便继续执行并完成事务，但是由于它由应用程序A持有，因此它无法获得该锁定。

因此，这里我们处于死锁。应用程序A在等待应用程序B拥有的资源以完成操作，而应用程序B在等待应用程序A拥有的资源。那么，如何继续？数据库引擎将检测死锁并杀死其中一个事务，解除对另一个事务的阻塞，并在被杀死的事务上引发死锁错误。

让我们检查一些PostgreSQL和MySQL死锁示例：

PostgreSQL

假设我们有一个测试数据库，其中包含来自世界各国的信息。

world=# SELECT code,region,population FROM country WHERE code IN ('NLD','AUS');
code |          region           | population
------+---------------------------+------------
NLD  | Western Europe            |   15864000
AUS  | Australia and New Zealand |   18886000
(2 rows)

我们有两个会话要更改数据库。

第一个会话将修改NLD代码的区域字段，以及AUS代码的填充字段。

第二个会话将修改AUS代码的区域字段和NLD代码的填充字段。

表格数据：

code: NLD
region: Western Europe
population: 15864000

code: AUS
region: Australia and New Zealand
population: 18886000

会话1：

world=# BEGIN;
BEGIN
world=# UPDATE country SET region='Europe' WHERE code='NLD';
UPDATE 1

会话2：

world=# BEGIN;
BEGIN
world=# UPDATE country SET region='Oceania' WHERE code='AUS';
UPDATE 1
world=# UPDATE country SET population=15864001 WHERE code='NLD';

会话2将挂起，等待会话1完成。

会话1：

world=# UPDATE country SET population=18886001 WHERE code='AUS';
 
ERROR:  deadlock detected
DETAIL:  Process 1181 waits for ShareLock on transaction 579; blocked by process 1148.
Process 1148 waits for ShareLock on transaction 578; blocked by process 1181.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,15) in relation "country"

在这里，我们陷入死锁。系统检测到死锁并终止了会话1。

会话2：

world=# BEGIN;
BEGIN
world=# UPDATE country SET region='Oceania' WHERE code='AUS';
UPDATE 1
world=# UPDATE country SET population=15864001 WHERE code='NLD';
UPDATE 1

并且我们可以检查第二个会话在检测到死锁并且会话1被杀死之后（因此，锁已释放）正确完成。

要获取更多详细信息，我们可以在PostgreSQL服务器中查看日志：

2018-05-16 12:56:38.520 -03 [1181] ERROR:  deadlock detected
2018-05-16 12:56:38.520 -03 [1181] DETAIL:  Process 1181 waits for ShareLock on transaction 579; blocked by process 1148.
       Process 1148 waits for ShareLock on transaction 578; blocked by process 1181.
       Process 1181: UPDATE country SET population=18886001 WHERE code='AUS';
       Process 1148: UPDATE country SET population=15864001 WHERE code='NLD';
2018-05-16 12:56:38.520 -03 [1181] HINT:  See server log for query details.
2018-05-16 12:56:38.520 -03 [1181] CONTEXT:  while updating tuple (0,15) in relation "country"
2018-05-16 12:56:38.520 -03 [1181] STATEMENT:  UPDATE country SET population=18886001 WHERE code='AUS';
2018-05-16 12:59:50.568 -03 [1181] ERROR:  current transaction is aborted, commands ignored until end of transaction block

在这里，我们将能够看到在死锁中检测到的实际命令。

MySQL

为了模拟MySQL中的死锁，我们可以执行以下操作。

与PostgreSQL一样，假设我们有一个测试数据库，其中包含有关演员和电影的信息。

mysql> SELECT first_name,last_name FROM actor WHERE actor_id IN (1,7);
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| PENELOPE   | GUINESS   |
| GRACE      | MOSTEL    |
+------------+-----------+
2 rows in set (0.00 sec)

我们有两个要更改数据库的过程。

第一个会话将为actor_id 1修改字段first_name，为actor_id 7修改字段last_name。

第二个会话将为actor_id 7修改字段first_name，为actor_id 1修改字段last_name。

表格数据：

actor_id: 1
first_name: PENELOPE
last_name: GUINESS

actor_id: 7
first_name: GRACE
last_name: MOSTEL

会话1：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE actor SET first_name='GUINESS' WHERE actor_id='1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

会话2：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE actor SET first_name='MOSTEL' WHERE actor_id='7';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE actor SET last_name='PENELOPE' WHERE actor_id='1';

会话2将挂起，等待会话1完成。

会话1：

mysql> UPDATE actor SET last_name='GRACE' WHERE actor_id='7';
 
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

在这里，我们陷入死锁。系统检测到死锁并终止了会话1。

会话2：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE actor SET first_name='MOSTEL' WHERE actor_id='7';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE actor SET last_name='PENELOPE' WHERE actor_id='1';
Query OK, 1 row affected (8.52 sec)
Rows matched: 1  Changed: 1  Warnings: 0

正如我们在PostgreSQL中看到的那样，我们在错误中可以看到，两个进程之间存在死锁。

有关更多详细信息，我们可以使用命令SHOW ENGINE INNODB STATUS \ G：

mysql> SHOW ENGINE INNODB STATUS\G
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-05-16 18:55:46 0x7f4c34128700
*** (1) TRANSACTION:
TRANSACTION 1456, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 54, OS thread handle 139965388506880, query id 15876 localhost root updating
UPDATE actor SET last_name='PENELOPE' WHERE actor_id='1'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 1456 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0001; asc   ;;
1: len 6; hex 0000000005af; asc       ;;
2: len 7; hex 2d000001690110; asc -   i  ;;
3: len 7; hex 4755494e455353; asc GUINESS;;
4: len 7; hex 4755494e455353; asc GUINESS;;
5: len 4; hex 5afca8b3; asc Z   ;;
 
*** (2) TRANSACTION:
TRANSACTION 1455, ACTIVE 47 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 53, OS thread handle 139965267871488, query id 16013 localhost root updating
UPDATE actor SET last_name='GRACE' WHERE actor_id='7'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 23 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 1455 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0001; asc   ;;
1: len 6; hex 0000000005af; asc       ;;
2: len 7; hex 2d000001690110; asc -   i  ;;
3: len 7; hex 4755494e455353; asc GUINESS;;
4: len 7; hex 4755494e455353; asc GUINESS;;
5: len 4; hex 5afca8b3; asc Z   ;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 1455 lock_mode X locks rec but not gap waiting
Record lock, heap no 202 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0007; asc   ;;
1: len 6; hex 0000000005b0; asc       ;;
2: len 7; hex 2e0000016a0110; asc .   j  ;;
3: len 6; hex 4d4f5354454c; asc MOSTEL;;
4: len 6; hex 4d4f5354454c; asc MOSTEL;;
5: len 4; hex 5afca8c1; asc Z   ;;
 
*** WE ROLL BACK TRANSACTION (2)

在标题“最新检测到的死锁”下，我们可以看到死锁的详细信息。

要在mysql错误日志中查看死锁的详细信息，我们必须在数据库中启用选项innodb_print_all_deadlocks。

mysql> set global innodb_print_all_deadlocks=1;
Query OK, 0 rows affected (0.00 sec)

MySQL日志错误：

2018-05-17T18:36:58.341835Z 12 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2018-05-17T18:36:58.341869Z 12 [Note] InnoDB:
*** (1) TRANSACTION:
 
TRANSACTION 1812, ACTIVE 42 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140515492943616, query id 8467 localhost root updating
UPDATE actor SET last_name='PENELOPE' WHERE actor_id='1'
2018-05-17T18:36:58.341945Z 12 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 
RECORD LOCKS space id 23 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 1812 lock_mode X locks rec but not gap waiting
Record lock, heap no 204 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0001; asc   ;;
1: len 6; hex 000000000713; asc       ;;
2: len 7; hex 330000016b0110; asc 3   k  ;;
3: len 7; hex 4755494e455353; asc GUINESS;;
4: len 7; hex 4755494e455353; asc GUINESS;;
5: len 4; hex 5afdcb89; asc Z   ;;
 
2018-05-17T18:36:58.342347Z 12 [Note] InnoDB: *** (2) TRANSACTION:
 
TRANSACTION 1811, ACTIVE 65 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 12, OS thread handle 140515492677376, query id 9075 localhost root updating
UPDATE actor SET last_name='GRACE' WHERE actor_id='7'
2018-05-17T18:36:58.342409Z 12 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
 
RECORD LOCKS space id 23 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 1811 lock_mode X locks rec but not gap
Record lock, heap no 204 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0001; asc   ;;
1: len 6; hex 000000000713; asc       ;;
2: len 7; hex 330000016b0110; asc 3   k  ;;
3: len 7; hex 4755494e455353; asc GUINESS;;
4: len 7; hex 4755494e455353; asc GUINESS;;
5: len 4; hex 5afdcb89; asc Z   ;;
 
2018-05-17T18:36:58.342793Z 12 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 
RECORD LOCKS space id 23 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 1811 lock_mode X locks rec but not gap waiting
Record lock, heap no 205 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0007; asc   ;;
1: len 6; hex 000000000714; asc       ;;
2: len 7; hex 340000016c0110; asc 4   l  ;;
3: len 6; hex 4d4f5354454c; asc MOSTEL;;
4: len 6; hex 4d4f5354454c; asc MOSTEL;;
5: len 4; hex 5afdcba0; asc Z   ;;
 
2018-05-17T18:36:58.343105Z 12 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

考虑到上面我们了解的死锁发生原因，您可以看到在数据库方面没有很多事情可以避免死锁。无论如何，作为DBA，我们有责任实际捕获它们，对其进行分析并向开发人员提供反馈。

实际上，这些错误是每个应用程序所特有的，因此您需要一个一个地检查它们，并且没有指南告诉您如何解决此问题。记住这一点，您可以找一些东西。

搜索长时间运行的事务。由于锁通常保持到事务结束，因此事务越长，对资源的锁定就越长。如果可能，请尝试将长期运行的事务拆分为较小/较快的事务。

有时实际上不可能拆分事务，因此工作应集中于每次尝试以一致的顺序执行那些操作，以便事务形成定义明确的队列，并且不会死锁。

您还可以建议的一种解决方法是，将重试逻辑添加到应用程序中（当然，首先尝试解决根本问题），这样，如果发生死锁，应用程序将再次运行相同的命令。

检查使用的隔离级别，有时您可以通过更改它们来尝试。查找诸如SELECT FOR UPDATE和SELECT FOR SHARE之类的命令，因为它们会生成显式锁，并评估是否确实需要它们，或者您可以使用较旧的数据快照。如果无法删除这些命令，您可以尝试的一件事是使用较低的隔离级别，例如READ COMMITTED。

当然，请始终将精选的索引添加到表中。然后，您的查询需要扫描更少的索引记录，并因此设置更少的锁。

在较高的级别上，作为DBA，您可以采取一些预防措施来最大程度地减少锁定。为了命名一个示例（在本例中是PostgreSQL），您可以避免在添加列的同一命令中添加默认值。更改表将获得非常积极的锁定，并为其设置默认值实际上将更新具有空值的现有行，从而使该操作花费了很长时间。因此，如果将此操作拆分为几个命令，添加列，添加默认值，更新空值，则可以最大程度地减少锁定影响。

当然，DBA会从实践中获得很多类似的技巧（同时创建索引，在添加pk之前分别创建pk索引，依此类推），但是重要的是学习和理解这种“思维方式”。 ”，并且始终将我们正在执行的操作的锁定影响最小化。