MYSQL INNODB replace into 死锁及 next key lock 解析

发布时间：2022-06-27 10:13:45 所属栏目：MySql教程来源：互联网

导读：全文带入了大量自我认知和理解，可能错误，因为水平有限，但是代表我努力分析过。一、问题提出问题是由姜大师提出的、问题如下：表： mysql show create table c G *************************** 1. row *************************** Table: c Create T

　　全文带入了大量自我认知和理解，可能错误，因为水平有限，但是代表我努力分析过。

　　一、问题提出
　　问题是由姜大师提出的、问题如下：
　　表：
　　mysql> show create table c G
　　*************************** 1. row ***************************
　　       Table: c
　　Create Table: CREATE TABLE `c` (
　　 `a` int(11) NOT NULL AUTO_INCREMENT,
　　 `b` int(11) DEFAULT NULL,
　　 PRIMARY KEY (`a`),
　　 UNIQUE KEY `b` (`b`)
　　) ENGINE=InnoDB
　　1 row in set (0.01 sec)
　　开启两个会话不断的执行
　　replace into c values(NULL,1);
　　会触发死锁。问死锁触发的原因。

　　我使用的环境：
　　MYSQL 5.7.14 debug版本、隔离级别RR、自动提交，很显然这里的c表中的可以select出来的记录始终是1条
　　只是a列不断的增大，但是这里实际存储空间确不止1条，因为从heap no来看二级索引中，heap no 已经到了
　　7，也就是有至少7(7-1)条记录,只是其他记录标记为del并且被purge线程放到了page free_list中。

　　二、准备工作和使用方法
　　1、稍微修改了源码关于锁的打印部分，我们知道每个事物下显示锁内存结构lock
　　   struct会连接成一个链表，只要按照顺序打印出内存lock struct就打印出了
　　   所有关于这个事物显示锁全部信息和加锁顺序如下：

　　点击(此处)折叠或打开

　　---TRANSACTION 184771, ACTIVE 45 sec
　　4 lock struct(s), heap size 1160, 3 row lock(s)
　　MySQL thread id 2, OS thread handle 140737154311936, query id 642 localhost root cleaning up
　　---lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
　　TABLE LOCK table `test`.`c4` trx id 184771 lock mode IX
　　---lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
　　RECORD LOCKS space id 413 page no 4 n bits 72 index id2 of table `test`.`c4` trx id 184771 lock_mode X(LOCK_X)
　　Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
　　 0: len 4; hex 80000014; asc ;;
　　 1: len 4; hex 80000014; asc ;;
　　---lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
　　RECORD LOCKS space id 413 page no 3 n bits 72 index PRIMARY of table `test`.`c4` trx id 184771 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
　　Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
　　 0: len 4; hex 80000014; asc ;;
　　 1: len 6; hex 00000002d1bd; asc ;;
　　 2: len 7; hex a600000e230110; asc # ;;
　　 3: len 4; hex 80000014; asc ;;
　　---lock strcut(4):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
　　RECORD LOCKS space id 413 page no 4 n bits 72 index id2 of table `test`.`c4` trx id 184771 lock_mode X(LOCK_X) locks gap before rec(LOCK_GAP)
　　Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
　　 0: len 4; hex 8000001e; asc ;;
　　 1: len 4; hex 8000001e; asc ;;

　　点击(此处)折叠或打开

　　---TRANSACTION 184771, ACTIVE 45 sec
　　4 lock struct(s), heap size 1160, 3 row lock(s)
　　MySQL thread id 2, OS thread handle 140737154311936, query id 642 localhost root cleaning up
　　部分后面的都是我加上的，其实修改很简单，innodb其实自己写好了只是没有开启，我开启后加上了序号来表示顺序。
　　上面是一个 select * from c where id2= 20 for update; b列为辅助索引的所有4 lock struct(s)，可以看到有了这些信息分析
　　不那么难了。
　　这里稍微分析一下
　　表结构为：
　　mysql> show create table c4;
　　+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
　　| Table | Create Table                                                                                                                                                  |
　　+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
　　| c4    | CREATE TABLE `c4` (
　　 `id1` int(11) NOT NULL,
　　 `id2` int(11) DEFAULT NULL,
　　 PRIMARY KEY (`id1`),
　　 KEY `id2` (`id2`)
　　) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
　　+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
　　1 row in set (0.00 sec)
　　数据为：
　　mysql> select * from c4;
　　+-----+------+
　　| id1 | id2 |
　　+-----+------+
　　|   1 |    1 |
　　| 10 |   10 |
　　| 20 |   20 |
　　| 30 |   30 |
　　+-----+------+
　　4 rows in set (0.00 sec)

　　2、在死锁检测回滚前调用这个打印函数打印到err日志文件中，打印出全部的事物的显示内存lock struct如下，这里就
　　不给出了，后面会有replace触发死锁千事物锁结构的一个输出

　　3、使用MYSQL TRACE SQL语句得到大部分的函数调用来分析replace的过程

　　修改出现的问题：修改源码打印出所有lock struct 在线上显然是不能用的。因为打印出来后show engine innodb status 会非常
　　长，甚至引发其他问题，但是测试是可以，其次修改了打印死锁事物锁链表到日志后，每次只要遇到死锁信息可以打印
　　到日志，但是每次MYSQLD都会挂掉，但是不影响分析了。

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

数据库中间件ProxySQL	zabbix 一次悲惨的分区
mysql如何实现数据备份	建立数据表主键外键

MYSQL INNODB replace into 死锁 及 next key lock 解析

MYSQL INNODB replace into 死锁及 next key lock 解析