MySQL数据库之Purge死锁问题解析
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 ~]> mysqld-debug --version mysqld-debug ver 5.7.11-debug for linux-glibc2.5 on x86_64 (mysql community server - debug (gpl)) [root@myserver ~]> mysqld-debug --datadir=/data/mysql_data/5.7.11/ & [1] 1493 [root@myserver ~]> netstat -tunlp | grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* listen 1493/mysqld-debug -- -- 终端会话1 mysql> create table test_purge(a int auto_increment primary key, b int , unique key(b)); query ok, 0 rows affected (0.20 sec) mysql> insert into test_purge(b) values (10),(20),(30),(40); query ok, 4 rows affected (0.05 sec) mysql> commit; -- autocommit=0 in my.cnf query ok, 0 rows affected (0.03 sec) mysql> set global innodb_purge_stop_now=1; -- show这个变量,结果还是off,这个不用管,purge线程已经停止了 query ok, 0 rows affected (0.00 sec) mysql> begin; mysql> delete from test_purge where b=20; query ok, 1 row affected (0.00 sec) mysql> commit; query ok, 0 rows affected (0.02 sec) -- 终端会话2 mysql> select * from test_purge; +---+------+ | a | b | -- 20的那条记录已经删除,但是还没有被purge(purge线程停止) | 1 | 10 | | 3 | 30 | | 4 | 40 | 3 rows in set (0.00 sec) mysql> insert into test_purge(b) values(20); query ok, 1 row affected (0.04 sec) -- 终端会话3 mysql> 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> 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 ;;
这个例子中出现了 锁等待 ,就要 警惕 了,如果有 两个事物相互等待 ,就是 死锁 了
总结
以上所述是小编给大家介绍的mysql数据库之purge死锁问题解析,希望对大家有所帮助
推荐阅读
-
MySQL数据库之Purge死锁问题解析
-
MySQL之数据查询语法(DQL)--排序问题解析
-
MySQL数据库性能优化之避免数据死锁的发生
-
解析MYSQL 数据库导入SQL 文件出现乱码的问题_MySQL
-
技巧应用:MySQL数据库常见问题解析_MySQL
-
解析MYSQL 数据库导入SQL 文件出现乱码的问题
-
【面试高频!】数据库事务/事务的作用/数据库事务ACID属性 / 特性/事务的隔离性/数据库事务隔离级别/ 数据库的隔离级别导致问题/数据库事务的使用/mysql的事务死锁/ 快速解决mysql死锁问
-
解析MYSQL 数据库导入SQL 文件出现乱码的问题
-
MySQL之数据查询语法(DQL)--排序问题解析
-
技巧应用:MySQL数据库常见问题解析_MySQL