问题：Lock wait timeout exceeded; try restarting transaction
MySQL版本：5.6.44
image-20211102115143947.png
意思是：InnoDB在锁等待超时过期时报告此错误。等待时间过长的语句被回滚（而不是整个事务）。如果SQL语句需要等待其他事务完成的时间更长，则可以增加 innodb_lock_wait_timeout 配置选项的值；如果太多长时间运行的事务导致锁定问题并降低繁忙系统上的并发性，则可以减少该选项的值。
分析
锁等待超时，可能是出现了死锁，也可能有事务长时间未提交
解决办法
- 如果是死锁，MySQL提供了超时等待机制，超时获取不到资源即
rollback
四张重要的表
库：information_schema
表：
-
innodb_trx：当前运行的所有事务
-
innodb_locks：当前出现的锁
-
innodb_lock_waits：锁等待对应的关系
-
processlist：当前进程信息
查看各表信息
desc information_schema.innodb_trx
desc information_schema.innodb_locks
desc information_schema.innodb_lock_waits
desc information_schema.processlist
innodb_trx 表
| 字段 | 释义 |
|---|---|
| trx_id | 事务ID |
| trx_state | 事务状态 |
| trx_started | 事务开始时间 |
| trx_requested_lock_id | innodb_locks.lock_id |
| trx_wait_started | 事务开始等待的时间 |
| trx_weight | |
| trx_mysql_thread_id | 事务线程ID |
| trx_query | 具体SQL语句 |
| trx_operation_state | 事务当前操作状态 |
| trx_tables_in_use | 事务中有多少个表被使用 |
| trx_tables_locked | 事务拥有多少个锁 |
| trx_lock_structs | |
| trx_lock_memory_bytes | 事务锁住的内存大小（B） |
| trx_rows_locked | 事务锁住的行数 |
| trx_rows_modified | 事务更改的行数 |
| trx_concurrency_tickets | 事务并发票数 |
| trx_isolation_level | 事务隔离级别 |
| trx_unique_checks | 是否唯一性检查 |
| trx_foreign_key_checks | 是否外键检查 |
| trx_last_foreign_key_error | 最后的外键错误 |
| trx_adaptive_hash_latched | |
| trx_adaptive_hash_timeout | |
| trx_is_read_only | 事务是否只读 |
| trx_autocommit_non_locking |
innodb_locks 表
| 字段 | 释义 |
|---|---|
| lock_id | 锁ID |
| lock_trx_id | 拥有锁的事务ID |
| lock_mode | 锁模式 |
| lock_type | 锁类型 |
| lock_table | 被锁的表 |
| lock_index | 被锁的索引 |
| lock_space | 被锁的表空间号 |
| lock_page | 被锁的页号 |
| lock_rec | 被锁的记录号 |
| lock_data | 被锁的数据 |
innodb_lock_waits 表
| 字段 | 释义 |
|---|---|
| requesting_trx_id | 请求锁的事务ID（等待锁的ID） |
| requested_lock_id | 请求锁的锁ID |
| blocking_trx_id | 当前拥有锁的事务ID |
| blocking_lock_id | 当前拥有锁的锁ID |
processlist 表
| 字段 | 释义 |
|---|---|
| Id | 线程ID |
| User | 用户 |
| Host | 主机 |
| db | 数据库 |
| Command | 命令 |
| Time | 时间 |
| State | 状态 |
| Info | 信息 |
排查思路
模拟出现死锁
准备一张只有主键的表：t_test (id)
Navicat 新建查询1
begin
update t_test set id = 30 where id = 3;
-- 执行完 update 后，暂时不 commit
commit;
Navicat 新建查询2
begin
update t_test set id = 40 where id = 3;
-- 执行完 update 后，暂时不 commit
commit;
检查是否锁表
show open tables where in_use > 0;
查询当前正在执行的事务
select * from information_schema.innodb_trx
当前正在执行的事务.png
可以看到，此时的事务2，出现了 锁等待
查询当前出现的锁
select * from information_schema.innodb_locks
当前出现的锁.png
查询锁等待对应的关系
锁等待对应的关系.png
查询等待锁的事务所执行的SQL
SELECT
a.trx_id '事务 id',
a.trx_mysql_thread_id '事务线程 id',
a.trx_query '事务 SQL'
FROM
information_schema.innodb_locks b,
information_schema.innodb_trx a
WHERE
b.lock_trx_id = a.trx_id;
查找当前出现锁的事务SQL.png
可以看到，对应的 SQL 就是事务2的
最后，事务2 等待锁超时报错：Lock wait timeout exceeded; try restarting transaction;
事务二超过等待时长报错.png
时间大概50s， 锁等待超时时间配置
show variables like 'innodb_lock_wait_timeout';
通过事务线程ID查找进程信息
select * from information_schema.processlist where id = 388654
通过事务线程ID查找进程列表.png
通过其中的 HOST 字段信息就可以定位到是哪个程序的事务。结合事务的 SQL 语句可以定位到业务代码。
win10 查看端口信息
netstat -ano | findstr 59501
网友评论