Lock wait timeout exceeded; try restarting transaction

迷南。 2023-11-26 00:43 99阅读 0赞

**一：问题分析 ：**

今天程序里报的错： java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction，重启服务后也没有效果，然后查看mysql官方文档如下：

Error: 1205 SQLSTATE: HY000 ([ER\_LOCK\_WAIT\_TIMEOUT][ER_LOCK_WAIT_TIMEOUT])

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][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的值，在特别繁忙的系统，你可以减小并发。

The length of time in seconds an InnoDB [transaction][] waits for a [row lock][] before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error:

ERROR 1205 (HY000):Lock wait timeout exceeded; try restarting transaction

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

**于是我尝试调大innodb\_lock\_wait\_timeout参数：**

具体操作：如下可知innodb\_lock\_wait\_timeout是动态参数,默认值50秒，最小值是1秒，最大值是1073741824；  
![bb][]

mysql> set GLOBAL innodb\_lock\_wait\_timeout=1500；

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

但是可怕的是问题并没有得到解决。。。。。。。。。。。。。依旧报同样的错

**二：问题真正解决：**

但是仔细想想，怎么可能会等待那么长时间呢？肯定是不正常的，怀疑是产生了死锁，那么关于关于mysql死锁的查询：

1)查询是否锁表：

show OPEN TABLES where In\_use > 0;

2）查询相关的锁：

在5.5中，information\_schema 库中增加了三个关于锁的表（inndodb引擎）：

innodb\_trx \#\# 当前运行的所有事务

innodb\_locks \#\# 当前出现的锁，查看正在锁的事务

innodb\_lock\_waits \#\# 锁等待的对应关系 ，查看等待锁的事务

看一下表结构：

root@127.0.0.1 : information\_schema 13:28:38> desc innodb\_locks;

\+————-+———————+——+—–+———+——-+

\+————-+———————+——+—–+———+——-+

| lock\_id | varchar(81) | NO | | | |\#锁ID

| lock\_mode | varchar(32) | NO | | | |\#锁模式

| lock\_type | varchar(32) | NO | | | |\#锁类型

\+————-+———————+——+—–+———+——-+

10 rows in set (0.00 sec)

root@127.0.0.1 : information\_schema 13:28:56> desc innodb\_lock\_waits;

\+——————-+————-+——+—–+———+——-+

\+——————-+————-+——+—–+———+——-+

\+——————-+————-+——+—–+———+——-+

4 rows in set (0.00 sec)

root@127.0.0.1 : information\_schema 13:29:05> desc innodb\_trx ;

\+—————————-+———————+——+—–+———————+——-+

\+—————————-+———————+——+—–+———————+——-+

| trx\_weight | bigint(21) unsigned | NO | | 0 | |\#

| trx\_lock\_structs | bigint(21) unsigned | NO | | 0 | |\#

| trx\_unique\_checks | int(1) | NO | | 0 | |\#是否唯一性检查

| trx\_foreign\_key\_checks | int(1) | NO | | 0 | |\#是否外键检查

| trx\_adaptive\_hash\_latched | int(1) | NO | | 0 | |\#

| trx\_adaptive\_hash\_timeout | bigint(21) unsigned | NO | | 0 | |\#

\+—————————-+———————+——+—–+———————+——-+

22 rows in set (0.01 sec)

**3）查询产生锁的具体sql，根据具体的sql，就能看出是不是死锁了，并且可以确定具体是执行了什么业务，是否可以kill;**

mysql> 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;

根据查出来的结果，kill掉产生锁的事务线程（注意是线程id,不是事务id)，具体如下

**把所有有锁的事务线程都kill掉（需要确认是否可以都kill）**

mysql> select concat('KILL ',a.trx\_mysql\_thread\_id ,';') from INFORMATION\_SCHEMA.INNODB\_LOCKS b,INFORMATION\_SCHEMA.innodb\_trx a where b.lock\_trx\_id=a.trx\_id;

如果太多的话可以批量执行

mysql> select concat('KILL ',a.trx\_mysql\_thread\_id ,';') from INFORMATION\_SCHEMA.INNODB\_LOCKS b,INFORMATION\_SCHEMA.innodb\_trx a where b.lock\_trx\_id=a.trx\_id into outfile '/tmp/kill.txt';

然后批量执行。

应该是数据被锁了，为了确认，去 MySQL 中查看 processlist 和 innodb 的状态

select * from INFORMATION_SCHEMA.PROCESSLIST where host like '{IP}%'

至此问题得到解决。。。。

**总结**：当看到mysql报错的时候，具体报错的错误号，不要盲目的去按着错误号去解决，需要冷静的分析下，透过现象看本质，结合实际，从根本上去解决问题，就像这个问题，如果一个事物被阻塞了50秒之久，那么基本可以断言数据库有问题！需要找根本原因

**题外话****：总结下mysql中关于timeout的参数的作用：**

mysql> show variables like '%timeout%';

\+-----------------------------+----------+

| Variable\_name | Value |

\+-----------------------------+----------+

| connect\_timeout | 10 |

| delayed\_insert\_timeout | 300 |

| innodb\_flush\_log\_at\_timeout | 1 |

| innodb\_lock\_wait\_timeout | 1500 |

| innodb\_rollback\_on\_timeout | OFF |

| interactive\_timeout | 28800 |

| lock\_wait\_timeout | 31536000 |

| net\_read\_timeout | 30 |

| net\_write\_timeout | 60 |

| rpl\_stop\_slave\_timeout | 31536000 |

| slave\_net\_timeout | 3600 |

| wait\_timeout | 28800 |

\+-----------------------------+----------+

1）connect\_timeout

connect\_timeout

官方文档中描述：The number of seconds that the [mysqld][] server waits for a connect packet before responding with Bad handshake

Increasing the connect\_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at 'XXX', system error: errno.

翻译：mysql服务等待连接包的时间（单位秒），超过这个时间就会抛出'坏握手'的响应。如果客户端经常遇到形如：Lost connection to MySQL server at 'XXX', system error: errno的错误，那么增加connect\_timeout的值可以有所帮助；

说下mysql处理客户端请求的过程：

mysql的基本原理应该是有个监听线程循环接收请求，当有请求来时，创建线程（或者从线程池中取）来处理这个请求。由于mysql连接采用TCP协议，那么之前势必是需要进行TCP三次握手的。TCP三次握手成功之后，客户端会进入阻塞，等待服务端的消息。服务端这个时候会创建一个线程(或者从线程池中取一个线程)来处理请求，主要验证部分包括host和用户名密码验证。host验证我们比较熟悉，因为在用grant命令授权用户的时候是有指定host的。用户名密码认证则是服务端先生成一个随机数发送给客户端，客户端用该随机数和密码进行多次加密后发送给服务端验证。如果通过，整个连接握手过程完成

2）delayed\_insert\_timeout

insert delay操作延迟的秒数，这里不是insert操作，而是insert delayed，延迟插入。 该参数再以后即将被遗弃，可不关注！

3）innodb\_flush\_log\_at\_timeout

这个是5.6中才出现的，是InnoDB特有的参数，日志刷新时间间隔，默认是1秒，至于双1设置请参考我的另一篇博客（[http://blog.itpub.net/29654823/viewspace-2143511/][http_blog.itpub.net_29654823_viewspace-2143511]）

4）innodb\_lock\_wait\_timeout

InnoDB事务等待一个行级锁的时间最长时间（单位是秒），超过这个时间就会放弃。默认值是50秒

5）innodb\_rollback\_on\_timeout

在innodb中，如果这个参数为 on，那么当事务中的最后一个语句超时的时候，就会回滚这个事务，默认是off,关闭状态。

6）interactive\_timeout （交互式） 和wait\_timeout（非交互式）

交互式和非交互式链接的超时设置,防止客户端长时间链接数据库,什么都不做处于sleep状态，强制关闭长时间的sleep链接。

还是先看官方文档，从文档上来看wait\_timeout和interactive\_timeout都是指不活跃的连接超时时间，连接线程启动的时候wait\_timeout会根据是交互模式还是非交互模式被设置为这两个值中的一个。如果我们运行mysql -uroot -p命令登陆到mysql，wait\_timeout就会被设置为interactive\_timeout的值。如果我们在wait\_timeout时间内没有进行任何操作，那么再次操作的时候就会提示超时，这需要mysql client重新连接。默认情况先两值的都为28800(8h)，一般情况下将两值都设置为1000s就行了。

7）lock\_wait\_timeout

获取元数据锁的超时时间。这个适合用于除了系统表之外的所有表（mysql库之外）。  
区别于innodb\_lock\_wait\_timeout是针对dml操作的行级锁的等待时间 ，而lock\_wait\_timeout是数据结构ddl操作的锁的等待时间

8）

net\_read\_timeout

net\_write\_timeout

这两个表示数据库发送网络包和接受网络包的超时时间。

9）rpl\_stop\_slave\_timeout（默认值即可，无需修改）

在 5.6.13开始以及以后的版本中，可以通过这个参数控制stop slave 的执行时间，在重放一个大的事务的时候,突然执行stop slave,命令 stop slave会执行很久（时间设置的太短会time out),这个时候可能产生死锁或阻塞,严重影响性能,默认值和最大值都是31536000秒（一年）

10）slave\_net\_timeout

The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER\_CONNECT\_RETRY option for the CHANGE MASTER TO statement, and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).

翻译：从库等待主库更多的数据的时间，超过这个时间（默认值是3600 ），slave就认为这个连接有问题了，并且终止继续读取主库的变化，尝试从新连接主库。等待超时后立马尝试第一次从新连接，

之后重连的时间间隔由CHANGE MASTER TO 的时候指定 MASTER\_CONNECT\_RETRY=的值 控制，重连的次数由CHANGE MASTER TO 的时候指定master-retry-count=的值 控制。

[ER_LOCK_WAIT_TIMEOUT]: https://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html#error_er_lock_wait_timeout
[rolled back]: https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_rollback
[transaction]: https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_transaction
[innodb_lock_wait_timeout]: https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout
[locking]: https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_locking
[concurrency]: https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_concurrency
[row lock]: https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_row_lock
[bb]: http://img.blog.itpub.net/blog/attachment/201801/23/29654823_1516715920raP2.png?x-oss-process=style/bb
[mysqld]: https://dev.mysql.com/doc/refman/5.6/en/mysqld.html
[http_blog.itpub.net_29654823_viewspace-2143511]: http://blog.itpub.net/29654823/viewspace-2143511/