阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL死锁问题解决一例

169次阅读
没有评论

共计 1666 个字符,预计需要花费 5 分钟才能阅读完成。

一、环境

  • CentOS, MySQL 5.6.21-70, JPA
  • 问题场景:系统有定时批量更新数据状态操作,每次更新上千条记录,表中总记录数约为 500W 左右。

二、错误日志


2017-2-25 17:38:41 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Lock wait timeout exceeded; try restarting transaction
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
警告: SQL Error: 1213, SQLState: 40001
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Deadlock found when trying to get lock; try restarting transaction

三、排查

Check InnoDB status for locks
mysql> SHOW ENGINE InnoDB STATUS;

Check MySQL open tables
mysql> SHOW OPEN TABLES WHERE In_use > 0;

Check pending InnoDB transactions
mysql> SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; 

Check lock dependency - what blocks what
mysql> SELECT * FROM `information_schema`.`innodb_locks`;

排查后发现都是执行类似这样的语句出现问题的:


update t_task_tel set state='iok', update_date='2017-02-27 11:03:02' where tel_id=66042 and task_id=350199;

四、分析

搜索相关资料后发现,原来 MySQL InnoDB 并不一定都是行级锁。

4、锁选择
1)、如果更新条件没有走索引,例如执行”update from t1 set v2=0 where v2=5;”,此时会进行全表扫描,扫表的时候,要阻止其他任何的更新操作,所以上升为表锁。
2)、如果更新条件为索引字段,但是并非唯一索引(包括主键索引),例如执行“update from t1 set v2=0 where v1=9;”那么此时更新会使用 Next-Key Lock。使用Next-Key Lock 的原因:
a)、首先要保证在符合条件的记录上加上排他锁,会锁定当前非唯一索引和对应的主键索引的值;
b)、还要保证锁定的区间不能插入新的数据。
3)、如果更新条件为唯一索引,则使用Record Lock(记录锁)。

InnoDB根据唯一索引,找到相应记录,将主键索引值和唯一索引值加上记录锁。但不使用 Gap Lock(间隙锁)。

MySQL InnoDB 锁表与锁行  http://www.linuxidc.com/Linux/2017-02/141110.htm

由于 InnoDB 预设是 Row-Level Lock,所以只有「明确」的指定主键,MySQL 才会执行Row lock (只锁住被选取的资料例),否则 MySQL 将会执行Table Lock (将整个资料表单给锁住)。

根据分析结论,猜测是在更新_task_tel 表时 Where 条件中 tel_id 和 task_id 没有建立 UNIQUE(唯一索引)原因;

五、解决

据此分析,尝试通过 tel_id 和 task_id 两个字段建立 UNIQUE(唯一索引)来解决。(也可以先查询出来,然后根据主键 ID 来更新,这样不会因表中数据量较大影响线上业务)。

通过此种方式解决后,问题没有再重现。

如果你的问题和我遇到的类似,可以尝试据此解决。

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-02/141116.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计1666字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中