一、問題提出 問題是由姜大師提出的、問題如下： 表： 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)
