MySQL -- 事务锁 Lock wait timeout exceeded

雨点打透心脏的1/2处 2023-05-29 15:24 11阅读 0赞

## 问题现象 ##

测试ddos持续攻击，防护程序频繁读写数据库，mysql处理报错。同时进行接口调用，接口响应时间较长，耗时几十秒才返回错误提示，两者错误日志均为 Error 1205: Lock wait timeout exceeded; try restarting transaction

![20191108101055566.png][]

## 问题原因 ##

查询官方手册：[官方错误文档地址][Link 1] [中文错误文档地址][Link 2]

Error number: 1205; Symbol: ER_LOCK_WAIT_TIMEOUT; SQLSTATE: HY000
    
    Message: Lock wait timeout exceeded; try restarting transaction
    
    InnoDB reports this error when lock wait timeout expires. The statement that waited too long was rolled back (not the entire transaction). You can increase the value of the innodb_lock_wait_timeout configuration option if SQL statements should wait longer for other transactions to complete, or decrease it if too many long-running transactions are causing locking problems and reducing concurrency on a busy system.

翻译：

当锁等待超时后innodb引擎报此错误，等待时间过长的语句被回滚（不是整个事务）。如果想让SQL语句等待其他事务更长时间之后完成，你可以增加参数innodb\_lock\_wait\_timeout配置的值。如果有太多长时间运行的有锁的事务，你可以减小这个innodb\_lock\_wait\_timeout的值，在特别繁忙的系统，你可以减小并发。

Mysql数据库采用InnoDB引擎，InnoDB事务等待一个行级锁的时间最长时间（单位是秒），超过这个时间就会放弃。默认值是50秒。一个事务A试图访问一行数据，但是这行数据正在被另一个innodb事务B锁定，此时事务A就会等待事务B释放锁，等待超过innodb\_lock\_wait\_timeout设置的值就会报错ERROR 1205 (HY000)。 附：查看行锁阈值

![20191108100256295.png][]

##  ##

## 问题场景 ##

1.  在同一事务内先后对同一条数据进行插入和更新操作；
2.  多台服务器操作同一数据库；
3.  瞬时出现高并发现象；(不同事务同时插入更新同一条数据)

## 解决方法 ##

**一 ：**可以尝试修改 innodb\_lock\_wait\_timeout 值(1-1073741824秒)

mysql> set GLOBAL innodb_lock_wait_timeout=1500；

关于set innodb\_lock\_wait\_timeout=1500; 和 set global innodb\_lock\_wait\_timeout=1500;的区别。前者等价于set session只影响当前session，后者作为全局的修改方式，只会影响修改之后打开的session；注意后者不能改变当前session；

**二：**根据数据库当前状态排除错误

1. 查看数据库当前的进程，看一下有无正在执行的慢SQL记录线程

show  processlist;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0Nzc3NjAw_size_16_color_FFFFFF_t_70][]

2. 查看当前事务

SELECT * FROM information_schema.INNODB_TRX;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0Nzc3NjAw_size_16_color_FFFFFF_t_70 1][]

若出现锁(LOCK WAIT)，直接 kill xxx(线程id)。 正常状态为RUNNING。

筛选需要kill掉的线程：
     select concat('KILL ',id,';') from information_schema.processlist where DB='base_anti_tp' and xxxxxxx;
    过滤掉|符号，进行批量kill进程

附 INNODB\_TRX表结构 ：

mysql> desc information_schema.innodb_trx ;
    +----------------------------+---------------------+------+-----+---------------------+-------+
    | Field                      | Type                | Null | Key | Default             | Extra |
    +----------------------------+---------------------+------+-----+---------------------+-------+
    | trx_id                     | varchar(18)         | NO   |     |                     |       |
    | trx_state                  | varchar(13)         | NO   |     |                     |       |
    | trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |
    | trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |
    | trx_wait_started           | datetime            | YES  |     | NULL                |       |
    | trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |
    | trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |
    | trx_query                  | varchar(1024)       | YES  |     | NULL                |       |
    | trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |
    | 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                   |       |
    | 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  |     | NULL                |       |
    | trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |
    | trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |
    | trx_is_read_only           | int(1)              | NO   |     | 0                   |       |
    | trx_autocommit_non_locking | int(1)              | NO   |     | 0                   |       |
    +----------------------------+---------------------+------+-----+---------------------+-------+
    24 rows in set (0.00 sec)

3.查看当前出现的锁

SELECT * FROM information_schema.INNODB_LOCKs;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0Nzc3NjAw_size_16_color_FFFFFF_t_70 2][]

从上图可以看出：出现事务X锁 -- 行锁，排他锁

附 INNODB\_LOCKS 表结构 ：

4.锁等待的对应关系

SELECT * FROM information_schema.INNODB_LOCK_waits;

附 INNODB\_LOCK\_WAITS 表结构 ：

##  ##

## 总结 ##

总体上来说，产生内存溢出与锁表都是由于代码写的不好造成的，因此提高代码的质量是最根本的解决办法。有的人认为先把功能实现，有BUG时再在测试阶段进 行修正，这种想法是错误的。正如一件产品的质量是在生产制造的过程中决定的，而不是质量检测时决定的，软件的质量在设计与编码阶段就已经决定了，测试只是 对软件质量的一个验证，因为测试不可能找出软件中所有的BUG。

--------------------

## 记录： ##

一、information\_schema简介  
在MySQL中，把 information\_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权 限等。在INFORMATION\_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。

--------------------

附：排查问题时的部分sql

select @@innodb_lock_wait_timeout;
    
    show variables like '%lock%';
    
    show table status from wam_tp where name='xxx';
    
    show ENGINES;
    
    show index from wam_tp.xxx;

**End**

[20191108101055566.png]: https://img-blog.csdnimg.cn/20191108101055566.png
[Link 1]: https://dev.mysql.com/doc/refman/8.0/en/server-error-reference.html
[Link 2]: http://www.mysqlab.net/docs/view/refman-5.1-zh/chapter/error-handling.html#error-messages-server
[20191108100256295.png]: https://img-blog.csdnimg.cn/20191108100256295.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0Nzc3NjAw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191108102902845.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0Nzc3NjAw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0Nzc3NjAw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191108102955654.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0Nzc3NjAw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0Nzc3NjAw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191108103019253.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0Nzc3NjAw,size_16,color_FFFFFF,t_70