Adrian Wang's blog

MySQL 死锁了，怎么办？ 在数据库的世界里，“死锁"是个时不时会出来捣乱的家伙。简单来说，就是两个或多个操作互相等着对方手里的"东西”（比如数据），结果谁也动不了，像交通堵塞一样卡住了。这篇文章，我们就通过一个订单系统的实际例子，聊聊死锁是怎么发生的，以及怎么尽量避免它。 想象一下，我们的系统里有新增、修改、查询订单这些功能。为了保证每个订单都是独一无二的（比如防止用户手快，重复提交了同一个订单），我们通常会在创建新订单前做个"幂等性校验"。常规操作是：先用 select ... for update 语句查一下这个订单号是不是已经存在了。这个 for update 很关键，它会尝试暂时"锁定"我们要检查的订单号（或者它可能存在的位置），目的是防止在我们检查和插入的短暂间隙，有其他操作也来插同一个订单号。如果查下来发现订单不存在，我们才真正动手插入这条新订单记录。 听起来挺稳妥，对吧？但在业务量一大，并发操作（很多用户同时操作）一多的时候，这种做法有时就会不小心触发死锁。 别担心，下面我们就一步步拆解死锁是怎么来的，以及有哪些招数可以对付它。 死锁的发生 本次案例我们用的是 MySQL 数据库，存储引擎是 InnoDB（一种常用的数据"仓库管理员"），事务的隔离级别设定为可重复读（Repeatable Read, RR，一种事务处理规则，保证在同一个事务里多次读取同样的数据，结果是一致的）。 接下来，我用实际操作带大家看看死锁是怎么发生的。 我建了一张订单表 t_order，结构如下，其中 id 字段是主键（唯一标识一条记录），order_no 字段建了个普通索引（非唯一索引，可以加快按订单号查询的速度）： 1234567CREATE TABLE `t_order` ( `id` int NOT NULL AUTO_INCREMENT, `order_no` int DEFAULT NULL, `create_date` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_order` (`order_no`) USING BTREE) ENGINE=InnoDB ; 然后，t_order 表里现在已经有了 6 条记录： id order_no create_date 1 1001 2021-12-28 13:59:07 2 1002 2021-12-28 13:59:14 3 1003 2021-12-28 13:59:21 4 1004 2021-12-28 13:59:30 5 1005 2021-12-28 13:59:36 6 1006 2021-12-28 14:24:33 假设这时有两个事务（可以理解为两个独立的业务操作流程），一个事务（我们叫它事务A）要插入订单号为 1007 的订单，另一个事务（事务B）要插入订单号为 1008 的订单。因为需要对订单做幂等性校验，所以两个事务都会先查询对应的订单是否存在，如果不存在才插入记录。过程如下： 上图的操作中，如果数据库没有开启死锁检测机制（或者检测需要时间），你就会看到，两个事务都卡住了，陷入了相互等待对方释放锁的状态，这就是死锁。 这里在查询订单是否存在时，我们特意用了 select ... for update 语句。它的主要目的是"占位"，即在当前事务检查和插入订单的这个过程中，防止其他事务也来插入相同的订单号，或者影响到我们正在判断的这个"空位"。如果不用它，就可能出现"幻读"（Phantom Read）的问题——比如我们刚查完发现订单1007不存在，正准备插入，结果另一个事务抢先一步插了1007，导致我们后续操作出错，或者最终系统里有了两条订单1007。如下图所示： 为什么会产生死锁？ 前面提到，select ... for update 是为了防止幻读。在MySQL的InnoDB存储引擎和"可重复读"（RR）这个事务隔离级别下，为了从根本上解决幻读问题（即在一个事务中，前后两次执行同样的查询，结果集却不一致，像出现了"幽灵"数据一样），引入了一种特殊的锁机制，叫做 next-key 锁。你可以把它理解成一种组合锁，它包含了两种锁的功能： Record Lock（记录锁）：顾名思义，就是直接锁住某条具体的记录本身。 Gap Lock（间隙锁）：它锁的不是某条具体的记录，而是记录与记录之间的"空档"或"缝隙"。比如，如果你的表里有订单号1005和1009，间隙锁就能锁住1005和1009之间的这个范围，防止其他事务在这中间插入新的订单号（比如1007）。这样就避免了你在这个事务里前后两次查询，发现中间突然多出来一条记录的幻读情况。 通常，我们执行普通的 select 语句是不会加锁的（它通过一种叫做MVCC的机制来实现"快照读"，保证可重复读）。如果想在查询时就给记录加上行锁（一种针对数据行的锁），可以用下面这两种方式： 123456789begin; -- 开始事务// 对读取的记录加共享锁 (S锁)select ... lock in share mode;commit; -- 提交事务，锁被释放begin; -- 开始事务// 对读取的记录加排他锁 (X锁)select ... for update;commit; -- 提交事务，锁被释放 要注意，行锁通常是在整个事务提交（commit）或回滚（rollback）后才会被释放，并不是某一条SQL语句执行完就立刻放锁。 举个例子，下面事务A的查询语句会锁住订单号大于2的范围，即 (2, +∞] 这个区间。在事务A提交前，如果有其他事务想在这个锁住的范围里插入数据，就会被阻塞。 next-key 锁的加锁规则其实挺复杂的，在某些特定场景下它可能会"退化"成单纯的记录锁或间隙锁。我之前也写过一篇专门讲加锁规则的文章，想深入了解的同学可以看看：MySQL 是怎么加锁的？ 这里有个非常重要提醒：如果你的 update 语句的 where 条件没有用到索引列，MySQL就不得不做全表扫描。在扫描过程中，它不仅会给每一行记录都加上行锁，还会给记录两边的空隙都加上间隙锁。这相当于把整张表都锁了！直到事务结束这些锁才会被释放。所以，在线上系统千万别执行没有带索引条件的 update 语句，否则可能导致业务大面积停顿。我有个读者就因为这么干了，结果被老板狠狠地"教育"了一番，详情可以看这篇：update 没加索引会锁全表？ 好了，让我们回到前面那个死锁的例子。 当事务A执行这条语句时： 1select id from t_order where order_no = 1007 for update; 我们可以通过 select * from performance_schema.data_locks\G; 这条语句，查看事务执行SQL过程中具体加了哪些锁。 从上图可以看到，事务A主要加了两种锁： 表锁：一个X类型的意向锁（IX锁，表示事务准备在表里的某些行上加X锁）。 行锁：一个X类型的next-key锁。 我们重点关注行锁。图中 LOCK_TYPE 的 RECORD 表示这是一个行级锁（不是特指记录锁）。具体是next-key锁、间隙锁还是记录锁，需要看 LOCK_MODE： X：表示X型的next-key锁。 X, REC_NOT_GAP：表示X型的记录锁。 X, GAP：表示X型的间隙锁。 因此，此时事务A在 order_no 这个二级索引（INDEX_NAME : index_order）上加的是X型的next-key锁，锁定的范围是 (1006, +∞]。这里的1006是执行这条查询时，t_order 表中 order_no 列上小于1007且最接近1007的值（如果表里有小于1007的，就是那个最大的；如果没有，可能会是更小的一个范围起点）。由于1006是当时表里最大的订单号，所以 (1006, +∞] 锁住了从1006订单号之后的所有可能间隙，一直到表尾。 next-key 锁的范围 (1006, +∞]，是怎么确定的？ 根据我的经验，如果 LOCK_MODE 显示是next-key锁或者间隙锁，那么 LOCK_DATA 通常表示这个锁范围的最右边的那个值。在事务A的例子里，LOCK_DATA 是 supremum pseudo-record，这代表的是正无穷大（+∞）。而锁范围的最左边的值，则是 t_order 表中，在 order_no 索引上，小于我们查询值（1007）的那个最大值，也就是1006。因此，事务A的next-key锁锁定的就是 (1006, +∞] 这个开区间。 有的读者可能会问，我在MySQL 是怎么加锁的？这篇文章里讲到，当在非唯一索引上进行等值查询，并且查询的记录不存在时，next-key lock会退化成间隙锁。那为什么上面事务A的next-key lock没有退化呢？这里的关键在于查询的值（1007）与索引中已存在的值的相对位置。如果表中 order_no 索引的最大值是1006（如此案例），然后我们查询 order_no = 1007（一个不存在且大于所有现有值的记录），此时加的是next-key lock，范围是 (1006, +∞]，它不会退化。但如果表中 order_no 索引的最大值是1010，我们查询 order_no = 1007（一个不存在但在现有值之间的记录），此时next-key lock会锁定 (1006, 1010] 这个区间（假设1006是小于1007的最大值），然后它会退化成一个间隙锁，锁住 (1006, 1010) 这个间隙。如下图所示： 当事务B想要在事务A的next-key锁范围 (1006, +∞] 里插入订单号为1008的记录时，它就会被卡住： 1Insert into t_order (order_no, create_date) values (1008, now()); -- 事务B的操作 这是因为，当一个事务（比如事务B）尝试向一个间隙中插入数据时，它需要先获得一种叫做插入意向锁（Insert Intention Lock）的"许可"。 关键点来了： 插入意向锁 与 (其他事务持有的)间隙锁 是冲突的：如果事务A的next-key锁（它包含了一个间隙锁）已经锁住了事务B想插入的那个"缝隙"，那么事务B就必须等待事务A把这个间隙锁放掉，才能拿到自己的插入意向锁。 间隙锁 与 (其他事务持有的)间隙锁 是兼容的：这就是为什么一开始两个事务都能成功执行 select ... for update 语句。它们各自获取的next-key锁虽然都覆盖了 (1006, +∞] 这个范围，但它们所包含的间隙锁部分并不会互相"打架"。多个事务可以同时拥有覆盖相同间隙的间隙锁。 所以，死锁的剧本是这样的： 事务A执行 select ... for update where order_no = 1007;，成功获得了覆盖 (1006, +∞] 范围的next-key锁（这个锁里包含了间隙锁成分）。 事务B执行 select ... for update where order_no = 1008;，也成功获得了覆盖 (1006, +∞] 范围的next-key锁（同样包含间隙锁，并且与事务A的间隙锁兼容）。 现在，事务A想插入订单1007。它需要获取插入意向锁。但这个位置（或者说这个意图）与事务B持有的 (1006, +∞] 间隙锁冲突了，所以事务A开始等待事务B释放锁。 同时，事务B想插入订单1008。它也需要获取插入意向锁。同样，这个意图...