教程集 www.jiaochengji.com
教程集 >  数据库  >  mysql  >  正文 深入分析MySQL更新死锁问题

深入分析MySQL更新死锁问题

发布时间:2017-01-11   编辑:jiaochengji.com
教程集为您提供深入分析MySQL更新死锁问题等资源,欢迎您收藏本站,我们将为您提供最新的深入分析MySQL更新死锁问题资源
死锁对于mysql来讲是非常的不好的,如果我们的mysql出现死锁问题那么肯定会导致网站卡了,今天我们来看一篇关于MySQL更新死锁问题,希望文章能够让各位理解到

作为一个社交类的 App ,我们有很多操作都会同时发生,为了确保数据的一致性,会采用数据库的事物。

比如现在我们有一个点赞操作,点赞成功后,需要更改文章的热度。以下是 SQL 语句:

INSERT INTO user_praise(uid,plan_id,stage_id) VALUES(123456,14456,10023);

UPDATE plan_hot SET hot = hot 1 WHERE plan_id = 14456;

在这里我们需要用到事物来确保它的原子性,也就是要么这两条语句全部执行成功,要么就全部回滚。

问题出现

在本地这么测试后,发现并没有出现什么问题,但是部署上线后,打印出了错误日志,如下

### Error updating database. 
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error occurred while setting parameters
SQL: UPDATE plan_hot SET hot = hot 1 WHERE plan_id = ?;


错误中提示下面的更新语句出现了死锁

UPDATE plan_hot SET hot = hot 1 WHERE plan_id = ?;

该问题是第一次遇到,大概也知道是哪里出现了死锁,就是更新 hot = hot 1 的时候,hot 可能会被一个锁锁住,而下一个操作就需要等待释放锁,然后才能获取到 hot 的值,进行 hot = hot 1。如果操作过快的话,可能就会造成死锁。

但是还是不懂为什么会这样,根本原因是什么,于是查资料,发现有一个小伙伴解释得蛮清晰的,下面是他的博文。

分析解决

问题分析及解决方案参考

就是如何利用乐观锁来解决并发问题,但是项目推到线上后就报错了,如下

-08-13 15:12:44 [ERROR] com.zhubajie.coupon.app.CouponReceiveAppServiceImpl {CouponReceiveAppServiceImpl.java:50} -
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE cpn_core_coupon     SET coup_num_usr = coup_num_usr 1     WHERE coup_usr = ? AND spec_id = ? AND coup_num_usr     <     ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

错误中提示下面这条sql发生了死锁

UPDATE coupon SET coup_num_usr = coup_num_usr 1 WHERE coup_usr = ? AND spec_id = ? AND coup_num_usr < ?
首先我们来看下coupon的表结构

CREATE TABLE `coupon` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ' ',
  `spec_id` char(20) NOT NULL COMMENT '优惠券活动编号',
  `coup_usr` char(11) DEFAULT NULL COMMENT '优惠券用户',
  `coup_num_usr` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '该用户已领取该活动的券数量',
  PRIMARY KEY (`id`),
  KEY `coup_usr_idx` (`coup_usr`),
  KEY `spec_idx` (`spec_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8508 DEFAULT CHARSET=utf8 COMMENT='优惠券';

其中coup usr和spec id是索引,

mysql的事务支持与存储引擎有关,MyISAM不支持事务,INNODB支持事务,更新时采用的是行级锁。这里采用的是INNODB做存储引擎,意味着会将update语句做为一个事务来处理。前面提到行级锁必须建立在索引的基础,这条更新语句用到了索引,所以这里肯定会加上行级锁。 行级锁并不是直接锁记录,而是锁索引,如果一条SQL语句用到了主键索引,mysql会锁住主键索引;如果一条语句操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引。 这个update语句会执行以下步骤: 1、由于用到了非主键索引,首先需要获取普通索引上的行级锁 2、紧接着根据主键进行更新,所以需要获取主键上的行级锁; 3、更新完毕后,提交,并释放所有锁。

如果在步骤1和2之间突然插入一条语句:UPDATE coupon SET coup num usr = coup num usr 1 WHERE coup usr = ? AND spec id = ? AND coup num usr < ?

就会发生死锁的情况,因为一条语句获取了普通索引的锁,等待主键锁,另外一条语句获取了主键锁,等待非主键索引,这样就出现了死锁.

如何来解决update ... where ...语句的死锁问题呢?我们可以对其进行分离,首先利用where条件找到主键,然后再利用这些主键去更新数据。

因为select * where ...语句是没有锁的,所以不存在会锁上where条件里面的字段,也就不会发生死锁的情况,只有在update的时候回锁上主键。

所以改成下面两条语句

SELECT id WHERE coup_usr = ? AND spec_id = ?

UPDATE coupon SET coup_num_usr = coup_num_usr 1 WHERE id = ? AND coup_num_usr < ?

第一条语句找出所有需要更新行的主键id,然后再一条一条更新。

在采用INNODB的MySQL中,更新操作默认会加行级锁,行级锁是基于索引的,在分析死锁之前需要查询一下mysql的执行计划,看看是否用到了索引,用到了哪个索引,对于没有用索引的操作会采用表级锁。如果操作用到了主键索引会先在主键索引上加锁,然后在其他索引上加锁,否则加锁顺序相反。在并发度高的应用中,批量更新一定要带上记录的主键,优先获取主键上的锁,这样可以减少死锁的发生。

您可能感兴趣的文章:
深入分析MySQL更新死锁问题
mysql如何锁定单个表
MySQL数据库之锁的详解
mysql LOCK TABLES和UNLOCK TABLES
自动删除mysql死连接 mysql死锁
分析MySQL数据库物理备份死锁
Mysql 主键自增长auto_increment问题分析
php高级程序员该学什么
解决mysql锁表的终极方法
python多线程中的死锁和递归锁用法

[关闭]
~ ~