一、问题提出 问题是由姜大师提出的、问题如下: 表: 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中。
---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)