Mysql造成锁的情况有很多,下面我们就列举一些情况:
- 执行
DML
操作没有 Commit,再执行删除操作就会锁表。 - 在同一事务内先后对同一条数据进行插入和更新操作。
- 表索引设计不当,导致数据库出现死锁。
- 长事物,阻塞
DDL
,继而阻塞所有同表的后续操作。
但是要区分的是 Lock wait timeout exceeded
与 Dead Lock
是不一样。
Lock wait timeout exceeded
:后提交的事务等待前面处理的事务释放锁,但是在等待的时候超过了mysql的锁等待时间,就会引发这个异常。Dead Lock
:两个事务互相等待对方释放相同资源的锁,从而造成的死循环,就会引发这个异常。
还有一个要注意的是 innodb_lock_wait_timeout
与 lock_wait_timeout
也是不一样的。
innodb_lock_wait_timeout
:innodb 的DML
操作的行级锁的等待时间lock_wait_timeout
:数据结构DDL
操作的锁的等待时间
如何查看 innodb_lock_wait_timeout
的具体值?
1 | SHOW VARIABLES LIKE 'innodb_lock_wait_timeout' |
参数修改的范围有Session和Global,并且支持动态修改,可以有两种方法修改:
方法一:
通过下面语句修改
1 | set innodb_lock_wait_timeout=100; |
注意 global 的修改对当前线程是不生效的,只有建立新的连接才生效。
方法二:
修改参数文件 /etc/my.cnf
:
1 | innodb_lock_wait_timeout = 50 |
innodb_lock_wait_timeout
指的是事务等待获取资源等待的最长时间,超过这个时间还未分配到资源则会返回应用失败; 当锁等待超过设置时间的时候,就会报如下的错误;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
。
其参数的时间单位是秒,最小可设置为1s(一般不会设置得这么小),最大可设置1073741824秒,默认安装时这个值是50s(默认参数设置)
问题导致的结果
- 数据更新或新增后数据会自动回滚
- 表操作返回
Lock wait timeout exceeded
并长时间无反应
解决方法
- 应急方法:
show full processlist
; kill掉出现问题的进程。 ps.有的时候通过processlist是看不出哪里有锁等待的,当两个事务都在commit阶段是无法体现在processlist上 - 根治方法:
select * from innodb_trx
; 查看有是哪些事务占据了表资源。 ps.通过这个办法就需要对innodb有一些了解才好处理
说起来很简单找到它杀掉它就搞定了,但是实际上并没有想象的这么简单,当问题出现要分析问题的原因,通过原因定位业务代码可能某些地方实现的有问题,从而来避免今后遇到同样的问题。
innodb_*表的解释
Mysql
的 InnoDB
存储引擎是支持事务的,事务开启后没有被主动Commit。导致该资源被长期占用,其他事务在抢占该资源时,因上一个事务的锁而导致抢占失败!因此出现 Lock wait timeout exceeded
下面几张表是innodb的事务和锁的信息表,理解这些表就能很好的定位问题。
- innodb_trx:当前运行的所有事务
- innodb_locks:当前出现的锁
- innodb_lock_waits:锁等待的对应关系
innodb_trx 表:
1 | desc information_schema.innodb_trx; |
innodb_locks 表:
1 | desc information_schema.innodb_locks; |
innodb_lock_waits 表:
1 | desc information_schema.innodb_lock_waits; |
锁等待的处理步骤
查看 innodb_lock_waits 表
1 | SELECT * FROM innodb_lock_waits; |
innodb_locks 表和 innodb_lock_waits 表联表查看:
1 | SELECT * FROM innodb_locks WHERE lock_trx_id IN (SELECT blocking_trx_id FROM innodb_lock_waits); |
innodb_locks 表 JOIN innodb_lock_waits 表联表查看:
1 | SELECT innodb_locks.* FROM innodb_locks JOIN innodb_lock_waits ON (innodb_locks.lock_trx_id = innodb_lock_waits.blocking_trx_id); |
查询 innodb_trx 表:
1 | SELECT trx_id, trx_requested_lock_id, trx_mysql_thread_id, trx_query FROM innodb_trx WHERE trx_state = 'LOCK WAIT'; |
trx_mysql_thread_id 即 kill 掉事务线程 ID
1 | SHOW ENGINE INNODB STATUS ; |
从上述方法中得到了相关信息,我们可以得到发生锁等待的线程 ID,然后将其 KILL 掉。 KILL 掉发生锁等待的线程。
1 | kill ID; |
前几天生成环境,有个需求需要跑个脚本处理一下。
脚本所做的事情是遍历查询出来的数据集,逐个调用某段逻辑。
结果等了半天,发现什么都没有修改成功。
后面查看日志才发现是因为某次抛出异常时,事务没有释放, 而后提交的事务又需要等待前面处理的事务释放锁,但是等待的时间超过了 Mysql innodb_lock_wait_timeout
所设置的超时时间,所以引发了 Lock wait timeout exceeded
异常,事务自动回滚了,也就出现了数据都没有变化的现象。