投稿
  1. 极客之音首页
  2. 其他分类
  3. 微信精选

MySQL delete 语句加锁分析

小半 • • 微信精选 • 阅读 467

引言

本文测试并基于源码分析 delete 语句在以下四种场景下的加锁流程：

  • 根据非索引字段删除
  • 根据非唯一二级索引删除
  • 根据唯一二级索引删除
  • 根据主键索引删除

由于个人能力有限，以下内容可能有误，欢迎批评指正。

在此之前建议阅读之前的两篇文章：

准备工作

测试数据

数据库版本：5.7.24

事务隔离级别：RR

测试数据

mysql> show create table t_lock G
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `t_lock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT '0',
  `b` int(11) DEFAULT '0',
  `c` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into t_lock(id, a, b, c)  values(1, 1, 1, 1),(5, 5, 5, 5),(9, 9, 9, 9);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

其中：

  • 测试表中有三个索引，包括主键索引、二级唯一索引、二级非唯一索引。

查看数据

mysql> select * from t_lock;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |    1 |    1 |    1 |
|  5 |    5 |    5 |    5 |
|  9 |    9 |    9 |    9 |
+----+------+------+------+
3 rows in set (0.00 sec)

场景

下面对比测试 delete 语句在以下四种场景下加锁的差异。

  • 根据非索引字段删除
  • 根据非唯一二级索引删除
  • 根据唯一二级索引删除
  • 根据主键索引删除

其中调试过程中给lock_rec_lock函数设置断点。

注意与 update 语句不同，delete 语句既改主键索引，也改二级索引，这里说的改对应删除操作。

根据非索引字段删除

delete from t_lock where c=5;

测试

事务信息

---TRANSACTION 136333, ACTIVE 9 sec
2 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 56, OS thread handle 139726191232768, query id 16900 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_lock` trx id 136333 lock mode IX
RECORD LOCKS space id 504 page no 3 n bits 80 index PRIMARY of table `test_zk`.`t_lock` trx id 136333 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000020b32; asc      2;;
 2: len 7; hex af000000360110; asc     6  ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000001; asc     ;;
 5: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000002148d; asc       ;;
 2: len 7; hex 660000002b2d68; asc f   +-h;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000005; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 6; hex 000000020b32; asc      2;;
 2: len 7; hex af00000036012a; asc     6 *;;
 3: len 4; hex 80000009; asc     ;;
 4: len 4; hex 80000009; asc     ;;
 5: len 4; hex 80000009; asc     ;;

其中：

  • 给主键索引加锁；
  • 每行记录加锁，表明查询字段没有索引导致锁表；
  • 被删除的记录 info bits 32，表示已被标记删除。


堆栈信息

给第一行数据的主键索引加锁。

MySQL delete 语句加锁分析

其中：

  • heap_no=2，表明是给第一行加锁；

  • type_mode=3，表明加锁类型为 next-key lock，与事务信息中看到的锁类型相同；

具体加锁类型 type_mode 的计算规则将单独一篇文章讲解。

  • 调用加锁的函数是row_search_mvcc，表明是数据查询过程中加锁。

然后给第二行数据的主键索引加锁，同样是数据查询过程中加锁。

MySQL delete 语句加锁分析

其中：

  • heap_no=3，表明是给第二行加锁；
  • 调用ha_innobase::rnd_next接口获取下一行数据。

由于第二行数据满足查询条件，因此在数据删除过程中继续加锁。

MySQL delete 语句加锁分析

其中：

  • impl=true, mode=1027，表明删除期间加 record lock，注意是隐式锁；
  • 调用加锁的函数是btr_cur_del_mark_set_sec_rec，表明对应二级索引标记删除；
  • 依次删除二级索引 uk_a 与 idx_b。

注意这里没有发现删除主键索引的加锁操作。

第二行数据处理完成后，依次处理第三行数据（ heap_no=4）与最大纪录 supremum（ heap_no=1），加锁规则与第一行相同，因此这里不再展示。

分析

删除主键索引

为分析删除主键索引时的加锁规则，给主键索引标记删除函数row_upd_del_mark_clust_rec设置断点。

测试显示删除主键索引时加隐式锁，但是没有调用lock_rec_lock函数。

MySQL delete 语句加锁分析

row_upd_clust_step函数对于 delete 语句，调用row_upd_del_mark_clust_rec函数。

 // delete 语句
 if (node->is_delete) {
  // 标记删除，内部调用 lock_clust_rec_modify_check_and_lock 函数
  err = row_upd_del_mark_clust_rec(
   flags, node, index, offsets, thr, referenced, &mtr);

  if (err == DB_SUCCESS) {
      // 对于 delete 语句，需要删除全部二级索引
   node->state = UPD_NODE_UPDATE_ALL_SEC;
   node->index = dict_table_get_next_index(index);
  }

  goto exit_func;
 }

其中：

  • 对于 delete 语句，设置node->state = UPD_NODE_UPDATE_ALL_SEC，表示需要删除全部二级索引。

最终调用lock_clust_rec_modify_check_and_lock 函数加锁，入参中flags = BTR_NO_LOCKING_FLAG，表示使用隐式锁。

 err = lock_clust_rec_modify_check_and_lock(BTR_NO_LOCKING_FLAG, block,
         rec, index, offsets, thr); 

lock_clust_rec_modify_check_and_lock函数中判断是否需要加锁。

 // flags = BTR_NO_LOCKING_FLAG 表示使用隐式锁，因此不加锁，直接返回
 if (flags & BTR_NO_LOCKING_FLAG) {

  return(DB_SUCCESS);
 }

 /* If a transaction has no explicit x-lock set on the record, set one
 for it */

 // 把主键索引记录上的隐式锁转换为显式锁
 lock_rec_convert_impl_to_expl(block, rec, index, offsets);

 // 加锁，record lock
 err = lock_rec_lock(TRUE, LOCK_X | LOCK_REC_NOT_GAP,
       block, heap_no, index, thr);

 if (err == DB_SUCCESS_LOCKED_REC) {
  err = DB_SUCCESS;
 }

其中：

  • 如果使用隐式锁，直接返回加锁成功，实际上查询阶段已加锁，主键更新与删除操作中加锁规则相同；
  • 如果使用显示锁，首先判断主键索引记录上是否已有隐式锁，如果有，将其提升为显示锁，然后给自身加锁，类型是 record lock。

具体隐式锁转换为显示锁的实现将单独一篇文章讲解。


从这里看主键索引加隐式锁时直接返回成功，但是可以发现存在隐式锁转换成显示锁的逻辑，因此隐式锁必然对应其他实现。

具体是在btr_cur_del_mark_set_clust_rec函数中加锁后实现隐式锁。

 // 加锁
 err = lock_clust_rec_modify_check_and_lock(BTR_NO_LOCKING_FLAG, block,
         rec, index, offsets, thr);

 // 更新隐藏字段，包括事务id和回滚指针
 row_upd_rec_sys_fields(rec, page_zip, index, offsets, trx, roll_ptr);

其中：

  • 对于 Compact 行格式，主键索引隐式锁的实现具体是更新行记录中的隐藏字段事务 IDDB_TRX_ID，保存最后修改行记录的事务 ID；
  • 更新回滚指针DB_ROLL_PTR用于构建指定行数据的版本链，从而找到历史版本。

注意由于隐式锁针对的是被修改的 B+ 树记录，因此仅 record lock 支持隐式锁，gap lock 与 next-key lock 不支持隐式锁。

主键索引删除完成后，遍历删除所有的二级索引。

删除二级索引

前文堆栈中显示删除二级索引时加锁函数入参lock_rec_lock(impl=true, mode=1027)，表明同样使用隐式锁。

lock_rec_lock函数执行时判断是否是隐式锁，如果是，直接返回加锁成功，否则创建锁结构。

MySQL delete 语句加锁分析

lock_rec_lock函数入参中impl表示该操作是否使用隐式锁。

lock_rec_lock(
/*==========*/
 bool   impl, /*!< in: if true, no lock is set
     if no wait is necessary: we
     assume that the caller will
     set an implicit lock */
 ulint   mode, /*!< in: lock mode: LOCK_X or
     LOCK_S possibly ORed to either
     LOCK_GAP or LOCK_REC_NOT_GAP */
 const buf_block_t* block, /*!< in: buffer block containing
     the record */
 ulint   heap_no,/*!< in: heap number of record */
 dict_index_t*  index, /*!< in: index of record */
 que_thr_t*  thr) /*!< in: query thread */

注释显示如果没有锁冲突，impl=true时使用隐式锁。

因此，lock_rec_lock函数中快速加锁与慢速加锁时都会在没有锁冲突的前提下判断是否使用隐式锁，如果使用，直接返回加锁成功，区别在于返回值 err 不同。


由于二级索引不支持原地更新，因此二级索引的 update 与 delete 语句执行过程中都存在 delete 操作，二级索引更新函数lock_sec_rec_modify_check_and_lock中同时支持 update 与 delete 语句，二级索引的隐式锁也在其中实现，而没有在二级索引标记删除函数btr_cur_del_mark_set_sec_rec函中实现。

lock_sec_rec_modify_check_and_lock函数中隐式锁实现的代码如下所示。

 // 加锁
 err = lock_rec_lock(TRUE, LOCK_X | LOCK_REC_NOT_GAP,
       block, heap_no, index, thr);

#endif /* UNIV_DEBUG */
 
 // 判断 lock_rec_lock 函数的返回值
 // err 的其他取值包括：DB_DEADLOCK、DB_LOCK_WAIT
 if (err == DB_SUCCESS || err == DB_SUCCESS_LOCKED_REC) {
  /* Update the page max trx id field */
  /* It might not be necessary to do this if
  err == DB_SUCCESS (no new lock created),
  but it should not cost too much performance. */
  // 二级索引更新数据页的 PAGE_MAX_TRX_ID
  page_update_max_trx_id(block,
           buf_block_get_page_zip(block),
           thr_get_trx(thr)->id, mtr);
  err = DB_SUCCESS;
 }

其中：

  • 在加锁后判断返回值，如果加锁成功，包括显式锁与隐式锁，更新二级索引数据页的 PAGE_MAX_TRX_ID，保存修改行记录的最大事务 ID；
  • 对比主键索引与二级索引的隐式锁实现，前者是在写入 undo log 过程中实现，后者是在加锁中实现，原因是二级索引没有隐藏列，因此不记录 undo log。

加锁类型

堆栈显示主键索引与二级索引标记删除过程中的加锁类型都是 record lock，下面结合源码进行分析。

更新主键索引的函数lock_clust_rec_modify_check_and_lock中调用lock_rec_lock函数加锁时指定加锁类型。

 err = lock_rec_lock(TRUE, LOCK_X | LOCK_REC_NOT_GAP,
       block, heap_no, index, thr);

其中：

  • 主键索引的加锁类型固定为 X 型 record lock。

同样，更新二级索引的函数lock_sec_rec_modify_check_and_lock中调用lock_rec_lock函数加锁时指定加锁类型。

 err = lock_rec_lock(TRUE, LOCK_X | LOCK_REC_NOT_GAP,
       block, heap_no, index, thr);

其中：

  • 二级索引的加锁类型固定为 X 型 record lock，包括二级唯一索引。

而查询二级索引的函数lock_sec_rec_read_check_and_lock中未指定加锁类型，mode 与 gap_mode 都不确定，由外部传入。

 err = lock_rec_lock(FALSE, mode | gap_mode,
       block, heap_no, index, thr);

因此：

  • 主键索引与二级索引标记删除过程中的加锁类型都是 record lock；
  • 如果没有锁冲突，使用隐式锁，否则使用显式锁。在创建显式锁之前会先将存在的隐式锁转换成显式锁，因此删除操作加锁时将发生锁等待。

根据非唯一索引字段删除

delete from t_lock where b=5;

测试

事务信息

---TRANSACTION 136335, ACTIVE 3 sec
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 56, OS thread handle 139726191232768, query id 16906 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_lock` trx id 136335 lock mode IX
RECORD LOCKS space id 504 page no 6 n bits 80 index idx_b of table `test_zk`.`t_lock` trx id 136335 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 504 page no 3 n bits 80 index PRIMARY of table `test_zk`.`t_lock` trx id 136335 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000002148f; asc       ;;
 2: len 7; hex 67000000c92968; asc g    )h;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 504 page no 6 n bits 80 index idx_b of table `test_zk`.`t_lock` trx id 136335 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000009; asc     ;;

其中：

  • 最后一个锁的类型是间隙锁，锁定的是满足条件的下一条记录。


堆栈信息

不同于全表扫描，使用索引时直接给第二行数据的二级索引加锁，同样是数据查询过程中加锁。

MySQL delete 语句加锁分析

其中：

  • heap_no=3，表明是给第二行加锁；
  • 调用lock_sec_rec_read_check_and_lock函数给二级索引加锁。

然后给第二行数据的主键索引加锁。

MySQL delete 语句加锁分析

其中：

  • row_sel_get_clust_rec_for_mysql函数用于回表，期间给主键索引加锁，由row_search_mvcc函数第 5759 行发起调用。

获取到完整的行数据后，依次标记删除主键索引与所有的二级索引，加锁过程忽略。

最后给第三行数据的二级索引加锁。

MySQL delete 语句加锁分析

其中：

  • heap_no=4，表明是给第三行加锁；
  • mode=515，表明加锁类型是间隙锁；
  • 调用加锁的函数是sel_set_rec_lock，由row_search_mvcc函数第 5389 行发起调用；
  • 对比第二行数据的二级索引加锁，虽然同样是sel_set_rec_lock，但是由row_search_mvcc函数第 5512 行发起调用。

这里可以提出两个问题：

  • 为什么使用二级索引删除时需要回表？
  • 为什么第二行数据与第三行数据的加锁类型不同？

分析

回表

row_search_mvcc函数第 5759 行调用row_sel_get_clust_rec_for_mysql函数进行回表查询。

对应代码如下所示。

 /* Get the clustered index record if needed, if we did not do the
 search using the clustered index. */
 
 // 如果定位的是二级索引记录并有回表需求则回表获取完整的聚簇索引记录
 // 如果row_search_mvcc读取的是二级索引记录，则还需进行回表，找到相应的聚簇索引记录后需对该聚簇索引记录加一个正经记录锁
 // 比如根据二级索引删除记录时，还需要回表给主键索引加锁
 if (index != clust_index && prebuilt->need_to_access_clustered) {

// case ICP_MATCH:
 // goto requires_clust_rec;
requires_clust_rec:
  ut_ad(index != clust_index);
    
  /* It was a non-clustered index and we must fetch also the
  clustered index record */

  // row_sel_get_clust_rec_for_mysql便是用于回表的函数
  err = row_sel_get_clust_rec_for_mysql(prebuilt, index, rec,
            thr, &clust_rec,
            &offsets, &heap,
            need_vrow ? &vrow : NULL,
            &mtr);

其中：

  • 如果定位数据使用的是二级索引，但是需要回表时调用row_sel_get_clust_rec_for_mysql函数；
  • 判断是否需要回表依赖prebuilt->need_to_access_clustered字段，因此重点就是该字段的赋值；
  • 此外可以看到requires_clust_rec标签，用于 goto 语句，条件是row_search_idx_cond_check函数的返回值等于ICP_MATCH。


下面分析prebuilt->need_to_access_clustered字段的赋值过程。

m_prebuilt变量是row_prebuilt_t结构体指针类型，用于保存数据操作过程中的上下文信息，比如表、索引、事务、游标。

build_template函数用于初始化m_prebuilt变量，包括need_to_access_clustered字段。

MySQL delete 语句加锁分析

其中：

  • build_template函数调用时入参whole_row为 false，表示ROW_MYSQL_REC_FIELDS；
  • 如果加锁类型是 X 型锁，需要将whole_row变量修改为 true，表示ROW_MYSQL_WHOLE_ROW；
  • X 型锁对应的操作包括 SELECT … FOR UPDATE、DELETE、UPDATE 语句；
  • 因此对于二级索引，即使可以使用覆盖索引，只要加锁类型是 X 型锁，都需要回表给主键索引加锁。

prebuilt->need_to_access_clustered字段的取值与whole_row变量有关。

MySQL delete 语句加锁分析

其中：

  • 由于whole_row等于 true，因此index等于主键索引；
  • 由于index等于主键索引，因此prebuilt->need_to_access_clustered等于 1，表示需要回表。

因此根据二级索引加锁时需要给主键索引加锁，而根据主键索引加锁时不需要给二级索引加锁。

原因是修改二级索引时需要锁定主键索引，防止其他线程修改主键索引，从而可以保证其他线程不会修改这条记录。

加锁类型判断

查询过程中第二行数据与第三行数据的加锁类型分别是 next-key lock 与 gap lock，下面分析原因。

MySQL update 后 insert 导致死锁 文章中介绍了丁奇大佬在《MySQL 实战 45 讲》中介绍的加锁规则。

加锁规则可以简单总结为如下两个原则、两个优化、一个bug。

1）两个原则

  • 原则 1：加锁的基本单位是 next-key lock，next-key lock 是左开右闭区间；
  • 原则 2：查找过程中访问到的对象才会加锁。

2）两个优化

  • 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为记录锁；
  • 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

3）一个bug

  • 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。注意是范围查询，不包括等值查询。

查询过程中具体加锁类型的判断需要分析row_search_mvcc函数，本文不进行介绍，后续再学习。

这里直接用结论，其中：

  • 第二行数据的加锁类型是 next-key lock，原因是原则 1；
  • 第三行数据的加锁类型是 gap lock，原因是优化 2。


剩下的两种场景的加锁就很简单了，原因是都是根据唯一键删除，包括二级唯一索引与主键索引。

根据唯一索引字段删除

delete from t_lock where a=5;

测试

事务信息

---TRANSACTION 136341, ACTIVE 8 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 56, OS thread handle 139726191232768, query id 16910 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_lock` trx id 136341 lock mode IX
RECORD LOCKS space id 504 page no 4 n bits 80 index uk_a of table `test_zk`.`t_lock` trx id 136341 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 504 page no 3 n bits 80 index PRIMARY of table `test_zk`.`t_lock` trx id 136341 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000021495; asc       ;;
 2: len 7; hex 6a000001cc07dc; asc j      ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000005; asc     ;;

其中：

  • 对比根据非唯一索引字段删除的场景，二级唯一索引加锁类型是 record lock，而不是 next-key lock，满足加锁规则中优化 1；
  • 对比根据非唯一索引字段删除的场景，二级唯一索引没有给第三行数据加锁。


堆栈信息

给第二行数据的二级索引加锁。

MySQL delete 语句加锁分析


然后回表给第二行数据的主键索引加锁。

MySQL delete 语句加锁分析

不同于根据非唯一索引字段删除的场景，在删除索引后，不需要给之后的记录加锁，原因是满足唯一键约束。

根据主键索引字段删除

delete from t_lock where id=5;

测试

事务信息

---TRANSACTION 136343, ACTIVE 8 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 56, OS thread handle 139726191232768, query id 16914 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_lock` trx id 136343 lock mode IX
RECORD LOCKS space id 504 page no 3 n bits 80 index PRIMARY of table `test_zk`.`t_lock` trx id 136343 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000021497; asc       ;;
 2: len 7; hex 6b000000c4269f; asc k    & ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000005; asc     ;;

其中：

  • 对比根据唯一索引字段删除删除的场景，主键索引删除时没有给二级索引加锁，原因是如果其他线程修改二级索引，必然也需要修改主键索引。


堆栈信息

给第二行数据的主键索引加锁。

MySQL delete 语句加锁分析

同样在删除索引后，不需要给之后的记录加锁，原因是满足唯一键约束。

知识点

删除操作流程

MySQL delete 语句的标记删除与 purge 操作 文章中分析 delete 操作的主要流程见下图。

MySQL delete 语句加锁分析

其中：

  • delete 操作的删除是标记删除，原因是原始数据需要提供给 MVCC 使用；
  • delete 事务提交后生成 delete-marked record（index record）与 undo record，对应红框；
  • purge 线程先后清理 delete-marked record（index record）与 undo record；
  • delete 语句执行时先后标记删除主键索引与二级索引，undo purge 时先后物理删除二级索引与主键索引；
  • 主键索引的标记删除操作生成 undo log 时更新行记录的两个隐藏字段，包括 trx_id 与 roll_pointer，用于构建版本链。

加锁类型对比

由于丁奇大佬在《MySQL 实战 45 讲》中介绍的加锁规则太过经典，因此这里再抄一遍。

加锁规则可以简单总结为如下两个原则、两个优化、一个bug。

1）两个原则

  • 原则 1：加锁的基本单位是 next-key lock，next-key lock 是左开右闭区间；
  • 原则 2：查找过程中访问到的对象才会加锁。

2）两个优化

  • 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为记录锁；
  • 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

3）一个bug

  • 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。注意是范围查询，不包括等值查询。


根据加锁规则，对比分析不同删除场景下的加锁类型。

删除场景 加锁类型 原因
根据非索引字段 主键索引全表 next-key lock 原则 2
根据非唯一索引字段 二级索引 next-key lock + 主键 record lock + 二级索引 gap lock 原则 1、优化 2
根据唯一索引字段 二级索引 record lock + 主键 record lock 优化 1
根据主键索引字段 主键 record lock 优化 1

锁继承

其实到这里为止，delete 语句的加锁分析依然是不完整的，原因是 undo purge 过程中将发生锁继承。

具体是删除记录的下一条记录需要继承删除记录的锁定范围，并且其模式是 gap lock，由lock_update_delete 函数实现。


下面测试并分析锁继承。

参考文章 MySQL · 特性分析 · innodb 锁分裂继承与迁移，复现锁继承。

session 1 session 2
begin;
delete from t_lock where id=5;

begin;
select * from t_lock where id=5 for update;
blocking
commit;

non-blocking

其中：

  • 事务 1 提交前事务 2 锁等待，等待 id=5 的 record lock；
  • 事务 2 提交后事务 2 锁等待结束，查看事务锁信息，结果如下所示。
---TRANSACTION 39713, ACTIVE 38 sec
3 lock struct(s), heap size 1160, 1 row lock(s)
MySQL thread id 6, OS thread handle 139780989490944, query id 82 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_lock` trx id 39713 lock mode IX
RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test_zk`.`t_lock` trx id 39713 lock_mode X locks rec but not gap
RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test_zk`.`t_lock` trx id 39713 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 6; hex 00000000941d; asc       ;;
 2: len 7; hex ae000001190134; asc       4;;
 3: len 4; hex 80000009; asc     ;;
 4: len 4; hex 80000009; asc     ;;
 5: len 4; hex 80000009; asc     ;;

其中：

  • 事务 2 申请的是 id=5 的 record lock，变成了 id=9 的 gap lock，原因是数据被删除，锁迁移到下一条记录；
  • 事务 1 持锁类型是 id = 5 的 record lock，被删除后变成下一条记录上的 gap lock。

lock_update_delete 函数主体代码如下所示。

/*************************************************************//**
Updates the lock table when a record is removed. */
void
lock_update_delete(
/*===============*/
 const buf_block_t* block, /*!< in: buffer block containing rec */
 const rec_t*  rec) /*!< in: the record to be removed */
{
 ...
 if (page_is_comp(page)) {
  // 获取即将被删除的记录的编号
  heap_no = rec_get_heap_no_new(rec);
  // 获取即将被删除记录的下一条记录的编号
  next_heap_no = rec_get_heap_no_new(page
         + rec_get_next_offs(rec,
               TRUE));
 } else {
  ...
 }
  ...

 /* Let the next record inherit the locks from rec, in gap mode */
 // 把即将被删除记录上的锁转移到它的下一条记录上
 lock_rec_inherit_to_gap(block, block, next_heap_no, heap_no);

 /* Reset the lock bits on rec and release waiting transactions */
 // 释放并重置删除记录上等待锁的信息
 lock_rec_reset_and_release_wait(block, heap_no);
 ...
}

其中：

  • 调用 rec_get_heap_no_new() 获取即将被删除记录的下一条记录的编号；
  • 然后调用 lock_rec_inherit_to_gap() 将即将被删除记录上的锁转移到它的下一条记录上；
  • 最后释放并重置删除记录上等待锁的信息。


与锁继承相关的另一个概念是锁分裂，其中锁分裂针对 insert 语句，锁继承针对 delete 语句。

MySQL 从一个死锁案例到锁分裂 文章中通过分析现象 update + insert 存在的记录时新增 gap lock，发现插入操作会导致锁分裂。

相比之下，锁继承相对少见，常见于回滚操作。

比如 答读者问：唯一索引冲突，为什么主键的 supremum 记录会加 next-key 锁？ 文章中通过分析现象 insert 二级唯一键冲突时给主键 supremum 记录加锁，流程上由于主键索引插入成功，二级索引插入失败，错误处理时需要删除主键索引。

但是在删除刚插入的主键索引的记录之前，需要把该记录上的锁转移到它的下一条记录上，由于新插入的主键的下一条记录是 supremum，因此将被删除记录上的锁转移到 supremum 记录上，索引类型是 X 型 next-key lock。


因此，删除操作在提交以后依然有可能影响到后续的操作，其中：

  • 如果没有被 purge，存在 delete-marked record，比如会影响二级唯一键的唯一性检查；
  • 如果被 purge，将发生锁继承，导致锁范围扩大。

结论

delete 操作分两步完成，包括查找与删除，这两个过程中都可能加锁：

  • 查询过程中加锁由row_search_mvcc函数完成，其中使用二级索引查询时，需要回表给主键索引加锁；
  • 删除过程中如果没有锁冲突，使用隐式锁，包括删除主键索引与二级索引，否则使用显式锁，加锁类型是 X 型 record lock。


没有锁冲突时，删除主键索引与二级索引时都使用隐式锁，但是隐式锁的实现方式不同。

其中：

  • 主键索引，写入 undo log 时更新行记录中的隐藏字段事务 IDDB_TRX_ID，保存最后修改行记录的事务 ID；
  • 二级索引，更新二级索引数据页的 PAGE_MAX_TRX_ID，保存修改行记录的最大事务 ID。

实现方式不同的原因是二级索引没有隐藏列，因此不记录 undo log。

有锁冲突时，使用显式锁，注意加锁类型都是 X 型 record lock。


查询数据之前需要判断是否需要回表。

如果定位数据使用的是二级索引，只要加锁类型是 X 型锁，就需要回表给主键索引加锁，即使可以使用覆盖索引。

X 型锁对应的操作包括 SELECT … FOR UPDATE、DELETE、UPDATE 语句。

参考教程

https://juejin.cn/post/7028435335382040589
  • 《MySQL 实战 45 讲》为什么我只改一行的语句，锁这么多？
https://time.geekbang.org/column/article/75659
https://cloud.tencent.com/developer/article/1745809
  • MySQL · 引擎特性 · InnoDB 事务锁系统简介
http://mysql.taobao.org/monthly/2016/01/01/
  • MySQL · 特性分析 · innodb 锁分裂继承与迁移
http://mysql.taobao.org/monthly/2016/06/01/

原文始发于微信公众号（丹柿小院）：MySQL delete 语句加锁分析

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/221925.html

赞 (0)
小半的头像小半
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！