死锁分析

定位锁表情况

查看锁表情况

mysql> show status like 'Table%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Table_locks_immediate      | 135237 |
| Table_locks_waited         | 0      |
| Table_open_cache_hits      | 0      |
| Table_open_cache_misses    | 0      |
| Table_open_cache_overflows | 0      |
+----------------------------+--------+

5 rows in set

Table_locks_immediate 指的是能够立即获得表级锁的次数
Table_locks_waited 指的是不能立即获取表级锁而需要等待的次数，如果数量大，说明锁等待多，有锁争用情况

查看正在被锁定的的表

mysql> show OPEN TABLES where In_use > 0;

如果查看到锁争用情况严重，可以再查看当前执行的 SQL：

mysql> show processlist\G

也可以用 mysqladmin

mysqladmin -uroot -p -P 3306 processlist

mysqladmin 命令有一个 debug 参数，可以分析当前 MySQL 服务的状态信息，同时也可以用来帮助定位当前锁的详细情况，这里通过该命令分析一下当前 MySQL 服务的详细状态，执行 mysqladmin 命令如下：

mysqladmin -uroot -p -S /var/run/mysql.sock debug

debug 会将状态信息生成到 mysql 的错误文件，一般锁的信息都会保存在最后几行，这在操作系统层 error log 最后几行：

...
Thread database.table_name Locked/Waiting Lock_type
2 hdpic.t_wiki_zutu Waiting - write Highest priority write lock
123890 hdpic.t_wiki_zutu_category Locked - read Low priority read lock
123890 hdpic.t_wiki_zutu_photo Locked - read Low priority read lock
123890 hdpic.t_wiki_zutu Locked - read Low priority read lock
124906 hdpic.t_wiki_zutu Waiting - read Low priority read lock

从上述信息可以看出，123890 持有的读锁阻塞了 2 进程的写入和 124906 的读操作，如果现状不可接受，不能继续等待，将 123890 杀掉，释放资源即可：

mysql> kill 123890;

使用系统表进行锁查询

使用系统表进行锁查询：

mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
...
| INNODB_TEMP_TABLE_INFO                |
| INNODB_TRX                            |
| INNODB_VIRTUAL                        |
...
+---------------------------------------+
79 rows in set (0.00 sec)

mysql> show tables;
+------------------------------------------------------+
| Tables_in_performance_schema                         |
...
| cond_instances                                       |
| data_lock_waits                                      |
| data_locks                                           |
| error_log                                            |
...
116 rows in set (0.00 sec)

注意：

innodb_locks 表在 8.0.13 版本中由 performance_schema.data_locks 表所代替
innodb_lock_waits 表则由 performance_schema.data_lock_waits 表代替。

通过这三张表可以更新监控当前事物并且分析存在的锁问题）

innodb_trx（打印 innodb 内核中的当前活跃（ACTIVE）事务）
data_locks（打印当前状态产生的 innodb 锁，仅在有锁等待时打印）
data_lock_waits（打印当前状态产生的 innodb 锁等待，仅在有锁等待时打印）

innodb_trx 表结构说明：

字段	说明
`trx_id`	innodb 存储引擎内部唯一的事务 ID
`trx_state`	当前事物状态（running 和 lock wait 两种状态）
`trx_started`	事务的开始时间
`trx_requested_lock_id`	等待事务的锁 ID，如 `trx_state` 的状态为 `Lock wait`，那么该值带表当前事务等待之前事务占用资源的 ID 若 `trx_state` 不是 `Lock wait` 则该值为 NULL
`trx_wait_started`	事务等待的开始时间
`trx_weight`	事务的权重，在 innodb 存储引擎中，当发生死锁需要回滚的时，innodb 存储引擎会选择该值最小的进行回滚
`trx_mysql_thread_id`	mysql 中的线程 id, 即 `show processlist` 显示的结果
`trx_query`	事务运行的 SQL 语句

data_locks 表结构说明：

字段	说明
`engine`
`engine_lock_id`	锁的 ID
`engine_transaction_id`	事务的 ID
`lock_type`	锁的类型，表锁还是行锁（RECORD）
`lock_mode`	锁的模式（S 锁与 X 锁两种模式）
`lock_data`	事务锁定记录主键值，若是表锁则该值为 NULL
`lock_status`

data_lock_waits 表结构说明：

字段	说明
`ENGINE`
`REQUESTING_ENGINE_TRANSACTION_ID`	申请锁资源的事务 ID
`REQUESTING_ENGINE_LOCK_ID`	申请的锁的 ID
`BLOCKING_ENGINE_TRANSACTION_ID`	阻塞其他事务的事务 ID
`BLOCKING_ENGINE_LOCK_ID`	阻塞其他锁的锁 ID

查看当前的事务

SELECT * FROM information_schema.innodb_trx\G

查看当前锁定的事务

SELECT * FROM performance_schema.data_locks\G

查看当前等锁的事务

SELECT * FROM performance_schema.data_lock_waits\G

联表查询

select r.trx_isolation_level, r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_trx_thread,
r.trx_state waiting_trx_state,lr.lock_mode waiting_trx_lock_mode,lr.lock_type waiting_trx_lock_type,
lr.lock_table waiting_trx_lock_table,lr.lock_index waiting_trx_lock_index,r.trx_query waiting_trx_query,
b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_trx_thread,b.trx_state blocking_trx_state,
lb.lock_mode blocking_trx_lock_mode,lb.lock_type blocking_trx_lock_type,lb.lock_table blocking_trx_lock_table,
lb.lock_index blocking_trx_lock_index,b.trx_query blocking_query
from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id=w.blocking_trx_id
inner join information_schema.innodb_trx r on r.trx_id=w.requesting_trx_id
inner join information_schema.innodb_locks lb on lb.lock_trx_id=w.blocking_trx_id
inner join information_schema.innodb_locks lr on lr.lock_trx_id=w.requesting_trx_id \G