MySQL 锁表问题的排查和处理

我们可以用下面三张 INFORMATION_SCHEMA 库中的表来查原因:

  • innodb_trx:当前运行的所有事务
  • innodb_locks:当前出现的锁
  • innodb_lock_waits:锁等待的对应关系

排查锁表

锁表查看命令

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

组合查询:

SELECT r.trx_id waiting_trx_id,   r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query,   b.trx_id blocking_trx_id,   b.trx_mysql_thread_id blocking_thread,   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;

如下图所示:

mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 1081258251
                 trx_state: ROLLING BACK
               trx_started: 2021-05-19 09:31:10
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2311922
       trx_mysql_thread_id: 83833568
                 trx_query: update XXXX set status=0 where id=0
       trx_operation_state: rollback of SQL statement
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 644407
     trx_lock_memory_bytes: 58073296
           trx_rows_locked: 5021486
         trx_rows_modified: 1667515
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

系统表的表结构

innodb_locks

desc information_schema.innodb_locks;

Field Type Null Key Default Extra
lock_id varchar(81) NO 锁 ID
lock_trx_id varchar(18) NO 拥有锁的事务 ID
lock_mode varchar(32) NO 锁模式
lock_type varchar(32) NO 锁类型
lock_table varchar(1024) NO 被锁的表
lock_index varchar(1024) YES 被锁的索引
lock_space bigint(21) unsigned YES 被锁的表空间号
lock_page bigint(21) unsigned YES 被锁的页号
lock_rec bigint(21) unsigned YES 被锁的记录号
lock_data varchar(8192) YES 被锁的数据

innodb_lock_waits

desc information_schema.innodb_lock_waits;

Field Type Null Key Default Remark
requesting_trx_id varchar(18) NO 请求锁的事务 ID
requested_lock_id varchar(81) NO 请求锁的锁 ID
blocking_trx_id varchar(18) NO 当前拥有锁的事务 ID
blocking_lock_id varchar(81) NO 当前拥有锁的锁 ID

innodb_trx

desc information_schema.innodb_trx;

Field Type Null Key Default Extra Remark
trx_id varchar(18) NO 事务 ID
trx_state varchar(13) NO 事务状态
trx_started datetime NO 0000-00-00 00:00:00 事务开始时间
trx_requested_lock_id varchar(81) YES innodb_locks.lock_id
trx_wait_started datetime YES 事务开始等待的时间
trx_weight bigint(21) unsigned NO 0 #
trx_mysql_thread_id bigint(21) unsigned NO 0 事务线程 ID
trx_query varchar(1024) YES 具体 SQL 语句
trx_operation_state varchar(64) YES 事务当前操作状态
trx_tables_in_use bigint(21) unsigned NO 0 事务中有多少个表被使用
trx_tables_locked bigint(21) unsigned NO 0 事务拥有多少个锁
trx_lock_structs bigint(21) unsigned NO 0 #
trx_lock_memory_bytes bigint(21) unsigned NO 0 事务锁住的内存大小(B)
trx_rows_locked bigint(21) unsigned NO 0 事务锁住的行数
trx_rows_modified bigint(21) unsigned NO 0 事务更改的行数
trx_concurrency_tickets bigint(21) unsigned NO 0 事务并发票数
trx_isolation_level varchar(16) NO 事务隔离级别
trx_unique_checks int(1) NO 0 是否唯一性检查
trx_foreign_key_checks int(1) NO 0 是否外键检查
trx_last_foreign_key_error varchar(256) YES 最后的外键错误
trx_adaptive_hash_latched int(1) NO 0 #
trx_adaptive_hash_timeout bigint(21) unsigned NO 0 #