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 | # |
相关文章