Mysql 并发update死锁_error updating database. cause: com.mysql.cj.jdbc.

更新update死锁问题MySQL## 标题

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

### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in file [/Users/hong/demo/target/classes/mapper/Mapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: update      set b = ?     where a = ? and b = ?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	
1
2
3
4
5
6
7
8

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

update set b = ? where a = ? and b = ?

首先我们来看下表结构

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `a` int(11) DEFAULT NULL COMMENT '时间，格式如1001',
  `b` int(11) DEFAULT NULL COMMENT '条件id',
  `num` int(11) NOT NULL DEFAULT '0' COMMENT '数量',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_a` (`a`) USING BTREE,
  KEY `idx_b` (`b`) USING BTREE
) COMMENT='日活量查询结果表(测试deadlock)';

其中a和b是普通索引，id是主键索引
1
2
3
4
5
6
7
8
9
10
11

INSERT INTO test (id, a, b, num)VALUES (7, 1000, 1000, -1);

mysql的事务支持与存储引擎有关，MyISAM不支持事务，INNODB支持事务，更新时采用的是行级锁。这里采用的是INNODB做存储引擎，意味着会将update语句做为一个事务来处理。前面提到行级锁必须建立在索引的基础，这条更新语句用到了索引，所以这里肯定会加上行级锁。
行级锁并不是直接锁记录，而是锁索引，如果一条SQL语句用到了主键索引，mysql会锁住主键索引；如果一条语句操作了非主键索引，mysql会先锁住非主键索引，再锁定主键索引。 这个update语句会执行以下步骤：

1、由于用到了非主键索引，首先需要获取普通索引上的行级锁
2、紧接着根据主键进行更新，所以需要获取主键上的行级锁；
3、更新完毕后，提交，并释放所有锁。

如果在步骤1和2之间插入一条语句：
第一步：

update test set a = null, b = 66 where id = 7;

第2步：

update test set b = 77 WHERE a = 1000 AND b = 1000;

通过代码进行模拟进行并发：

@GetMapping("/test11")
    public String deadLock999() {
        for(int i = 0; i < 10; i++){
            Thread thread1 = new Thread(() -> {
                dauResultService.updateABbyId(7,null,66);

            });
            Thread thread2 = new Thread(() -> {
                dauResultService.updateBbyAB(1000,1000,77);
            });
            thread1.start();
            thread2.start();
        }
        return "执行成功2";
    }

dauResultService:
@Transactional(propagation = Propagation.REQUIRES_NEW)
    public void updateABbyId(Integer id,Integer a,Integer b) {
        dauResultMapper.updateABbyId(id,a,b);
        return;
    }
    
@Transactional(propagation = Propagation.REQUIRES_NEW)
    public void updateBbyAB(Integer a,Integer b,Integer b1) {
        dauResultMapper.updateBbyAB(a,b,b1);
        return;
    }
    
 mapper.java:
 int updateABbyId(@Param("id") Integer id,
                     @Param("a") Integer a,
                     @Param("b") Integer b);
                     
 int updateBbyAB(@Param("a") Integer a,
                 @Param("b") Integer b,
                 @Param("b1") Integer b1);



mapper.xml
    <update id="updateABbyId" >
        update dau_result
        set a = #{a,jdbcType=INTEGER},b = #{b,jdbcType=INTEGER}
        where id = #{id,jdbcType=INTEGER}
    </update>
    
 <update id="updateBbyAB" >
    update dau_result
    set b = #{b1,jdbcType=INTEGER}
    where a = #{a,jdbcType=INTEGER} and b = #{b,jdbcType=INTEGER}
  </update>

 <update id="updateBbyId" >
        update dau_result
        set a = null,b = #{b,jdbcType=INTEGER}
        where id = #{id,jdbcType=INTEGER}
  </update>

备注： 
dauResultService.updateABbyId(7,null,1000); 需set a= null,时产生死锁，如果不设置a=null 不产生死锁。

sql:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

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

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

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

所以改成下面两条语句

SELECT id from test WHERE a = 1000 and b =1000;
 
UPDATE test SET b = 1000 WHERE id = 7;
1
2
3

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

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