死锁与解决方案
# 死锁与解决方案
下面介绍几种常见的死锁现象和解决方案:
# 表锁死锁
# 产生原因
互相持有对方想要的锁,不肯释放:用户A访问表A(锁住了表A),然后又访问表B;另一个用户B访问表B(锁住了表B),然后企图 访问表A;这时用户A由于用户B已经锁住表B,它必须等待用户B释放表B才能继续,同样用户B要等用户A释放表A才能继续,这就死锁就产生了。
- 用户A--》A表(表锁)--》B表(表锁)
- 用户B--》B表(表锁)--》A表(表锁)
# 解决方案
这种死锁比较常见,一般是由于程序的BUG产生的,除了调整的程序的逻辑没有其它的办法。
这种情况下需要仔细分析程序的逻辑,对于数据库的多表操作时,尽量按照相同的顺序进行处理,尽量避免同时锁定两个资源,如操作A和B两张表时,总是按先A后B的顺序处理, 必须同时锁定两个资源时,要保证在任何时刻都应该按照相同的顺序来锁定资源。
# 行锁死锁
# 产生原因1
如果在事务中执行了一条没有索引条件的查询,引发全表扫描,把行级锁上升为全表记录锁定(等价于表级锁),多个这样的事务执行后,就很容易产生死锁和阻塞,最终应用系统会越来越慢,发生阻塞或死锁。
# 解决方案1
SQL语句中不要使用太复杂的关联多表的查询;使用explain“执行计划"对SQL语句进行分析,对于有全表扫描和全表锁定的SQL语句,建立相应的索引进行优化。
# 产生原因2
两个事务分别想拿到对方持有的锁,互相等待,于是产生死锁。(与上面表死锁的原因是一样的)
# 解决方案2
- 在同一个事务中,尽可能做到一次锁定所需要的所有资源
- 按照id对资源排序,然后按顺序进行处理
# 共享锁转换为排他锁
# 产生原因
事务A 查询一条纪录,然后更新该条纪录;此时事务B也更新该条纪录,这时事务B 的排他锁由于事务A 有共享锁,必须等A 释放共享锁后才可以获取,只能排队等待。事务A 再执行更新操作时,此处发生死锁,因为事务A 需要排他锁来做更新操作。但是,无法授予该锁请求,因为事务B 已经有一个排他锁请求,并且正在等待事务A 释放其共享锁。
# 解决方案
- 对于按钮等控件,点击立刻失效,不让用户重复点击,避免引发同时对同一条记录多次操作;
- 使用乐观锁进行控制。乐观锁机制避免了长事务中的数据库加锁开销,大大提升了大并发量下的系统性能。需要注意的是,由于乐观锁机制是在我们的系统中实现,来自外部系统的用户更新操作不受我们系统的控制,因此可能会造成脏数据被更新到数据库中;
# 死锁排查
MySQL提供了几个与锁有关的参数和命令,可以辅助我们优化锁操作,减少死锁发生。
# 查看死锁日志
查看存储引擎近期死锁日志信息;
show engine innodb status\G;
使用explain查看下SQL执行计划
# 查看锁状态变量
通过show status like'innodb_row_lock%'
命令检查状态变量,分析系统中的行锁的争夺情况:
- Innodb_row_lock_current_waits:当前正在等待锁的数量
- Innodb_row_lock_time:从系统启动到现在锁定总时间长度
- Innodb_row_lock_time_avg: 每次等待锁的平均时间
- Innodb_row_lock_time_max:从系统启动到现在等待最长的一次锁的时间
- Innodb_row_lock_waits:系统启动后到现在总共等待的次数
如果等待次数高,而且每次等待时间长,需要分析系统中为什么会有如此多的等待,然后着手定制优化。