mysql死锁排查方案
张新光 2022-10-13 17:11:49 94次浏览 0次留言
information_schema库下相关事务表和锁相关信息表介绍
innodb_trx
存储了当前正在执行的事务信息
trx_id:事务ID。
trx_state:事务状态,有以下几种状态:RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started:事务开始时间。
trx_requested_lock_id:事务当前正在等待锁的标识,可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
trx_wait_started:事务开始等待的时间。
trx_mysql_thread_id:事务线程 ID,可以和 PROCESSLIST 表 JOIN。
trx_tables_locked:表示该事务目前加了多少个表级锁。
trx_lock_structs:表示该事务生成了多少个内存中的锁结构。
trx_lock_memory_bytes:事务锁住的内存大小,单位为 BYTES。
trx_rows_locked:表示该事务目前加了多少个行级锁。
innodb_lock_waits
表明每个阻塞的事务是因为获取不到哪个事务持有的锁而被阻塞
requesting_trx_id:–获取不到锁而被阻塞的事务id(等待方)
requested_lock_id:-- 请求锁ID ,事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。
blocking_trx_id: --获取到别的事务需要的锁而阻塞其事务的事务id(当前持有方,待释放)
blocking_lock_id: --这一事务的锁的 ID,该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。
processlist
id:标识ID。这与在SHOW PROCESSLIST语句的Id列、Performance Schema threads表的PROCESSLIST_ID列中显示的值类型相同,并由CONNECTION_ID()函数返回
user:发出该语句的mysql用户。
host:发出该语句的客户机的主机名(系统用户除外,没有主机)。
db:默认数据库。
command:线程正在执行的命令的类型。
time:线程处于当前状态的时间(以秒为单位)。
state:指示线程正在执行的操作、事件或状态。
info:线程正在执行的语句,如果没有执行任何语句,则为NULL。
如何借助这几张表来定位到有行锁等待
查看当前有无锁等待
show status like 'innodb_row_lock%';
查看哪个事务在等待(被阻塞了)
select * from information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT'
trx_state : 表示该事务处于锁等待状态。
trx_query : 当前被阻塞的操作是
trx_id: 事务ID是1385249
trx_mysql_thread_id :线程ID是 75
查询该事务被哪个事务给阻塞了
select * from information_schema.innodb_lock_waits where requesting_trx_id='1385395';
requesting_trx_id: 被阻塞的trx_id是1385395
blocking_trx_id: 阻塞该事务的事务id是1385394
根据trx_id,从innodb_trx表可查询到trx_mysql_thread_id线程id
select * from information_schema.innodb_trx where trx_id='1385444';
trx_mysql_thread_id :线程id是75
根据线程id,查询线程信息
SELECT * FROM performance_schema.threads WHERE processlist_id=75
线程信息有导致锁表的SQL信息。
可以直接查看锁列表
select * from information_schema.innodb_lock_waits
然后根据信息直接查询导致锁的源sql,拿到导致锁的事务ID,字段是requesting_trx_id
然后根据事务ID,查询事务信息拿到线程ID,字段是trx_mysql_thread_id
select * from information_schema.innodb_trx where trx_id='1385534';
最后查询线程信息获取锁表的SQL语句
SELECT * FROM performance_schema.threads WHERE processlist_id=75