MySQL死锁/锁表问题排查

在生产运行过程中，如果存在长事物，很容易产生锁表、死锁的情况。本篇记录锁表、死锁排查过程中常用的语句，后续慢慢补充完善。

1、information_schema.innodb_trx

-- 查询当前正在运行的事物
SELECT * FROM information_schema.innodb_trx

事物在执行的过程中，会一直在innodb_trx中，直到事物结束记录才会没掉。最近碰到一个长事物正在执行回滚操作，由于比较记录比较大，执行了很长时间才结束，在这期间，相关的表都被锁住，而且即时使用了KILL ID放松，事物依然存在，似乎只能等待回滚结束。所以生产业务需要避免长事物的执行

2、metadata_locks、processlist、threads、data_locks

-- 查询当前哪些线程持有元数据锁及锁的类型（共享锁、独占锁）
SELECT * FROM performance_schema.metadata_locks;

-- 当前持有的行锁的信息
select * from performance_schema.data_locks
包括
   `LOCK_TYPE`: 锁的类型（如 `RECORD`、`TABLE` 等）。
   `LOCK_MODE`: 锁的模式（如 `S`、`X`、`IS`、`IX`、`GAP`、`REC_NOT_GAP` 等）。
   `LOCK_STATUS`: 锁的状态（如 `GRANTED`、`WAITING`）。
   `LOCK_DATA`: 锁定的数据（如行记录的主键值或范围）。

-- 查询哪个SQL语句持有了锁
SELECT
	ml.OBJECT_TYPE ,
	ml.OBJECT_SCHEMA ,
	ml.OBJECT_NAME ,
	ml.LOCK_TYPE ,
	t.PROCESSLIST_ID,
	pl.USER,
	pl.*
FROM
	performance_schema.metadata_locks ml
JOIN 
  performance_schema.threads t 
    ON
	ml.OWNER_THREAD_ID = t.THREAD_ID
JOIN 
  information_schema.processlist pl 
    ON
	t.PROCESSLIST_ID = pl.ID
WHERE
	ml.OBJECT_SCHEMA = 'your_schema_name'
	AND ml.OBJECT_NAME = 'your_object_name';
        
    

3、死锁排查

show engine innodb status
查看Status字段，搜索LATEST DETECTED DEADLOCK

可以找到最近发生死锁的SQL语句，调整相关的业务逻辑，避免死锁

如果死锁频繁，则可以开启innodb_print_all_deadlocks选项（默认为OFF 关闭），可以将所有死锁信息打印到MySQL错误日志中（如果死锁频繁，可能导致日志快速膨胀）