死锁分析-（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数据库会自动回滚其中一个事务。
