死锁分析-（DML+DDL触发Server层死锁）

参考文档：

MySQL Server层的锁信息比innodb存储引擎层略复杂，而且Server层的死锁信息不做记录。

提示：show engine innodb status; 只能查看Innodb存储引擎层的死锁信息

内核月报（MDL加锁源码分析）：

http://mysql.taobao.org/monthly/2018/02/01/

object上已持有锁和请求锁的兼容性矩阵如下。

Request   |  Granted requests for lock                  |
 type     | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
----------+---------------------------------------------+
S         | +   +   +   +    +    +   +    +    +    -  |
SH        | +   +   +   +    +    +   +    +    +    -  |
SR        | +   +   +   +    +    +   +    +    -    -  |
SW        | +   +   +   +    +    +   -    -    -    -  |
SWLP      | +   +   +   +    +    +   -    -    -    -  |
SU        | +   +   +   +    +    -   +    -    -    -  |
SRO       | +   +   +   -    -    +   +    +    -    -  |
SNW       | +   +   +   -    -    -   +    -    -    -  |
SNRW      | +   +   -   -    -    -   -    -    -    -  |
X         | -   -   -   -    -    -   -    -    -    -  |

环境说明：

为了更好的说明整个过程，数据库在启动时需要开启 performance_schema 参数，开启 MDL 锁的相关监控数据。

#确认 performance_schema 参数已经打开。
mysql> show global variables like 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
1 row in set (0.00 sec)
 
#开启 MDL 锁的相关监控数据。
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
 
 
#创建测试数据
mysql> create table t_lock (id int primary key auto_increment,
-> name varchar(20));
Query OK, 0 rows affected (0.01 sec)
 
 
mysql> insert into t_lock(id,name) values(1,'张三'),(2,'李四'),(3,'王五'),(4,'赵六');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

复现死锁过程(提前开启3个mysql会话)：

session1（只进行查询），session3（查询持有的MDL）:

#session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_lock;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 赵六 |
+----+--------+
4 rows in set (0.00 sec)
 
#session3 OWNER_THREAD_ID 46 这条记录忽略掉，是session3持有的MDL锁
OWNER_THREAD_ID = 45 这条记录是session1
LOCK_TYPE = SHARED_READ 持有锁类型
LOCK_STATUS = GRANTED 锁状态已经是待有状态
 
mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+
| TABLE | ceshi | t_lock | 139765484063408 | SHARED_READ | TRANSACTION | GRANTED | | 45 | 30 |
| TABLE | performance_schema | metadata_locks | 52674736 | SHARED_READ | TRANSACTION | GRANTED | | 46 | 3 |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+
2 rows in set (0.00 sec)

session2（进行DDL操作），session3（查询持有的MDL）:

#session3 OWNER_THREAD_ID 46 这条记录忽略掉，是session3持有的MDL锁

OWNER_THREAD_ID = 45 这条记录是session1的，暂时忽略掉。

从结果看，session2执行的DDL命令，

获取了 GLOBAL 的INTENTION_EXCLUSIVE （全局意向排它锁）

获取了 schema 为 ceshi 的INTENTION_EXCLUSIVE （ceshi 库的意向排它锁）

获取了 ceshi.t_lock 对象的SHARED_UPGRADABLE （ceshi.t_lock 的升级锁）

等待获取 ceshi.t_locK 对象的EXCLUSIVE （等待获取ceshi.t_lock排它锁，LOCK_STATUS 状态是PENDING）

#session2
mysql> alter table t_lock add age int;
...命令会卡住，在等待
 
 
#session3
mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| TABLE | ceshi | t_lock | 139765484063408 | SHARED_READ | TRANSACTION | GRANTED | | 45 | 30 |
| GLOBAL | NULL | NULL | 139766032053392 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | | 47 | 21 |
| SCHEMA | ceshi | NULL | 139766031949536 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | | 47 | 21 |
| TABLE | ceshi | t_lock | 139766032048752 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | | 47 | 21 |
| TABLE | ceshi | t_lock | 139766031960848 | EXCLUSIVE | TRANSACTION | PENDING | | 47 | 21 |
| TABLE | performance_schema | metadata_locks | 52674736 | SHARED_READ | TRANSACTION | GRANTED | | 46 | 4 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
6 rows in set (0.00 sec)

在session1 执行一个update命令，复现死锁。

session1 的事务被回滚，

session2 的DDL命令成功执行。

#session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_lock;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 赵六 |
+----+--------+
4 rows in set (0.00 sec)
 
mysql> update t_lock set id=100 where id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

#session2

mysql> alter table t_lock add age int;

Query OK, 0 rows affected (27 min 2.98 sec)

Records: 0 Duplicates: 0 Warnings: 0

死锁分析：

根据之前的锁兼容矩阵图，X锁和任何锁是不兼容的。

session1: 开启事务，并执行查询，持有 ceshi.t_lock 对象的 SHARED_READ 锁，简称SR锁。

session2:执行DDL命令，想要获取 ceshi.t_lock 对象的 EXCLUSIVE 锁，简称X锁。

这个状态时，session2 在等 session1 释放锁。

sessin1: 继续执行 update 命令，会申请 ceshi.t_lock 对象的 SHARED_WRITE 锁，简称SW锁。

这个状态时，session2 在等待获取 ceshi.t_lock 对象的X锁，session1 想要申请SW锁，必须等session2 释放掉锁。

所以 session1 在等 session2 释放锁。

两个会话互相等待，发生死锁，MySQL数据库会自动回滚其中一个事务。