【工作笔记】数据库死锁的排查和数据库锁知识记录

起因

最近在做一个新项目，特点就是数据量特别大，更新还频繁，但没啥并发。照常写代码、写sql、测试、上线。。。上线之后便召唤来了质量同学。。。
亲，您的 bug 来了~
查一下居然是数据库死锁，业务就不介绍了，直接上死证吧

Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
1

于是赶快数据库执行下边的命令查一下为啥会有死锁，虽然有些时候可能通过报错的地方可以猜测出来。不过咱是个严谨的人儿~

------------------------
执行下边命令可以查看数据库最近的一次死锁，执行命令后，粘贴state里边的值就好了。中间部分就是死锁的内容，见下方
------------------------
show engine innodb status;

1
2
3
4
5

------------------------
LATEST DETECTED DEADLOCK （敏感内容咱已修改）
------------------------
2022-06-03 08:03:23 0x2b8a3af39700
*** (1) TRANSACTION:
    // 注释：事务ID 355199151
    TRANSACTION 355199151,
    // 注释：当前事务活跃了 97S,且只使用一个表
    ACTIVE 97 sec starting index read mysql tables in use 1, 
    // 注释：locked 1 表示有一个表锁；LOCK WAIT 表示正在等待锁；4 lock struct(s) 表示四个锁锁结构
    locked 1 LOCK WAIT 4 lock struct(s), 
    heap size 1136, 
    2 row lock(s), 
    undo log entries 1 MySQL thread id 13767769, 
    OS thread handle 47871886231296, 
    query id 4662968700 127.0.0.1 u_user_rw updating

    DELETE FROM user  WHERE (UNIQUE_CODE IN ('xxxxx'))

        *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
        RECORD LOCKS space id 10900 page no 4213 n bits 192 index uq_unique_code of table `user_uat`.`user` trx id 355199151 
        lock_mode X locks rec but not gap waitingRecord lock, heap no 124 PHYSICAL RECORD: n_fields 2; compact format; 
        info bits 32 
        0: len 30; hex 313831313536343765326163613831663762343130376363613266323936; asc 18115647e2aca81f7b4107cca2f296; (total 64 bytes);
        1: len 8; hex 80000000000558d4; asc       X ;;

*** (2) TRANSACTION:
    TRANSACTION 355199146, ACTIVE 99 sec inserting
    mysql tables in use 1, locked 1
    6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 3
    MySQL thread id 13767760, OS thread handle 47872694523648, query id 4662969187 127.0.0.1 u_user_rw update

    INSERT INTO user (xxx,xxx,xxx)

        *** (2) HOLDS THE LOCK(S):
        RECORD LOCKS space id 10900 page no 4213 n bits 192 index uq_unique_code of table `user_uat`.`user` trx id 355199146 
        lock_mode X locks rec but not gap Record lock, heap no 124 PHYSICAL RECORD: n_fields 2; compact format; 
        info bits 32 
        0: len 30; hex 313831313536343765326163613831663762343130376363613266323936; asc 18115647e2aca81f7b4107cca2f296; (total 64 bytes);
        1: len 8; hex 80000000000558d4; asc       X ;;

        *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
        RECORD LOCKS space id 10900 page no 4213 n bits 192 index uq_unique_code of table `user_uat`.`user` trx id 355199146 
        lock mode S waiting Record lock, heap no 124 PHYSICAL RECORD: n_fields 2; compact format; 
        info bits 32 
        0: len 30; hex 313831313536343765326163613831663762343130376363613266323936; asc 18115647e2aca81f7b4107cca2f296; (total 64 bytes);
        1: len 8; hex 80000000000558d4; asc       X ;;

*** WE ROLL BACK TRANSACTION (1)
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

问题分析

• 内容介绍

1. 事务一是哪个 sql，它的锁状态
2. 事务二是哪个 sql，它的锁状态
3. 哪个事务赢了，哪个事务被回滚了

• 分析结果

1. 事务一是个 delete 操作，它等着唯一索引的 X locks rec （排他锁，记录锁）
2. 事务二是个 insert 操作，它拿着唯一索引的 X locks rec but not gap Record lock （排他锁，但不是间隙锁）
3. 事务二还有个锁，它在等待唯一索引的 S waiting Record lock（共享锁，记录锁）
4. 分析一和分析三互相等待，事务一最后被回滚了（一旦发生死锁，MySQL 会选择相对小的事务(undo较少的)进行回滚）

问题处理

具体处理大致就是 delete 是正常业务流程，update 是一个任务调度的异步批量操作。所以改 update 就完了，代码问题，具体怎么改咱还要脸就不说了

数据库锁的知识回顾

问题处理完了，看着东西不多。那些乱七八糟的锁看似眼熟，出了问题才发现谁也不认识谁。所以各种问度娘的问题还是要总结下的

锁粒度

首先锁可以分为：表锁，页锁，行锁

• 行锁之共享锁( S lock )
  允许事务读一行数据，一般记为 S，也称为读锁

• 行锁之排他锁 ( X lock )
  允许事务删除或者更新一行数据，一般记为 X，也称为写锁

锁模式

• Record Lock（记录锁）
  这里需要明白的是，锁直接加在索引记录上，而不是行数据

• Gap Lock（间隙锁）
  这里需要明白的是，锁加在了索引记录间隙（记住是间隙不是记录本身！），确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别

什么是间隙？
假如有一个索引 key 目前有 (1, 3, 5, 7, 9) 五个 key。你要是更新 key=7 时。间隙锁就会锁定 (5,7) 和 (7,9) 这两个范围的数据，然后找到 key=7 的数据行的主键索引和非唯一索引，对 key 加上锁

• Next-Key Lock
  行锁和间隙锁组合起来就叫 Next-Key Lock，以此防止幻读的发生
  默认情况下，InnoDB 中，更新非唯一索引对应的记录，会加上 Next-Key Lock。如果更新记录为空，就不能加记录锁，只能加间隙锁

锁选择

1. 如果更新条件没有走索引
   1. 此时所有记录都会加 X 锁和 Gap 锁，相当于进行了表锁
2. 如果更新条件为索引字段，但是并非唯一索引（包括主键索引）
   1. 使用 Next-Key Lock，此时匹配的数据会加 X 锁，记录间隙会加 Gap 锁
3. 如果更新条件为唯一索引（包括主键索引）
   1. 因为唯一索引和主键索引是等值查询，则加 Record Lock（记录锁）
   2. 唯一索引需要锁住唯一索引和主键索引，主键索引只需要锁住主键即可
4. 间隙锁是在可重复读隔离级别下才会生效的

如何尽可能避免死锁

1. 合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争
2. 调整业务逻辑 SQL 执行顺序， 避免 update/delete 长时间持有锁的 SQL 在事务前面
3. 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小
4. 以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁
5. 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录
6. 尽量按主键/索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住
7. 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂 SQL 分解为多个简单的 SQL