遇到 MySQL Lock wait timeout exceeded 不要慌

在系统运行过程中可能我们会执行了某条写数据库的 SQL , 等了很久没有返回 , 最终会得到一个 Lock wait timeout exceeded; try restarting transaction 异常，遇到这种状况首先不要惊慌，第一步很重要一定要第一时间甩锅出去，可以说 SQL 写的不合理（前提是不是自己写的），业务设计不合理事务太大了，表设计不合理，索引建立的不合理，今天天气不好，太阳风暴影响了系统硬件等等。第二步开始解决问题。

搞清除 Lock wait timeout exceeded 是怎么产生的

首先确认一点: MySQL 锁超时时间是多久，如果这个值太小了那超时也是可以理解的。MySQL innodb 默认的锁等待超时时间是 50 秒。如果在这个时间下还超时了那就别怀疑了，就是自己的问题。

show global variables like '%innodb_lock_wait_timeout%' ;

事务 1

set autocommit = 0 ;
begin ;
update employee set dept = '技术部' where id < 5;

事务 2

set autocommit = 0 ;
begin ;
update employee set age = 33 where id < 10;

事务 2 在等待 50s 后超时，报错 Lock wait timeout exceeded; try restarting transaction

发生这种情况的根本原因是事务1先执行,给需要更新的数据加了锁, 事务2要更新的数据和事务1要更新的数据有交集,事务1没有提交锁就没有释放,事务2在等待锁的释放。简单来说就是不同的事务对同一条记录的锁存在竞争，最终导致了锁等待超时。

解决问题,但没有完全解决

按照一个正常人的思路分析，既然是等待锁超时了，那就说明肯定还有某个正在执行的 SQL 还没执行完在占用着锁。这时候就会有大聪明可能会说那我们就等它执行完就行了呗，什么都不用做干等就行了。理论上来说这个思路是没问题的，但是用户可不会等，老板更不会等。等不行只能换个思路了，提前把这个还没执行完的 SQL 终止掉是不是就可以了，终止掉后锁就释放了啊。

首先让我们看看是那个大聪明执行了这么久还没有执行完。

show processlist ;

找到这个 Id 然后直接暴力终止:

kill 61 ;

这种方法只能看到很少的信息,我们没法知道更详细的信息。

为了看到更详细的信息可以用:

select * from information_schema.INNODB_TRX;

NNODB_TRX表提供了当前在InnoDB内部执行的所有事务信息，包含事务是否在等待锁，事务何时开始以及事务正在执行的SQL语句(如果有的话，sql语句阻塞就可以显示)。

也可以通过 information_schema.INNODB_LOCK_WAITS (当前出现的锁) , information_schema.INNODB_LOCKS (锁等待的对应关系) 表信息定位问题。

[
  {
    "trx_id": 77859,
    "trx_state": "LOCK WAIT",
    "trx_started": "2023-05-11 00:33:55",
    "trx_requested_lock_id": "1965752586400:227:4:41:1965716235576",
    "trx_wait_started": "2023-05-11 00:33:55",
    "trx_weight": 2,
    "trx_mysql_thread_id": 64,
    "trx_query": "update employee set age = 33 where id < 10",
    "trx_operation_state": "starting index read",
    "trx_tables_in_use": 1,
    "trx_tables_locked": 1,
    "trx_lock_structs": 2,
    "trx_lock_memory_bytes": 1136,
    "trx_rows_locked": 1,
    "trx_rows_modified": 0,
    "trx_concurrency_tickets": 0,
    "trx_isolation_level": "REPEATABLE READ",
    "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,
    "trx_schedule_weight": 1
  },
  {
    "trx_id": 77858,
    "trx_state": "RUNNING",
    "trx_started": "2023-05-11 00:33:53",
    "trx_requested_lock_id": null,
    "trx_wait_started": null,
    "trx_weight": 3,
    "trx_mysql_thread_id": 60,
    "trx_query": null,
    "trx_operation_state": null,
    "trx_tables_in_use": 0,
    "trx_tables_locked": 1,
    "trx_lock_structs": 3,
    "trx_lock_memory_bytes": 1136,
    "trx_rows_locked": 5,
    "trx_rows_modified": 0,
    "trx_concurrency_tickets": 0,
    "trx_isolation_level": "REPEATABLE READ",
    "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,
    "trx_schedule_weight": null
  }
]

INNODB_TRX

列名	含义
trx_id	InnoDB内部的唯一事务ID，不会为只读且非锁定事务创建ID
trx_state	事务的执行状态。值为 RUNNING(运行), LOCK WAIT(等待锁), ROLLING BACK(正在回滚), 和 COMMITTING(正在提交)
trx_started	事务开始的时间
trx_requested_lock_id	如果状态为锁等待则为事务当前等待的锁id，否则为空
trx_wait_started	开始等待锁的时间，如果 trx_state 不是 LOCK WAIT，则为空
trx_weight	事务的权重，反映事务修改和锁定的行数（不一定是准确的数量），当解决死锁时，InnoDB会选择权重最小的事务作为牺牲品让其回滚。已经更改非事务表的事务被认为比其他事务的权重更重，不管其修改和锁定的行数
trx_mysql_thread_id	MySQL线程ID，要获取更多的线程信息，需要关联INFORMATION_SCHEMA PROCESSLIST表进行查询
trx_query	事务正在执行的SQL语句
trx_operation_state	事务当前的状态。updating or deleting
trx_tables_in_use	事务执行SQL语句时使用的表的数量
trx_tables_locked	当前SQL语句在多少张表(表的数量)上持有锁。因为这些是行锁，不是表锁，所以，尽管某些行被锁定，这些表仍然可以被多个事务读写
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_last_foreign_key_error	最后一次外键的错误详情，没有的话为空
trx_adaptive_hash_latched	自适应哈希索引是否被当前事务锁定。当自适应哈希索引搜索系统被分区时，单个事务不会锁定整个自适应哈希索引。自适应哈希索引分区由 innodb_adaptive_hash_index_parts 参数控制，默认设为 8
trx_adaptive_hash_timeout	自适应哈希超时时间。是否立即放弃自适应哈希索引的搜索latch，或者在MySQL 的调用中保留它。当没有自适应哈希索引争用时，该值保持为零，并且语句保留 latch 直到它们完成。在争用期间，它倒计时到零，并且语句在每行查找后立即释放 latch。当自适应哈希索引搜索系统被分区（由 innodb_adaptive_hash_index_parts 控制）时，该值保持为 0
trx_is_read_only	只读事务标识，1代表只读事务
trx_autocommit_non_locking	无锁自动提交标识。值为1表示事务是一个没有使用 FOR UPDATE 或者 LOCK IN SHARED MODE 字句的SELECT 语句，并且自动提交开启，这样事务就会只包含一条语句。如果这行和 TRX_IS_READ_ONLY 都为1的话，InnoDB会优化事务，以减少事务改变表数据的开销
trx_schedule_weight	记录事务的权重

LOCKS

列名	含义
lock_id	锁ID
lock_trx_id	拥有锁的事务ID
lock_mode	锁模式
lock_type	锁类型
lock_table	被锁的表
lock_index	被锁的索引
lock_space	被锁的索引
lock_page	被锁的页号
lock_rec	被锁的记录号
lock_data	被锁的数据

LOCK_WAITS

列名	含义
requesting_trx_id	请求锁的事务ID
requested_lock_id	请求锁的锁ID
blocking_trx_id	当前拥有锁的事务ID
blocking_lock_id	当前拥有锁的锁ID

解决问题

把大事务拆解成小事务执行,大事务指的是一个事务中写的行过多,写的行多锁的行也就多。
窄化锁的范围，比如用主键或唯一键更新。

总的来说就是需要从自身寻找问题,表越大的时候出现这种状况的几率就越高。

查看表的大小

查看所有数据库容量大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

查看所有数据库各表容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

查看指定数据库容量大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

查看指定数据库各表容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

DevX 会持续有趣的技术和见闻，如果你觉得本文对你有帮助希望你可以分享给更多的朋友看到。该文章会同步在微信公众号【DevXJava】, 方便在微信客户端阅读。今天开会的时候领导分享了一句话“希望大家都能坚持到最后”，这里分享给各位读者 : )