Mysql 锁 等待超时 1205 - Lock wait timeout exceeded

背景

版本 mysql 5.6
测试环境中反馈订单审核保存时一直在转圈圈，几十秒之后都不成功。在重现时发现数据库提示如下错误

[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

原因

Mysql的 InnoDB存储引擎是支持事务的，事务开启后没有Commit，导致该资源被长期占用，其他事务在抢占该资源时，因上一个事务的锁而导致抢占失败！因此出现 Lock wait timeout exceeded。

简单的说就是，这一条数据被其他的事务锁住了，其他的事务没提交，锁就不会释放。后面来的sql要修改这条数据的时候，就只能等着。mysql默认的锁等待时间是50秒，50秒还没获得锁的话，后面来的这个sql就会报错

查看数据库锁等待时间：

SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

查看数据库释放开启自动提交：

show variables like 'autocommit' 

分析

已经知道是因为前面的事务没有提交，导致了锁等待超时。
查一下数据库当前运行的事务，当前出现的锁，以及锁等待的对应关系

在mysql 5.5之后 information_schema 库中增加了三个关于锁的表（MEMORY引擎）；
innodb_trx 当前运行的所有事务
innodb_locks 当前出现的锁
innodb_lock_waits 锁等待的对应关系
文章末尾有这三张表的字段介绍

1. 首先查看一下这三张表的情况，除了第一张事务表中有三个事务外，另外两个表都没有数据。并且这三个事务都没有具体的执行sql（trx_query）。

select * from information_schema.INNODB_TRX

select * from information_schema.INNODB_LOCKS

select * from information_schema.INNODB_LOCK_WAITS

3. 执行修改语句，触发锁等待。再次查看这三张表的信息

• 在执行修改语句之后，innodb_trx 事务表中增加了一个事务，从trx_query中能看出就是当前对应的sql语句,并且在trx_requested_lock_id 列中能看到，当前事务对应的锁ID为 44544337509:12717:4665:39
  
• 在执行修改语句之后，innodb_locks表中出现了两把锁。除去对应的事务ID不同外，锁的表，内存页，行都是一致的。其中第一把锁为新事务的锁，第二把为事务表中旧事物（第四条事务）的锁。
  到此可以认为旧事物没有提交，导致了新事务在执行修改语句时无法获得锁，一直等待。这个在innodb_lock_waits 锁等待的对应关系中也能看出来
  
• 在执行修改语句之后，锁等待对应关系表中也出现了一条数据，这里就非常明确的能看出，新事物在等待旧事物提交（第四条事务），释放锁
  

解决

一般遇到这种问题都是将mysql配置文件中自动提交打开，并且延长锁等待的时间，重启服务
mysql自动提交打开
set global autocommit=0;
增大锁等待时间
innodb_lock_wait_timeout=500

不过很明显，在我这个案例中。这种方式是无效的。
可以在事务表中看到旧事物已经好几个小时了，一直都没有提交。所以在这只能采取强行kill线程ID的方式杀死线程，释放锁资源。

当然事后需要找到这个事务一直不提交的原因才能避免后期依然发生这样的问题。

三张表具体的字段说明

参考：MySQL 5.5 – innodb_lock_wait 锁 等待

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

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
requested_lock_id	请求锁的锁ID
blocking_trx_id	当前拥有锁的事务ID
blocking_lock_id	当前拥有锁的锁ID

看到这了，希望对你有所帮助。至少能打开思路，遇到类似问题时，不至于两眼一抹黑