• 欢迎访问开心洋葱网站,在线教程,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站,欢迎加入开心洋葱 QQ群
  • 为方便开心洋葱网用户,开心洋葱官网已经开启复制功能!
  • 欢迎访问开心洋葱网站,手机也能访问哦~欢迎加入开心洋葱多维思维学习平台 QQ群
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏开心洋葱吧~~~~~~~~~~~~~!
  • 由于近期流量激增,小站的ECS没能经的起亲们的访问,本站依然没有盈利,如果各位看如果觉着文字不错,还请看官给小站打个赏~~~~~~~~~~~~~!

MYSQL update语句导致的死锁

Mysql 水墨上仙 2725次浏览 0个评论

因为需要批量并发的修改表中数据,发现日志中有部分SQL执行失败了,并报错:Deadlock found when trying to get lock; try restarting transaction,明显是出现死锁问题了。

死锁产生原因

  • mysql innodb引擎支持事务,锁级别是行级锁。但这个行级锁是有索引的情况。
  • 行级锁并不是直接锁记录,而是锁索引,如果一条SQL语句用到了主键索引,mysql会锁住主键索引;如果一条语句操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引。如果操作用到了主键索引会先在主键索引上加锁,然后在其他索引上加锁,否则加锁顺序相反。
  • 对于没有用索引的操作会采用表级锁

表结构

CREATE TABLE `user_video` (
  `id` varchar(64) NOT NULL DEFAULT '' COMMENT '主键',
  `user_id` varchar(40) NOT NULL DEFAULT '' COMMENT '用户id',
  `video_id` varchar(255) NOT NULL DEFAULT '' COMMENT '视频id',
  `title` bit(1) NOT NULL DEFAULT b'0'
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `user_video_user_id_IDX` (`user_id`) USING BTREE,
  KEY `user_video_video_id_IDX` (`video_id`) USING BTREE,
  KEY `user_video_create_time_IDX` (`create_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码

分析

产生死锁的sql语句如下: update user_video set title='哈哈' where user_id=? and video_id=?

由于用到了非主键索引,首先需要获取user_video_video_id_IDX上的行级锁紧接着根据主键进行更新,所以需要获取主键上的行级锁;更新完毕后,提交,并释放所有锁。 user_id和video_id都有索引,mysql会锁住user_id和video_id的行,如果并发情况,有同样的video_id过来,不同的user_id会被锁住。

问题模拟

在代码中执行时,会出现报错信息:Deadlock found when trying to get lock; try restarting transaction,这代表了执行SQL过程出现锁等待现象。

SQL模拟
# sql1
start TRANSACTION;
UPDATE  user_video   set  modify_time=NOW() 
where  video_id='1'
and  user_id='1005';
select sleep(10);
UPDATE  user_video   set modify_time=NOW() 
where  video_id='1'
and  user_id='1004';
COMMIT;

# sql2
start TRANSACTION;
UPDATE  user_video   set modify_time=NOW() 
where  video_id='1'
and  user_id='1004';
select sleep(5);
UPDATE  user_video  set modify_time=NOW() 
where  video_id='1'
and  user_id='1005';
COMMIT

# sql3
show status like '%lock%';
复制代码
现象

在Navicat中打开三个窗口,分别执行sql1、sql2、sql3。执行过程中不断刷新sql3,会发现Innodb_row_lock_current_waits的数量在增加1,执行完sql1、sql2又恢复了。

总结

由于用到了非主键索引,首先需要获取video_id上的行级锁紧接着根据主键进行更新,所以需要获取主键上的行级锁;更新完毕后,提交,并释放所有锁。

解决办法

结合业务需求,保证先根据其他字段获取主键索引,用主键索引去更新数据。


开心洋葱 , 版权所有丨如未注明 , 均为原创丨未经授权请勿修改 , 转载请注明MYSQL update语句导致的死锁
喜欢 (37)

您必须 登录 才能发表评论!

加载中……