Lock wait timeout exceeded； try restarting transaction

ERROR org.apache.struts2.dispatcher.DefaultDispatcherErrorHandler - Exception occurred during processing request: 对象.update (batch index #1) failed.

 Cause: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
com.cmdi.b2bjoy.common.exception.HessianMethodException: 对象.update (batch index #1) failed. 
Cause: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction

原因：产生了死锁

解决办法：

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;

root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;

root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;

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

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

mysql> show variables like '%timeout%';

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设置请参考我的另一篇博客（mysql sync_binlog和 innodb_flush_log_at_trx_commit_ITPUB博客）

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=的值 控制。