一、insert … select 語(yǔ)句：
??對(duì)于表t，t2：
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t
??在可重復(fù)讀隔離級(jí)別下，binlog_format = statement時(shí)執(zhí)行insert into t2(c,d) select c,d from t;時(shí)需要對(duì)表t的所有行和間隙加鎖的原因：
??對(duì)于這個(gè)執(zhí)行序列：
??但如果沒(méi)有鎖的話，就可能出現(xiàn)session B的insert語(yǔ)句先執(zhí)行，但是后寫入binlog的情況。于是在
binlog_format = statement的情況下，binlog里面就記錄了這樣的語(yǔ)句序列：
insert into t values(-1,-1,-1);
insert into t2(c,d) select c,d from t;
??這個(gè)語(yǔ)句到了備庫(kù)執(zhí)行，就會(huì)把id=-1這一行也寫到表t2中，出現(xiàn)主備不一致。
注意：
- 主要考慮的是日志和數(shù)據(jù)的一致性。
- 對(duì)目標(biāo)表不是鎖全表，而是只鎖住需要訪問(wèn)的資源。
二、insert 循環(huán)寫入：
1、對(duì)于語(yǔ)句：insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);//往表t2中插入一行數(shù)據(jù)，這一行的c值是表t中c值的最大值加1。:
??這個(gè)語(yǔ)句的加鎖范圍，就是表t索引c上的(4,supremum]這個(gè)next-key lock和主鍵索引上id=4這一行。
??該語(yǔ)句的執(zhí)行流程是：從表t中按照索引c倒序，掃描第一行，拿到結(jié)果寫入到表t2中。
2、對(duì)于語(yǔ)句：insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);:
??該語(yǔ)句執(zhí)行的慢查詢?nèi)罩?slow log)為：
??因此整條語(yǔ)句的掃描行數(shù)是5。(Rows_examined :5)
??該語(yǔ)句的explain結(jié)果為：
??該語(yǔ)句的執(zhí)行過(guò)程為：
創(chuàng)建臨時(shí)表，表里有兩個(gè)字段c和d。
按照索引c掃描表t，依次取c=4、3、2、1，然后回表，讀到c和d的值寫入臨時(shí)表。這時(shí)，Rows_examined=4。
由于語(yǔ)義里面有l(wèi)imit 1，所以只取了臨時(shí)表的第一行，再插入到表t中。這時(shí)，Rows_examined的值加1，變成了5。
注意：
- 這個(gè)語(yǔ)句會(huì)導(dǎo)致在表t上做全表掃描，并且會(huì)給索引c上的所有間隙都加上共享的next-key lock。所以，這個(gè)語(yǔ)句執(zhí)行期間，其他事務(wù)不能在這個(gè)表上插入數(shù)據(jù)。
- 執(zhí)行需要臨時(shí)表的原因：這類一邊遍歷數(shù)據(jù)，一邊更新數(shù)據(jù)的情況，如果讀出來(lái)的數(shù)據(jù)直接寫回原表，就可能在遍歷過(guò)程中，讀到剛剛插入的記錄，新插入的記錄如果參與計(jì)算邏輯，就跟語(yǔ)義不符。
- 由于實(shí)現(xiàn)上這個(gè)語(yǔ)句沒(méi)有在子查詢中就直接使用limit 1，從而導(dǎo)致了這個(gè)語(yǔ)句的執(zhí)行需要遍歷整個(gè)表t。它的優(yōu)化方法也比較簡(jiǎn)單，就是先insert into到臨時(shí)表temp_t，這樣就只需要掃描一行；然后再?gòu)谋韙emp_t里面取出這行數(shù)據(jù)插入表t1。
三、insert 唯一鍵沖突：
1、
![]()
唯一鍵沖突加鎖
??在可重復(fù)讀（repeatable read）隔離級(jí)別下執(zhí)行?？梢钥吹?，session B要執(zhí)行的insert語(yǔ)句進(jìn)入了鎖等待狀態(tài)。session A執(zhí)行的insert語(yǔ)句，發(fā)生主鍵沖突的時(shí)候，并不只是簡(jiǎn)單地報(bào)錯(cuò)返回，還在沖突的索引上加了鎖。而由于一個(gè)next-key lock就是由它右邊界的值定義的。這時(shí)候，session A持有索引c上的(5,10]共享next-key lock（讀鎖）。
??加這個(gè)讀鎖，從作用上來(lái)看，這樣做可以避免這一行被別的事務(wù)刪掉。
2、
![]()
唯一鍵沖突--死鎖
??現(xiàn)象：在session A執(zhí)行rollback語(yǔ)句回滾的時(shí)候，session C幾乎同時(shí)發(fā)現(xiàn)死鎖并返回。
??死鎖產(chǎn)生的邏輯：
- 在T1時(shí)刻，啟動(dòng)session A，并執(zhí)行insert語(yǔ)句，此時(shí)在索引c的c=5上加了記錄鎖。注意，這個(gè)索引是唯一索引，因此退化為記錄鎖。
- 在T2時(shí)刻，session B要執(zhí)行相同的insert語(yǔ)句，發(fā)現(xiàn)了唯一鍵沖突，加上讀鎖；同樣地，session C也在索引c上，c=5這一個(gè)記錄上，加了讀鎖。
-
T3時(shí)刻，session A回滾。這時(shí)候，session B和session C都試圖繼續(xù)執(zhí)行插入操作，都要加上寫鎖。兩個(gè)session都要等待對(duì)方的行鎖，所以就出現(xiàn)了死鎖。
??流程的狀態(tài)變化圖：
四、insert into … on duplicate key update：
??對(duì)于語(yǔ)句：insert into t values(11,10,10) on duplicate key update d=100;,
- 會(huì)給索引c上(5,10] 加一個(gè)排他的next-key lock（寫鎖）。
-
insert into … on duplicate key update;這個(gè)語(yǔ)義的邏輯是插入一行數(shù)據(jù)，如果碰到唯一鍵約束，就執(zhí)行后面的更新語(yǔ)句。 - 如果有多個(gè)列違反了唯一性約束，就會(huì)按照索引的順序，修改跟第一個(gè)索引沖突的行。
-
假設(shè)現(xiàn)在表t里面已經(jīng)有了(1,1,1)和(2,2,2)這兩行，下面這個(gè)語(yǔ)句執(zhí)行的效果：
兩個(gè)唯一鍵同時(shí)沖突
可以看到：
- 主鍵id是先判斷的，MySQL認(rèn)為這個(gè)語(yǔ)句跟id=2這一行沖突，所以修改的是id=2的行。
- 需要注意的是，執(zhí)行這條語(yǔ)句的affected rows返回的是2，很容易造成誤解。實(shí)際上，真正更新的只有一行，只是在代碼實(shí)現(xiàn)上，insert和update都認(rèn)為自己成功了，update計(jì)數(shù)加了1， insert計(jì)數(shù)也加了1。
