如何排查?
MYSQL出现死锁,首先查询information_schema.innodb_trx表,查看哪些mysql查询线程ID导致的,
SELECT * FROM information_schema.innodb_trx
SELECT * FROM information_schema.innodb_trx 命令是用来查看当前运行的所以事务:
说明:
FORMATION_SCHEMA提供对数据库元数据的访问、关于MySQL服务器的信息,如数据库或表的名称、列的数据类型或访问权限。其中有一个关于InnoDB数据库引擎表的集合,里面有记录数据库事务和锁的相关表,InnoDB INFORMATION_SCHEMA表可以用来监视正在进行的InnoDB活动,在它们变成问题之前检测低效,或者对性能和容量问题进行故障排除。在实际开发和应用中,会碰到和数据库事务相关的问题,比如事务一直未结束,出现行锁,表锁以及死锁等情况,这时我们就需要有一个快速定位问题行之有效的方法,所以我们来系统了解下INFORMATION_SCHEMA和定位事务问题。
记录如下:
INNODB_TRX表提供了关于当前在InnoDB中执行的每个事务(不包括只读事务)的信息,包括事务是否等待锁、事务何时启动以及事务正在执行的SQL语句(如果有的话)。INNODB_TRX表有以下字段:
Field | Comment |
---|---|
TRX_ID | 自增id |
TRX_WEIGHT | 事务权重,反映(但不一定是准确的计数)事务更改的行数和锁定的行数。为了解决死锁,InnoDB选择权重最小的事务作为要回滚的“受害者” |
TRX_STATE | 事务执行状态。允许的值包括运行(RUNNING)、锁等待(LOCK WAIT)、回滚(ROLLING BACK)和提交(COMMITTING)。 |
TRX_STARTED | 事务开始时间 |
TRX_REQUESTED_LOCK_ID | 事务当前等待的锁的ID,如果TRX_STATE为LOCK WAIT;否则无效。要获取关于锁的详细信息,请将此列与INNODB_LOCKS表的LOCK_ID列关联 |
TRX_WAIT_STARTED | 事务开始等待锁的时间,如果TRX_STATE为锁等待(LOCK WAIT);否则无效。 |
TRX_MYSQL_THREAD_ID | MySql事务线程id,要获取关于线程的详细信息,与INFORMATION_SCHEMA PROCESSLIST表的ID列关联 |
TRX_QUERY | 事务正在执行的SQL语句 |
TRX_OPERATION_STATE | 事务当前操作 |
TRX_TABLES_IN_USE | 处理此事务的当前SQL语句使用的InnoDB表的数量 |
TRX_TABLES_LOCKED | 当前SQL语句具有行锁(row locks)的InnoDB表的数量(因为这些是行锁(row locks),而不是表锁(table locks),所以表通常仍然可以由多个事务读写,尽管有些行被锁定了) |
TRX_LOCK_STRUCTS | 事务保留的锁的数量 |
TRX_LOCK_MEMORY_BYTES | 此事务在内存中的锁结构占用的总大小 |
TRX_ROWS_LOCKED | 此事务锁定的近似数目或行。该值可能包括物理上存在但对事务不可见的删除标记行 |
TRX_ROWS_MODIFIED | 此事务中修改和插入的行数量 |
TRX_CONCURRENCY_TICKETS | 指示当前事务在换出之前可以做多少工作的值,由innodb_concurrency_tickets系统变量指定 |
TRX_ISOLATION_LEVEL | 事务隔离级别 |
TRX_UNIQUE_CHECKS | 是否为当前事务打开或关闭唯一性检查 |
TRX_FOREIGN_KEY_CHECKS | 是否为当前事务打开或关闭外键检查 |
TRX_ADAPTIVE_HASH_LATCHED | 自适应哈希索引是否被当前事务锁定 |
TRX_ADAPTIVE_HASH_TIMEOUT | 是否立即放弃自适应哈希索引的搜索锁存器,还是在来自MySQL的调用之间保留它 |
TRX_IS_READ_ONLY | 值为1表示只读事务 |
TRX_AUTOCOMMIT_NON_LOCKING | 值1表示事务是一个SELECT语句,它不使用FOR UPDATE或LOCK IN SHARED MODE子句,并且在执行时启用了autocommit,因此事务将只包含这一条语句。当这个列和TRX_IS_READ_ONLY都为1时,InnoDB优化事务,以减少与更改表数据的事务相关的开销。 |
操作步骤
使用如下语句查看事务,找到状态为RUNNING的记录
SELECT * FROM information_schema.INNODB_TRX;
通过trx_mysql_thread_id: xxx的去查询information_schema.processlist找到执行事务的客户端请求的SQL线程
select * from information_schema.PROCESSLIST WHERE ID in( '219','218');
根据我们拿到的线程id去查,可以获取到具体的执行sql
select * from performance_schema.events_statements_current where THREAD_ID in (select THREAD_ID from performance_schema.threads where PROCESSLIST_ID in( '219','218'))
结果如下:
问题就已经出来了,这两个in字句,导致死锁。
说明:
如果以上根据SQL分析不出来问题,我们需要从我们系统来进行定位,此时需要保存“案发现场”,数据库中处于RUNNING的事务先不要结束掉,然后根据上面定位的进程对应的项目来跟踪线程的执行情况,可以利用jconsole或者jmc来跟踪线程的执行活动,或者用jstack来跟踪。
结束线程
在执行结果中可以看到是否有表锁等待或者死锁,如果有死锁发生,可以通过下面的命令来杀掉当前运行的事务:
KILL thread id;
KILL 后面的数字指的是 trx_mysql_thread_id 值。
KILL '219','218'
线程ID是23464106,通过information_schema.processlist查看对应的记录,可以从中看到连接的IP地址和用户等信息,特别是里面的INFO字段。
最简单的死锁避免方案:
没有其它的办法,只能再次检查代码。
所有事务中出现了问题, 需要return的地方一定需要加上回滚,最后对执行结果的判断时,如果有一个结果未成功就需要回滚。