Mysql报Deadlock found when trying to get lock; try restarting transaction问题解决!!
文章目录
- 问题发生场景
- Mysql锁类型分析
- 死锁原理
- 问题排查过程
- 问题原因
- 解决方法
- 经验教训
- 查看mysql死锁日志
问题发生场景
今天记录一下最近项目中遇到的一个问题,前几天在部署项目后,在线上运行过程中,突然报了入下这样的错误,从报错信息中我们可以看到,是mysql在执行update操作的时候报了一个死锁的问题,今天解决了,特此记录一下.
Mysql锁类型分析
MySQL有三种锁的级别：页级、表级、行级,这个地方我遇到的问题是来自于行级锁,所以重点说一下。
| 类型 | 特性 |
|---|---|
| 表级锁 (table-level locking) | 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。 |
| 行级锁 (row-level locking) | 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。 |
| 页面锁 (page-level locking) | 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。 |
行级锁在使用的时候并不是直接锁掉这行记录,而是锁索引
如果一条sql用到了主键索引(mysql主键自带索引),mysql会锁住主键索引;
如果一条sql操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引.
死锁原理
mysql的两种锁排它锁(X锁)和共享锁(S锁)(mysql还有其他锁，需要了解可以自己去查，这个地方列举两个)：
- X锁,是事务T对数据A加上X锁时,只允许事务T读取和修改数据A，别的事务就没办法读取和修改，所以也叫排它锁，是互斥的
- S锁,是事务T对数据A加上S锁时,其他事务只能再对数据A加S锁,而不能加X锁,直到T释放A上的S锁，别的事务也用加S锁，所以也叫共享锁，是不互斥的
一般造成死锁的原因是因为两个事物添加锁的时候没能及时的解锁释放资源，等到第二个事务要添加锁的时候发现已经被锁，从而造成环路等待，构成死锁条件。
问题排查过程
通过上面log日志中的报错信息,能很快确认报错的准确位置,
这个地方是执行了一个update的操作,我找到了了这个表,看了一下这个表的索引是有三个,两个非主键索引,一个主键索引.
果然,在图中这两条索引在那个update语句中都有进行的操作,具体如下
然后我又找了一下有可能会跟这条语句发生冲突的地方,果然在这个报错信息的上面,执行了一条这样的sql,这两条sql执行的间隔仅仅不超过1毫秒
根据上面所说的,如果一条sql用到了主键索引(mysql主键自带索引),mysql会锁住主键索引;
如果一条sql操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引.
因此sql(2)在使用的时候用到了schedu_id这个非主键索引,还需要锁定主键索引,然而此时sql(1)开始执行
,然后锁定了主键索引,但是在set操作中还用到了schedu_id这个非主键索引,但是这个索引在sql(1)执行的时候还在处于被锁的状态,因此两条sql就出现了对索引资源的竞争,造成了死锁.
问题原因
我的事务1中update wc_examine会多很多个update，这里有N行记录被锁定，事务的更新大量数据时间比较长，更新会加x锁，而此时事务2是UPDATE wc_examine ，在update之前先执行了select 操作，添加了S锁，然后想要update的时候添加X锁。
事务1的X锁正准备加上还是还没加上，实际是存在X锁，但是事务2加了s锁，事务1会等待事务2的s锁 事务2的完整事务加了s锁立即就要加x锁，但是事务1的x锁没有释放。造成了环路等待。
解决方法
这个地方,代码的问题需要根据情况自己去修改,
- 可以试着把索引去掉(有风险),
- 或者在进行update的时候尽量避开非主键索引
我这里记录一下被锁后应该怎么去解决的方法,首先先用sql查询一下mysql的事务处理表
select * from information_schema.INNODB_TRX
正常情况下的状态都是RUNNING,但是在被锁之后就会变成LOCK WAIT ,一旦出现这种情况,就得杀死这个进程,如果进程杀不死就只能重启Mysql服务了
杀死进程
kill 进程ID
经验教训
无论前台后台的程序，都不应该存在仅根据非主键的几个字段一查就要update/delete的场景。即使有，也应该改为先把要更新的记录查出来然后逐条按主键id更新。
查看mysql死锁日志
show engine innodb status
找到信息中LATEST DETECTED DEADLOCK这一行，可以看到mysql的死锁信息详情
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-08-20 01:57:49 7fa264240700
*** (1) TRANSACTION:
TRANSACTION 309164542, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 848
MySQL thread id 177543, OS thread handle 0x7fa26c0a0700, query id 698443941 192.168.96.32 root update
INSERT INTO kmsong.km_tbl_companygrandsong(CompanyID, SongID, SongDate)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 321 page no 13948 n bits 240 index `idx_companygrandsong_songid` of table `kmsong`.`km_tbl_companygrandsong` trx id 309164542 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 134 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 8; hex 3030303234373631; asc 00024761;;1: len 8; hex 0000000000016dc8; asc m ;;*** (2) TRANSACTION:
TRANSACTION 309164538, ACTIVE 2 sec fetching rows
mysql tables in use 1, locked 1
9714 lock struct(s), heap size 1275432, 632965 row lock(s), undo log entries 565
MySQL thread id 177544, OS thread handle 0x7fa264240700, query id 698448556 192.168.96.32 root updating
delete from kmsong.km_tbl_companygrandsong where CompanyID=-1 AND SongID='00053458'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 321 page no 13948 n bits 240 index `idx_companygrandsong_songid` of table `kmsong`.`km_tbl_companygrandsong` trx id 309164538 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;可以很明显看出来是因为批量写入和删除产生了数据库死锁异常。
