mysql 执行delete引发死锁问题

淡淡的烟草味﹌ 2023-02-21 11:38 9阅读 0赞

### 关于mysql事务引发的死锁异常解决 ###

*  场景
 *   *  问题
     *  死锁日志\[^2\]
     *  锁类型与隔离级别
     *   *  InnerDB 锁：
         *  mysql事务
     *  解决方案

# 场景 #

mysql 5.7  
InnoDB存储引擎  
jdk 8 springboot  
hikari 连接池

spring:
      application:
        name: product
      datasource:
        url: jdbc:mysql://123456:3306/productdb?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
        userName: 
        password: 
        driver-class-name: com.mysql.cj.jdbc.Driver
        hikari:
          #连接只读数据库时配置为true， 保证安全
          readOnly: false
          #等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
          connectionTimeout: 3000000
          #一个连接idle状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟
          idleTimeout: 600000
          #一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:30分钟，建议设置比数据库超时时长少30秒，参考MySQL wait_timeout参数（show variables like '%timeout%';，一般为8小时）
          #maxLifetime: 600000
          #连接池中允许的最大连接数。缺省值：10；推荐的公式：((core_count * 2) + effective_spindle_count)
          maximumPoolSize: 200
          pool-name: pool-lxl
          connectionTestQuery: SELECT 1
          minimum-idle: 50

导入号和修改手机号场景，高并发情况下频繁修改数据库同一张表。  
先通过手机号搜索出数据。  
再通过uin搜索出相关的数据，如果搜索出的数据大于1则删除后再添加通过手机号搜索出的数据。  
语句是delete，通过uin删除数据，当前情景下uin无索引。  
同样的执行操作，并发场景：  
登录失败时删除  
用户手动删除数据

## 问题 ##

mysql删除语句  
delete from device where uin = xxxxxxxxxx

等待锁

index PRIMARY of table `productdb`.`device` trx id 27264001 lock\_mode X locks rec but not gap waiting

等待锁类型1  
X locks rec but not gap waiting

## 死锁日志2 ##

------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2020-06-29 12:16:52 0x7f03c2cfd700
    
    *** (1) TRANSACTION:
    TRANSACTION 27264001, ACTIVE 5 sec fetching rows
    mysql tables in use 1, locked 1
    LOCK WAIT 73 lock struct(s), heap size 8400, 16 row lock(s), undo log entries 9
    MySQL thread id 3248046, OS thread handle 139658719131392, query id 201165398 172.31.242.1 root updating
    delete from device
        where uin = 1107000000
        
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    
    RECORD LOCKS space id 224 page no 75 n bits 208 index PRIMARY of table `productdb`.`device` trx id 27264001 lock_mode X locks rec but not gap waiting
    Record lock, heap no 104 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
     0: len 11; hex 3134353231393836343539; asc 14521986459;;
     1: len 6; hex 0000019fd14c; asc      L;;
     2: len 7; hex a70000014d0110; asc     M  ;;
     3: SQL NULL;
     4: len 19; hex 777869645f7979683937336d7036746c6d3232; asc wxid_yyh973mp6tlm22;;
     5: len 8; hex 6161313233313233; asc aa123123;;
     6: len 15; hex 363637323838383135323635373838; asc 667288815265788;;
     7: len 16; hex 41313561643064316434666661636332; asc A15ad0d1d4ffacc2;;
     8: len 8; hex 8000000014f0d0c3; asc         ;;
    
    *** (2) TRANSACTION:
    
    TRANSACTION 27264064, ACTIVE 0 sec fetching rows
    mysql tables in use 1, locked 1
    24 lock struct(s), heap size 3520, 3 row lock(s), undo log entries 1
    MySQL thread id 3248058, OS thread handle 139654130030336, query id 201165376 172.31.242.1 root updating
    delete from device
        where uin = 576000000
        
    *** (2) HOLDS THE LOCK(S):
    
    RECORD LOCKS space id 224 page no 75 n bits 208 index PRIMARY of table `productdb`.`device` trx id 27264064 lock_mode X locks rec but not gap
    Record lock, heap no 104 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
     0: len 11; hex 3134353231393836343539; asc 14521986459;;
     1: len 6; hex 0000019fd14c; asc      L;;
     2: len 7; hex a70000014d0110; asc     M  ;;
     3: SQL NULL;
     4: len 19; hex 777869645f7979683937336d7036746c6d3232; asc wxid_yyh973mp6tlm22;;
     5: len 8; hex 6161313233313233; asc aa123123;;
     6: len 15; hex 363637323838383135323635373838; asc 667288815265788;;
     7: len 16; hex 41313561643064316434666661636332; asc A15ad0d1d4ffacc2;;
     8: len 8; hex 8000000014f0d0c3; asc         ;;
    
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 224 page no 75 n bits 208 index PRIMARY of table `productdb`.`device` trx id 27264064 lock_mode X locks rec but not gap waiting
    Record lock, heap no 23 PHYSICAL RECORD: n_fields 9; compact format; info bits 32
     0: len 11; hex 3134353739323835393638; asc 14579285968;;
     1: len 6; hex 000001a00401; asc       ;;
     2: len 7; hex 25000007132c16; asc %    , ;;
     3: SQL NULL;
     4: len 19; hex 777869645f32706b646a62636c6e3432663232; asc wxid_2pkdjbcln42f22;;
     5: len 11; hex 6d696e796f6e6763687539; asc minyongchu9;;
     6: len 15; hex 363830303638323834323632393936; asc 680068284262996;;
     7: len 16; hex 41653031623564613061616366363433; asc Ae01b5da0aacf643;;
     8: len 8; hex 8000000030a54cf0; asc     0 L ;;
    
    *** WE ROLL BACK TRANSACTION (2)

## 锁类型与隔离级别 ##

### InnerDB 锁： ###

*  共享锁（S Lock）：允许事务读取一行数据，多个事务可以拿到一把S锁（即读读并行）；
 *  排他锁（X Lock）：允许事务删除或更新一行数据，多个事务有且只有一个事务可以拿到X锁（即写写/写读互斥）；
 *  意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁；
 *  意向排他锁（IX Lock）：事务想要获得一张表中某几行的排他锁；
 *  插入意向锁（Insert Intention Lock）：插入意向锁是间隙锁的一种，专门针对insert操作的。即多个事务在同一个索引、同一个范围区间内插入记录时，如果插入的位置不冲突，则不会阻塞彼此
 *  自增锁（Auto-inc Locks）：自增锁是一种特殊的表级别锁，专门针对事务插入AUTO-INCREMENT类型的列。即一个事务正在往表中插入记录时，其他事务的插入必须等待，以便第1个事务插入的行得到的主键值是连续的。
 *  记录锁（Record Locks）- locks rec but not gap  
    记录锁是的单个行记录上的锁，会阻塞其他事务对其插入、更新、删除；
 *  间隙锁(Gap Lock) ：间隙锁锁定记录的一个间隔，但不包含记录本身。
 *  临键锁（Next-Key Lock）= Gap Lock + Record Lock  
    临建锁是记录锁与间隙锁的组合，即：既包含索引记录，又包含索引区间，主要是为了解决幻读

参考3

### mysql事务 ###

<table> 
 <thead> 
  <tr> 
   <th>事务隔离级别<sup><a rel="nofollow">4</a></sup></th> 
   <th>脏</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>读未提交（read-uncommitted）</td> 
   <td>是</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>不可重复读（read-committed）</td> 
   <td>否</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>可重复读（repeatable-read）</td> 
   <td>否</td> 
   <td>否</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>串行化（serializable）</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
  </tr> 
 </tbody> 
</table>

## 解决方案 ##

1、对于引起的条件语句添加索引：  
uin

2、设置数据库的事务隔离级别（并发数据量大大场景作用不明显，需要小心脏数据）：  
read commit

> select @@global.tx_isolation,@@tx_isolation;
    
    > set 作用域 transaction isolation level 事务隔离级别;

3、针对业务场景，更新为使用redis记录uin与登录账号

--------------------

1.  [mysql锁][mysql] ↩︎
2.  [死锁日志分析][Link 1] ↩︎
3.  [记录一次MySQL死锁的分析与解决过程][Link 1] ↩︎
4.  [MySQL的四种事务隔离级别][MySQL] ↩︎

[mysql]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html
[Link 1]: https://blog.csdn.net/noaman_wgs/article/details/82529908
[MySQL]: https://www.cnblogs.com/wyaokai/p/10921323.html