学习

MySQL：锁（三）

MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读吗？ MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读问题。 场景： select * from t_user where age >20 for update; 同时B事务执行: delete from t_user where id

MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读吗？

MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读问题。

场景：

select * from t_user where age >20 for update;

同时B事务执行:

delete from t_user where id = 2;

B 事务的删除语句会陷入了等待状态

加锁分析：

Untitled.png

  • 表锁（LOCK_TYPE: TABLE）：X 类型的意向锁；
  • 行锁（LOCK_TYPE: RECORD）：X 类型的 next-key 锁；

这里我们重点关注「行锁」，图中 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思：

  • 如果 LOCK_MODE 为 X，说明是 next-key 锁；
  • 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
  • 如果 LOCK_MODE 为 X, GAP，说明是间隙锁；

此时事务 A 在主键索引（INDEX_NAME : PRIMARY）上加了 10 个 next-key 锁，如下：

  • X 型的 next-key 锁，范围：(-∞, 1]
  • X 型的 next-key 锁，范围：(1, 2]
  • X 型的 next-key 锁，范围：(2, 3]
  • X 型的 next-key 锁，范围：(3, 4]
  • X 型的 next-key 锁，范围：(4, 5]
  • X 型的 next-key 锁，范围：(5, 6]
  • X 型的 next-key 锁，范围：(6, 7]
  • X 型的 next-key 锁，范围：(7, 8]
  • X 型的 next-key 锁，范围：(8, 9]
  • X 型的 next-key 锁，范围：(9, +∞]

相当于把整个表给锁住了，其他事务在对该表进行增、删、改操作的时候都会被阻塞。因为事务 A 的这条查询语句是全表扫描，锁是在遍历索引的时候加上的，并不是针对输出的结果加锁。在线上在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了

接下来，对 age 字段建立索引再查

主键索引会加如下的锁：

  • X 型的记录锁，锁住 id = 2 的记录；
  • X 型的记录锁，锁住 id = 3 的记录；
  • X 型的记录锁，锁住 id = 5 的记录；
  • X 型的记录锁，锁住 id = 6 的记录；
  • X 型的记录锁，锁住 id = 7 的记录；
  • X 型的记录锁，锁住 id = 8 的记录；

Untitled.png

  • X 型的 next-key lock，锁住 age 范围 (19, 21] 的记录；
  • X 型的 next-key lock，锁住 age 范围 (21, 21] 的记录；
  • X 型的 next-key lock，锁住 age 范围 (21, 23] 的记录；
  • X 型的 next-key lock，锁住 age 范围 (23, 23] 的记录；
  • X 型的 next-key lock，锁住 age 范围 (23, 39] 的记录；
  • X 型的 next-key lock，锁住 age 范围 (39, 43] 的记录；
  • X 型的 next-key lock，锁住 age 范围 (43, +∞] 的记录；

对 age 字段建立了索引后，查询语句是索引查询，并不会全表扫描，因此不会把整张表给锁住。

事务 A 加上锁后，事务 B、C、D、E 在执行以下语句都会被阻塞。

死锁

场景：

CREATE 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 ;

Untitled.png

假设这时有两事务，一个事务要插入订单 1007 ，另外一个事务要插入订单 1008，因为需要对订单做幂等性校验，所以两个事务先要查询该订单是否存在，不存在才插入记录，过程如下：

Untitled.png

可以看到，两个事务都陷入了等待状态（前提没有打开死锁检测），也就是发生了死锁，因为都在相互等待对方释放锁。

这里在查询记录是否存在的时候，使用了 select ... for update 语句，目的为了防止事务执行的过程中，有其他事务插入了记录，而出现幻读的问题。

如果没有使用 select ... for update 语句，而使用了单纯的 select 语句，如果是两个订单号一样的请求同时进来，就会出现两个重复的订单，有可能出现幻读，如下图：

Untitled.png

可重复读隔离级别下，是存在幻读的问题。

Innodb 引擎为了解决「可重复读」隔离级别下的幻读问题，就引出了 next-key 锁，它是记录锁和间隙锁的组合。

  • Record Lock，记录锁，锁的是记录本身；
  • Gap Lock，间隙锁，锁的就是两个值之间的空隙，以防止其他事务在这个空隙间插入新的数据，从而避免幻读现象。

普通的 select 语句是不会对记录加锁的，因为它是通过 MVCC 的机制实现的快照读，如果要在查询时对记录加行锁，可以使用下面这两个方式：

begin;
//对读取的记录加共享锁
select ... lock in share mode;
commit; //锁释放

begin;
//对读取的记录加排他锁
select ... for update;
commit; //锁释放

行锁的释放时机是在事务提交（commit）后，锁就会被释放，并不是一条语句执行完就释放行锁。

回到死锁：

select id from t_order where order_no = 1007 for update; 这句话执行的时候

共加了两个锁，分别是：

  • 表锁：X 类型的意向锁；
  • 行锁：X 类型的间隙锁；

这里我们重点关注行锁，图中 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思，通过 LOCK_MODE 可以确认是 next-key 锁，还是间隙锁，还是记录锁：

  • 如果 LOCK_MODE 为 X，说明是 X 型的 next-key 锁；
  • 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是 X 型的记录锁；
  • 如果 LOCK_MODE 为 X, GAP，说明是 X 型的间隙锁；

因此，此时事务 A 在二级索引（INDEX_NAME : index_order）上加的是 X 型的 next-key 锁，锁范围是**(1006, +∞]**。

next-key 锁的范围 (1006, +∞]，是怎么确定的？

根据我的经验，如果 LOCK_MODE 是 next-key 锁或者间隙锁，那么 LOCK_DATA 就表示锁的范围最右值，此次的事务 A 的 LOCK_DATA 是 supremum pseudo-record，表示的是 +∞。然后锁范围的最左值是 t_order 表中最后一个记录的 index_order 的值，也就是 1006。因此，next-key 锁的范围 (1006, +∞]。

当事务 B 往事务 A next-key 锁的范围 (1006, +∞] 里插入 id = 1008 的记录就会被锁住：

Insert into t_order (order_no, create_date) values (1008, now());

因为当我们执行以下插入语句时，会在插入间隙上获取插入意向锁，而插入意向锁与间隙锁是冲突的，所以当其它事务持有该间隙的间隙锁时，需要等待其它事务释放间隙锁之后，才能获取到插入意向锁。而间隙锁与间隙锁之间是兼容的，所以所以两个事务中 select ... for update 语句并不会相互影响。

案例中的事务 A 和事务 B 在执行完后 select ... for update 语句后都持有范围为(1006,+∞]的next-key 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，导致死锁。（同一个事务插入意向锁和间隙锁是兼容的）

插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁。

这段话表明尽管插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。

另外，补充一点，插入意向锁的生成时机：

  • 每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），现象就是 Insert 语句会被阻塞。

为什么间隙锁可以共存？

间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，共享和排他的间隙锁是没有区别的，他们相互不冲突，且功能相同，即两个事务可以同时持有包含共同间隙的间隙锁。

这里的共同间隙包括两种场景：

  • 其一是两个间隙锁的间隙区间完全一样；
  • 其二是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集。

但是有一点要注意，next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。

虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系。X 型的记录锁与 X 型的记录锁是冲突的，比如一个事务执行了 select … where id = 1 for update，后一个事务在执行这条语句的时候，就会被阻塞的。

但是还要注意！对于这种范围为 (1006, +∞] 的 next-key lock，两个事务是可以同时持有的，不会冲突。因为 +∞ 并不是一个真实的记录，自然就不需要考虑 X 型与 S 型关系。

Insert 语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。

当事务需要加锁的时，如果这个锁不可能发生冲突，InnoDB会跳过加锁环节，这种机制称为隐式锁。隐式锁是 InnoDB 实现的一种延迟加锁机制，其特点是只有在可能发生冲突时才加锁，从而减少了锁的数量，提高了系统整体性能。

隐式锁就是在 Insert 过程中不加锁，只有在特殊情况下，才会将隐式锁转换为显示锁，这里我们列举两个场景。

  • 如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的；
  • 如果 Insert 的记录和已有记录存在唯一键冲突，此时也不能插入记录；

避免死锁

  • 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

    当发生超时后，就出现下面这个提示：

c296c1889f0101d335699311b4ef20a8.png

  • 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

    当检测到死锁后，就会出现下面这个提示：

f380ef357d065498d8d54ad07f145e09.png

上面这个两种策略是「当有死锁发生时」的避免方式。

我们可以回归业务的角度来预防死锁，对订单做幂等性校验的目的是为了保证不会出现重复的订单，那我们可以直接将 order_no 字段设置为唯一索引列，利用它的唯一性来保证订单表不会出现重复的订单，不过有一点不好的地方就是在我们插入一个已经存在的订单记录时就会抛出异常。

Comment