背景
版本 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 锁等待的对应关系
文章末尾有这三张表的字段介绍
- 首先查看一下这三张表的情况,除了第一张事务表中有三个事务外,另外两个表都没有数据。并且这三个事务都没有具体的执行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 |
看到这了,希望对你有所帮助。至少能打开思路,遇到类似问题时,不至于两眼一抹黑