MySQL数据库之Purge死锁问题解析_Mysql

MySQL数据库之Purge死锁问题解析

场景说明

MySQL数据库之Purge死锁问题解析

Purge死锁说明

表中存在记录（unique key） 10，20，30，40 （且有自增主键），现在删除记录 20 ，并且已经提交了该事物。 purge 线程此时还没有回收该记录，且此时又插入新的记录 20 。

									+------+------+------+------+

									orignal | 10 | 20 | 30 | 40 |

									unique +------+------+------+------+

									delete 20 +------+------+------+------+

									| 10 | 20* | 30 | 40 | (20 : delete-mark)

									and commit +------+^----^+------+------+

									| |

									non happen | +--insert new 20

									|

									Purge

									# 自增主键图中没有给出

回顾插入过程完整的插入过程如下：

假设现在有记录 10，30，50，70 ；且为 unique key ，需要插入记录 25 。

1. 找到小于等于25的记录，这里是 10

如果记录中已经存在记录25 ，且带有唯一性约束，则需要在记录25 上增加 S Gap-lock （purge的案例中，老记录20* 要加S lock的原因）

不直接报错退出或者提示已存在的原因，是因为有可能之前的记录25 标记为删除( delete-mark )，然后等待 purge

如果假设这里没有S Gap-Lock ，此时记录30 上也没有锁的，按照下面的步骤，可以插入两个25 ，这样就破坏了唯一性约束

2. 找到记录10的下一条记录，这里是 30

3. 判断下一条记录30 上是否有锁（如果有=25的情况，后面再讨论）

判断 30 上面如果没有锁，则可以插入

判断 30 上面如果有 Record Lock ，则可以插入

判断 30 上面如果有 Gap Lock / Next-Key Lock ，则无法插入，因为锁的范围是 (10, 30) / （10, 30] ；在 30 上增加 insert intention lock （此时处于 waiting 状态），当 Gap Lock / Next-Key Lock 释放时，等待的事物（transaction）将被唤醒，此时记录30 上才能获得 insert intention lock ，然后再插入记录25

在这个场景中，新插入的记录 20 ，和已经存在的记录 20* 相等，且带有唯一约束，那此时就需要在记录 20* 上增加 S lock（with gap）

演示

因为要模拟插入记录 20* 的时候，老的记录20 要存在，所以使用debug版本，将 purge线程停掉。

									[root@MyServer ~]&gt; mysqld-debug --version

									mysqld-debug Ver 5.7.11-debug for linux-glibc2.5 on x86_64 (MySQL Community Server - Debug (GPL))

									[root@MyServer ~]&gt; mysqld-debug --datadir=/data/mysql_data/5.7.11/ &amp;

									[1] 1493

									[root@MyServer ~]&gt; netstat -tunlp | grep 3306

									tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1493/mysqld-debug

									--

									-- 终端会话1

									mysql&gt; create table test_purge(a int auto_increment primary key, b int , unique key(b));

									Query OK, 0 rows affected (0.20 sec)

									mysql&gt; insert into test_purge(b) values (10),(20),(30),(40);

									Query OK, 4 rows affected (0.05 sec)

									mysql&gt; commit; -- autocommit=0 in my.cnf

									Query OK, 0 rows affected (0.03 sec)

									mysql&gt; set global innodb_purge_stop_now=1;

									-- show这个变量，结果还是off，这个不用管，purge线程已经停止了

									Query OK, 0 rows affected (0.00 sec)

									mysql&gt; begin;

									mysql&gt; delete from test_purge where b=20;

									Query OK, 1 row affected (0.00 sec)

									mysql&gt; commit;

									Query OK, 0 rows affected (0.02 sec)

									-- 终端会话2

									mysql&gt; select * from test_purge;

									+---+------+

									| a | b | -- 20的那条记录已经删除，但是还没有被purge（purge线程停止）

									| 1 | 10 |

									| 3 | 30 |

									| 4 | 40 |

									3 rows in set (0.00 sec)

									mysql&gt; insert into test_purge(b) values(20);

									Query OK, 1 row affected (0.04 sec)

									-- 终端会话3

									mysql&gt; show engine innodb status\G

									-- ----------------省略其他输出----------------

									---TRANSACTION 9497, ACTIVE 19 sec

									3 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1

									MySQL thread id 3, OS thread handle 139922002294528, query id 26 localhost root cleaning up

									TABLE LOCK table `burn_test`.`test_purge` trx id 9497 lock mode IX

									RECORD LOCKS space id 47 page no 4 n bits 72 index b of table `burn_test`.`test_purge` trx id 9497 lock mode S -- S lock （with gap）

									Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

									-- heap no=3表示是第二个插入的记录20

									-- 且info bits为32，表示记录被标记删除了

									0: len 4; hex 80000014; asc ;; -- 记录为20

									1: len 4; hex 80000002; asc ;; -- 对应的主键为2

									Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

									-- heap no=4表示的是20的下一个记录30

									-- 且该记录上也有S lock

									0: len 4; hex 8000001e; asc ;;

									1: len 4; hex 80000003; asc ;;

									RECORD LOCKS space id 47 page no 4 n bits 72 index b of table `burn_test`.`test_purge` trx id 9497 lock mode S locks gap before rec

									Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 -- heap no=6为新插入的记录20，从隐式锁提升为显示锁

									0: len 4; hex 80000014; asc ;;

									1: len 4; hex 80000005; asc ;;

1. 因为是唯一索引，需要做唯一性检查，从老的记录 20* 开始检查（第一个小于等于自己的值），则此时 20* 上要加上一把 S lock ，然后往下检查到第一个不相等的记录，即记录30 ，然后退出，但是这个记录30 也要加上S lock

2. 在插入新的记录20 的时候，发现下一条记录30上有锁，则自己插入的时的隐式锁提升为显示锁（见插入步骤）

3. 目前锁住的范围是 (10,20], (20,30]

4. 新插入的记录20本身是一把 S-Gap Lock （前面20*的有S lock了，由于是唯一索引，本身其实就不需要有记录锁了，有GAP就够了）

所以记录25无法插入（锁等待）

									mysql&gt; insert into test_purge(b) values(25);

									ERROR 1205 (HY000): Unknown error 1205 -- 等待了一段时间后,超时

									---TRANSACTION 9508, ACTIVE 3 sec inserting

									mysql tables in use 1, locked 1

									LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1

									MySQL thread id 5, OS thread handle 139922002560768, query id 46 localhost root update

									insert into test_purge(b) values(25) -- 插入的25在等待

									------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

									RECORD LOCKS space id 47 page no 4 n bits 72 index b of table `burn_test`.`test_purge` trx id 9508 lock_mode X locks gap before rec insert intention waiting

									------------------

									TABLE LOCK table `burn_test`.`test_purge` trx id 9508 lock mode IX

									---TRANSACTION 9503, ACTIVE 10 sec

									MySQL thread id 7, OS thread handle 139922002028288, query id 44 localhost root cleaning up

									TABLE LOCK table `burn_test`.`test_purge` trx id 9503 lock mode IX

									RECORD LOCKS space id 47 page no 4 n bits 72 index b of table `burn_test`.`test_purge` trx id 9503 lock mode S

									1: len 4; hex 80000002; asc ;;

									RECORD LOCKS space id 47 page no 4 n bits 72 index b of table `burn_test`.`test_purge` trx id 9503 lock mode S locks gap before rec

									Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

									1: len 4; hex 80000007; asc ;;