mysql delete锁_MySQL DELETE 删除语句加锁分析

MySQL DELETE 删除语句加锁分析

http://www.fordba.com/lock-analyse-of-delete.html

1. 前言

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

本次分析对SQL的删除语句进行分析，主要从以下几种情况进行分析：非唯一索引删除一条存在的记录

唯一索引删除一条存在的记录

主键删除一条存在的记录

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

唯一索引删除一条不存在的记录

主键删除一条不存在的记录

不同的SQL根据主键删除2条记录

非唯一索引删除一条已经标记删除的记录

唯一索引删除一条已经标记删除的记录

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

2. SQL的加锁分析

相关表结构普通索引表结构唯一索引表结构表的记录，唯一索引和普通索引的表结构均一样

测试的事务隔离级别为RR。

2.1 删除SQL加锁分析

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

根据非唯一索引进行删除的时候，锁情况为：4 lock struct(s)：4种锁结构，分别为IX，idx_c1和主键的行锁，还有idx_c1的gap锁

3 row lock(s)：有3个行锁，除去IX的都是算在row lock里面

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

根据唯一索引进行删除的时候，锁情况为：3 lock struct(s)：3种锁结构，分别为IX，idx_c1和主键的行锁，没有gap锁

2 row lock(s)：有2个行锁，除去IX的都是算在row lock里面，没有gap，因此为2个

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

根据主键进行删除的时候，锁情况为：2 lock struct(s)：2种锁结构，分别为IX和主键的行锁，没有gap锁

1 row lock(s)：有1个行锁，就主键记录上的行锁，没有gap，因此为1个

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

根据非唯一索引删除一条 不存在 记录，锁情况为：2 lock struct(s)：2种锁结构，分别为IX和X类型的gap锁

1 row lock(s)：有1个行锁，为非唯一索引的gap锁

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

根据唯一索引删除一条 不存在 记录,发现和非唯一索引一样，锁情况为：2 lock struct(s)：2种锁结构，分别为IX和X类型的gap锁

1 row lock(s)：有1个行锁，为唯一索引的gap锁

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

根据主键删除一条 不存在 记录,发现和非唯一索引一样，锁情况为：2 lock struct(s)：2种锁结构，分别为IX和X类型的gap锁

1 row lock(s)：有1个行锁，为主键上的gap锁

根据主键删除两条存在的记录

根据主键删除两条的时候，使用in的锁情况为：2 lock struct(s)：2种锁结构，分别为IX和i主键的行锁，没有gap锁

2 row lock(s)：有2个行锁，就主键记录上的行锁，没有gap，因此为2个

根据主键删除两条的时候，使用>,=,<=,比较符号的锁情况为：无论如何，匹配到2条记录，因此必须会有2 row lock(s)

如果只有>,

碰到 >= 的时候，判断 >= 的值是否存在，如果存在，则锁定该记录。所以除了IX,X锁，还有行锁，因此存在的时候为3 lock struct(s), 3 row lock(s)。如果不存在，和第二种是一样的，为2 lock struct(s) ，3 row lock(s) 。

非唯一索引删除一条已经标记删除的记录Sess1Sess2Sess3

begin;

delete from t where c1=8;

begin;

delete from t where c1=8;

@1 show engine innodb status

commit;

@2 show engine innodb status

非唯一索引删除一条已经标记删除的记录的锁情况为：加锁等待时: 2 lock struct(s) ，持有IX锁，等待记录上的X锁

加锁成功时：3 lock struct(s)，持有IX,行锁，和gap锁，这个和非唯一索引删除一条不存在的记录是基本一样的，多了个因Sess1 提交成功后多获得的行锁。

唯一索引删除一条已经标记删除的记录Sess1Sess2Sess3

begin;

delete from tu where c1=8;

begin;

delete from tu where c1=8;

@1 show engine innodb status

commit;

@2 show engine innodb status

唯一索引删除一条已经标记删除的记录的锁情况为：加锁等待时: 2 lock struct(s) ，持有IX锁，等待记录上的X锁

加锁成功时：3 lock struct(s)，持有IX,行锁，和gap锁，和非唯一索引删除一条标记为已删除的记录的情况一模一样。

3. 总结在非唯一索引的情况下，删除一条存在的记录是有gap锁，锁住记录本身和记录之前的gap

在唯一索引和主键的情况下删除一条存在的记录，因为都是唯一值，进行删除的时候，是不会有gap存在

非唯一索引，唯一索引和主键在删除一条不存在的记录，均会在这个区间加gap锁

通过非唯一索引和唯一索引去删除一条标记为删除的记录的时候，都会请求该记录的行锁，同时锁住记录之前的gap

RC 情况下是没有gap锁的，除了遇到唯一键冲突的情况，如插入唯一键冲突。