昨天讲了第一个案例，今天讲一下第二个案例，今天的主要内容：

  • Mysql锁概览
  • 如何通过show engine innodb status分析死锁
  • 案例分析

Mysql中的锁的分类

Mysql的锁是一个老生常谈的话题，这里只会简单介绍，不会涉及详细的理论知识，在本文后面的案例分析阶段，会有这些知识的应用。

表锁

表锁是Mysql是Mysql Server层的实现，与Engine无关。

  1. -- 对order和order_item加读锁
  2. lock table order read, order_item read;
  4. -- 释放锁
  5. unlock tables;

行锁

行锁需要Engine层的支持，我们用的最多的是Innodb引擎。行锁是针对某一行数据加锁，我们在上篇文章中也说到过，Mysql的行锁是加在索引上的。

  • 行锁的粒度：

    • LOCK_ORDINARY：也称为 Next-Key Lock，Mysql对行的默认加锁规则，相当于 RECORD + GAP。
    • LOCK_GAP：间隙锁，锁两个记录之间的间隙，主要是为了防止在间隙中插入数据（update也可能会导致数据在一个GAP中插入）
    • LOCK_REC_NOT_GAP：只锁记录，
    • LOCK_INSERT_INTENSION：插入意向 GAP 锁，插入记录时使用，是 LOCK_GAP 的一种特例。

  • Mysql的加锁规则：

    • 原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
    • 原则 2：查找过程中访问到的对象才会加锁。
    • 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
    • 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
    • 优化 3：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
    • 所谓“间隙”，就是由“这个间隙右边的那个记录”定义的

Mysql中锁的模式

上文中的表锁、行锁、行锁中的GAP、Next-Key Lock都是锁的类型，Mysql中锁的模式：

  • LOCK_IS：读意向锁；
  • LOCK_IX：写意向锁；
  • LOCK_S：读锁；又称共享锁（Share locks），行锁语法lock in shared mode
  • LOCK_X：写锁；又称排他锁（Exclusive locks，简称 X 锁），行锁语法for update
  • LOCK_AUTO_INC：自增锁；特殊的锁，后面再写文章详细介绍。

注意Mysql不支持U锁（更新锁），Mysql中对于更新操作会先加S锁，再通过锁升级变为X锁。U锁表示有事务对该行有更新意向，只允许一个事务拿到U锁，该事务在发生写后U锁变X锁，未写时看做S锁。

案例分析

昨天的文章我们写到，我们优化了发号器后，发现还是会出现锁超时，而且通过show engine innodb status分析发现，迁移过程有死锁发生。我们来一步步分析一下。

背景

为了模拟事故现场，我这里准备了四张表order、order_item、reservation、reservation_item。表间关系:
Mysql锁优化案例 -- 外键导致的死锁 - 图1
建表语句：

  1. create table `order`
  2. (
  3.     id           int auto_increment
  4.         primary key,
  5.     order_number varchar(10) null,
  6.     user         varchar(10) null,
  7.     constraint order_order_number_uindex
  8.         unique (order_number)
  9. );
  10. create table order_item
  11. (
  12.     id        int auto_increment
  13.         primary key,
  14.     item_name varchar(30)   null,
  15.     price     decimal(4, 2) null,
  16.     order_id  int           not null
  17. );
  18. create table reservation
  19. (
  20.     id                 int auto_increment
  21.         primary key,
  22.     comment            varchar(255) null,
  23.     reservation_number varchar(20)  null,
  24.     order_id           int          null,
  25.     constraint reservation_reservation_number_uindex
  26.         unique (reservation_number),
  27.     constraint reservation_order_id_fk
  28.         foreign key (order_id) references `order` (id)
  29. );
  30. create table reservation_item
  31. (
  32.     id             int auto_increment
  33.         primary key,
  34.     reservation_id int null,
  35.     order_item_id  int null,
  36.     constraint reservation_item_order_item_id_fk
  37.         foreign key (order_item_id) references order_item (id),
  38.     constraint reservation_item_reservation_id_fk
  39.         foreign key (reservation_id) references reservation (id)
  40. );

业务介绍：

  • 一个业务会更新一个order下面所有order_item的价格
  • 一个业务会插入reservation数据，并且插入对应的reservation_item数据

死锁分析

由于我们负责迁移任务，对业务库的表间关系不太了解。迁移过程中发现，有些事务失败了，数据没进去。错误还是上篇文章中的锁超时。
我们用show engine innodb status，输出如下（模拟的场景，并非真实场景，真实场景要比这个复杂的多）：

  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2020-04-19 02:10:51 0x7fe5e86ae700
  5. *** (1) TRANSACTION:
  6. TRANSACTION 17192, ACTIVE 11 sec starting index read
  7. -------为了精简，删除了部分内容-----------------------------
  8. -------事务一执行的语句----------
  9. update order_item set  price = 30 where id = 2
  10. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  11. -------事务一想要获取的锁---------
  12. RECORD LOCKS space id 609 page no 3 n bits 72 index PRIMARY of table `test`.`order_item` trx id 17192 lock_mode X locks rec but not gap waiting
  13. Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
  14.  0: len 4; hex 80000002; asc     ;;
  15.  1: len 6; hex 000000004316; asc     C ;;
  16.  2: len 7; hex d3000001d3011d; asc        ;;
  17.  3: len 2; hex 4932; asc I2;;
  18.  4: len 2; hex 9400; asc   ;;
  19.  5: len 4; hex 80000001; asc     ;;
  21. *** (2) TRANSACTION:
  22. TRANSACTION 17193, ACTIVE 8 sec inserting
  23. -------为了精简，删除了部分内容----
  24. -------事务二执行的语句----------
  25. insert into reservation_item (reservation_id, order_item_id) VALUE (1, 1)
  26. *** (2) HOLDS THE LOCK(S):
  27. -------事务二持有的锁----------
  28. RECORD LOCKS space id 609 page no 3 n bits 72 index PRIMARY of table `test`.`order_item` trx id 17193 lock mode S locks rec but not gap
  29. Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
  30.  0: len 4; hex 80000002; asc     ;;
  31.  1: len 6; hex 000000004316; asc     C ;;
  32.  2: len 7; hex d3000001d3011d; asc        ;;
  33.  3: len 2; hex 4932; asc I2;;
  34.  4: len 2; hex 9400; asc   ;;
  35.  5: len 4; hex 80000001; asc     ;;
  37. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  38. -------事务二想要获取的锁----------
  39. RECORD LOCKS space id 609 page no 3 n bits 72 index PRIMARY of table `test`.`order_item` trx id 17193 lock mode S locks rec but not gap waiting
  40. Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
  41.  0: len 4; hex 80000001; asc     ;;
  42.  1: len 6; hex 000000004328; asc     C(;;
  43.  2: len 7; hex 5d000001672eb5; asc ]   g. ;;
  44.  3: len 2; hex 4931; asc I1;;
  45.  4: len 2; hex 9e00; asc   ;;
  46.  5: len 4; hex 80000001; asc     ;;
  48. *** WE ROLL BACK TRANSACTION (1)

部分内容的解释：

  • lock_mode X locks rec but not gap

    • lock_mode X 排他锁
    • locks rec but not gap RECORD 锁，行锁

  • lock mode S locks rec but not gap waiting

    • lock_mode S 共享锁
    • locks rec but not gap RECORD 锁，行锁

从业务的代码中我们发现，两个事务的具体操作如下：

  1. -- 事务一
  2. begin;
  3. update order_item set  price = 30 where id = 1;
  4. update order_item set  price = 30 where id = 2;
  5. commit;
  7. -- 事务二
  8. begin;
  9. insert into reservation_item (reservation_id, order_item_id) VALUE (1, 2);
  10. insert into reservation_item (reservation_id, order_item_id) VALUE (1, 1);
  11. commit ;

但insert操作为什么会需要一行的读锁呢？后面发现这个表其实有外键。所以猜测，插入操作，对于有外键的表，需要获取到主表（外键目标表）的目标行的读锁。后面查阅官方文档，发现外键检测确实会在目标行增加S锁。

If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails.

解决方案

既然是外键检测出的问题，有以下三种解决思路：

  1. 我们只有一个业务是会更新order_item的，其他N项业务都只是插入。所以可以从业务需求入手，把更新操作前置。由于后面的业务只会获取S锁，不会互相阻塞。
  2. 删除外键，有不少的Blog说，外键已经不是一个好的设计了，你觉得呢？
  3. 关闭外键检测，但业务服务用的是Spring JPA，单个事务控制外键检测比较麻烦。

由于我们不是业务服务的开发人员，所以只能建议去除外键，并不能真正要求业务团队去除。所以采取方案一解决。

参考资料

  1. innodb lock set
  2. innodb locking