检查死锁【工具】
检查死锁【information_schema】
应对死锁的两种策略：
- 1. innodb_deadlock_detect【开启死锁检查】
- 2. innodb_lock_wait_timeout【到时间就释放】
查询阻塞

检查死锁【工具】

show engine innodb status

只能查看最近一次死锁信息

pt-deadlock-logger：死锁记录到表中

设置innodb_print_all_deadlocks【记录死锁到日志中】

查看：

检查死锁【information_schema】

检查阻塞死锁 - 图7
mysql死锁检测算法MySQL 8 死锁检测脚本最近一直忙于睡觉的博客-CSDN博客

SELECT a.*,c.trx_state as block_trx_state,c.trx_started as block_trx_started,c.trx_query as block_trx_query,c.trx_mysql_thread_id as block_trx_mysql_thread_id,d.thread_id as block_thread_id,d.PROCESSLIST_USER as block_user,d.PROCESSLIST_HOST as block_host from (SELECT b.trx_id as req_trx_id,b.trx_state as req_trx_state,b.trx_started as req_trx_started,b.trx_query as req_trx_query,b.trx_mysql_thread_id as req_trx_mysql_thread_id,c.thread_id as req_thread_id,c.PROCESSLIST_USER as req_user,c.PROCESSLIST_HOST as req_host from performance_schema.data_lock_waits a INNER JOIN information_schema.INNODB_TRX b on a.REQUESTING_ENGINE_TRANSACTION_ID=b.trx_id and a.REQUESTING_ENGINE_LOCK_ID = b.trx_requested_lock_id INNER JOIN performance_schema.threads c on a.REQUESTING_THREAD_ID = c.THREAD_ID ) a INNER JOIN performance_schema.data_lock_waits b on a.req_trx_id = b.REQUESTING_ENGINE_TRANSACTION_ID and a.req_thread_id = b.REQUESTING_THREAD_ID INNER JOIN information_schema.INNODB_TRX c on b.BLOCKING_ENGINE_TRANSACTION_ID = c.TRX_ID INNER JOIN performance_schema.threads d on b.BLOCKING_THREAD_ID = d.THREAD_ID

information_schema.INNODB_TRX：当前运行的所有事务

检查阻塞死锁 - 图8

performance_schema.data_lock_waits：锁等待的对应关系
performance_schema.data_locks：当前出现的锁
- MySQL8.0删除了information_schema.innodb_locks，添加了performance_schema.data_locks

检查阻塞死锁 - 图9

应对死锁的两种策略：

1. innodb_deadlock_detect【开启死锁检查】

默认on
新来线程判断是否会死锁，浪费时间。
如果他要加锁访问的行上有锁，他才要检测。

2. innodb_lock_wait_timeout【到时间就释放】

死锁等待时间。默认50s
锁超时事务不会自动回滚

查询阻塞

show engine innodb status查看锁
输出锁信息配置参数：innodb_status_output_locks

show engine innodb status
lock mode x是next key lock
not gap：是记录锁
gap before：是间隙锁
insert intention：是插入意向锁（不是意向锁表锁）