MySQL DELETE 删除语句加锁分析

文章目录

[隐藏]

• 一. 前言
• 二. SQL的加锁分析
  • 2.1 删除SQL加锁分析
• 三. 总结

一. 前言

在MySQL的使用过程中，对SQL加锁的类型经常感到疑惑，这让死锁分析也变得举步维艰。因此需要将MySQL的各种SQL在各个隔离级别下加的锁进行分析，以免再次分析的时候还感到疑惑，也方便用于查询。

本次分析对SQL的删除语句进行分析，主要从以下几种情况进行分析：

1. 非唯一索引删除一条存在的记录
2. 唯一索引删除一条存在的记录
3. 主键删除一条存在的记录
4. 非唯一索引删除一条不存在记录
5. 唯一索引删除一条不存在的记录
6. 主键删除一条不存在的记录
7. 不同的SQL根据主键删除2条记录
8. 非唯一索引删除一条已经标记删除的记录
9. 唯一索引删除一条已经标记删除的记录

在使用之前需要打开innodb lock monitor，这样在查看 engine innodb status 的时候可以更加清晰的查到到锁的情况

set GLOBAL innodb_status_output_locks=ON;  

二. SQL的加锁分析

相关表结构

• 普通索引表结构

CREATE TABLE `t` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `c1` int(11) NOT NULL DEFAULT '0',    `c2` int(11) NOT NULL DEFAULT '0',    PRIMARY KEY (`id`),    KEY `idx_c1` (`c1`)  ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;  

• 唯一索引表结构

CREATE TABLE `tu` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `c1` int(11) NOT NULL DEFAULT '0',    `c2` int(11) NOT NULL DEFAULT '0',    PRIMARY KEY (`id`),    UNIQUE KEY `uniq_c1` (`c1`)  ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4  

• 表的记录，唯一索引和普通索引的表结构均一样
• 测试的事务隔离级别为RR。

+----+----+----+  | id | c1 | c2 |  +----+----+----+  |  2 |  3 |  2 |  |  3 |  5 |  3 |  |  4 |  8 |  4 |  |  5 | 11 |  5 |  |  9 |  9 | 20 |  | 10 |  7 | 10 |  | 11 | 20 | 15 |  | 12 | 30 | 17 |  | 13 | 25 | 16 |  | 14 | 27 | 10 |  +----+----+----+  

2.1 删除SQL加锁分析

根据非唯一索引删除一条存在记录

delete from t where c1=5;  Query OK, 1 rows affected (0.00 sec)      ---TRANSACTION 146749, ACTIVE 9 sec  4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1  MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 104 localhost msandbox cleaning up  TABLE LOCK table `test`.`t` trx id 146749 lock mode IX  RECORD LOCKS space id 53 page no 5 n bits 72 index `idx_c1` of table `test`.`t` trx id 146749 lock_mode X  RECORD LOCKS space id 53 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 146749 lock_mode X locks rec but not gap  RECORD LOCKS space id 53 page no 5 n bits 72 index `idx_c1` of table `test`.`t` trx id 146749 lock_mode X locks gap before rec  

根据非唯一索引进行删除的时候，锁情况为：

4 lock struct(s)：4种锁结构，分别为IX，idx_c1和主键的行锁，还有idx_c1的gap锁
3 row lock(s)：有3个行锁，除去IX的都是算在row lock里面

根据唯一索引删除一条存在记录

delete from tu where c1=5;  Query OK, 1 rows affected (0.00 sec)      ---TRANSACTION 146751, ACTIVE 2 sec  3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1  MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 134 localhost msandbox cleaning up  TABLE LOCK table `test`.`tu` trx id 146751 lock mode IX  RECORD LOCKS space id 45 page no 5 n bits 72 index `uniq_c1` of table `test`.`tu` trx id 146751 lock_mode X locks rec but not gap  RECORD LOCKS space id 45 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146751 lock_mode X locks rec but not gap  

根据唯一索引进行删除的时候，锁情况为：

3 lock struct(s)：3种锁结构，分别为IX，idx_c1和主键的行锁，没有gap锁
2 row lock(s)：有2个行锁，除去IX的都是算在row lock里面，没有gap，因此为2个

根据主键删除一条存在记录

delete from tu where 三. 总结">三. 总结

在非唯一索引的情况下，删除一条存在的记录是有gap锁，锁住记录本身和记录之前的gap
在唯一索引和主键的情况下删除一条存在的记录，因为都是唯一值，进行删除的时候，是不会有gap存在
非唯一索引，唯一索引和主键在删除一条不存在的记录，均会在这个区间加gap锁
通过非唯一索引和唯一索引去删除一条标记为删除的记录的时候，都会请求该记录的行锁，同时锁住记录之前的gap
RC 情况下是没有gap锁的，除了遇到唯一键冲突的情况，如插入唯一键冲突。

原文出处：fordba -> http://fordba.com/lock-analyse-of-delete.html