在MySQL鎖機制漫談(一)一文中，我們主要是探究了以下MySQL（主要是InnoDB）的鎖的機制,但是我們平常經(jīng)常使用的SQL語句一般都會加上什么鎖，我自己也并不太熟悉，因此本文就羅列一些SQL語句在在執(zhí)行過程中添加的鎖情況。注意，本文主要是基于MySQL的官方文檔而來，有不同意見也可以繼續(xù)討論。
一個UPDATE或者DELETE語句一般會在執(zhí)行SQL操作的過程中對掃描到的每一個索引記錄添加記錄鎖（record lock）.InnoDB不會記錄具體的where條件，而僅僅知道那些索引范圍被掃描過。這些鎖通常來說都是會阻塞在記錄前面的間隙中插入數(shù)據(jù)的next-key lock。但是由于間隙鎖可以被顯式禁用，這同時會導(dǎo)致next-key鎖定無法使用。
如果通過一個二級索引進行查詢，同時該索引記錄是獨占的，InnoDB會找到對應(yīng)的聚集索引并加鎖。
如果你的表中沒有適合SQL語句的索引，此時MySQL就會掃描整個表來執(zhí)行語句，因此表中的每一行就被鎖住了，這回導(dǎo)致任何其他事物的insert操作都會被阻塞。所以，好的索引能夠極大的減少不必要的行掃描。
對于SELECT ... FOR UPDATE或者 SELECT ... LOCK IN SHARE MODE來說，鎖被你獲取是進行行掃描，而且會在對比發(fā)現(xiàn)呢不符合結(jié)果集（也即不符合WHERE后的條件）而被釋放。但是在某些情況下，由于結(jié)果集和數(shù)據(jù)源之間的關(guān)系在查詢過程丟失，行鎖可能不會立即被釋放（如在一個UNION操作中，在沒有評估這些行是否符合結(jié)果集前，被掃描的行可能會插入一個臨時表中。在這種情況下，臨時表和原始表中的行的關(guān)系就丟失了，后續(xù)的行鎖不會立即釋放知道整個查詢的結(jié)束）。
InnoDB在執(zhí)行SQL語句時會添加如下類型的鎖：
SELECT ... FROM 這是一個一致性讀，會讀取數(shù)據(jù)庫的一個快照版本同時不會加鎖（即所謂的一致性非鎖定讀），但這不會發(fā)生在數(shù)據(jù)庫的隔離級別設(shè)置為SERIALIZABLE。在SERIALIZABLE隔離級別下，每個查詢會在每一個掃描到的索引上添加共享的next-key鎖。但是，對于使用唯一索引來查詢唯一行的語句來說，此時只會加上record lock。
SELECT ... FROM ... LOCK IN SHARE MODE 該語句會在查詢時每一個掃描到的索引上添加共享的next-key鎖。但是，對于使用唯一索引來查詢唯一行的語句來說，此時只會加上record lock。
SELECT ... FROM ... FOR UPDATE 該語句會在查詢時每一個掃描到的索引上添加排他的next-key鎖。但是，對于使用唯一索引來查詢唯一行的語句來說，此時只會加上record lock。對于碰到的索引記錄，該語句會通過執(zhí)行SELECT ... FROM ... LOCK IN SHARE MODE 或者在特定的隔離級別上讀來阻塞其他事務(wù)。一致性讀會忽略讀視圖中的任何加在記錄上的鎖。
UPDATE ... WHERE 該語句會在查詢時每一個掃描到的索引上添加排他的next-key鎖。但是，對于使用唯一索引來查詢唯一行的語句來說，此時只會加上record lock。當UPDATE操作修改一個聚集索引記錄時，隱式的鎖會影響二級索引。UPDATE操作會在一個二級索引上加上共享鎖當在插入一個二級索引記錄之前執(zhí)行重復(fù)檢查掃描或者當進行插入一個二級索引記錄時。
DELETE ... FROM ... WHERE 該語句會在查詢時每一個掃描到的索引上添加排他的next-key鎖。但是，對于使用唯一索引來查詢唯一行的語句來說，此時只會加上record lock。
INSERT 操作在插入行上加上了排他鎖，這是一個基于索引的鎖，而非next-key lock （也即不是間隙鎖，因此也就不會阻止其他事務(wù)在要插入的行前面執(zhí)行插入語句）。
在插入該行記錄前，會加一種叫做插入意向間隙鎖的gap lock。該鎖是這樣告知一個事務(wù)有插入的意向的-即如果多個事務(wù)在同一個索引間隙上執(zhí)行插入操作，如果各事務(wù)不是在同一個插入點上進行摻入操作就不需要互相等待。假設(shè)現(xiàn)有值為4和7的索引記錄，分別有兩個事務(wù)嘗試插入值5和6，這兩個事務(wù)都會先去獲取插入意向鎖而非在被插入的行上獲取排他鎖去鎖住4和7之間的間隙，由于這兩行的插入也不存在沖突，故也不會去互相阻塞。
如果出現(xiàn)了key重復(fù)的錯誤，就會在該重復(fù)key索引記錄上加上共享鎖。如果多個事務(wù)嘗試在同一個行記錄上進行插入同時某個事務(wù)已經(jīng)在該行上擁有一個排他鎖，那么這種共享鎖就會導(dǎo)致死鎖的出現(xiàn)。
假設(shè)一個場景，一個InnoDB表t1有如下結(jié)構(gòu)：
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
現(xiàn)在假設(shè)有三個事務(wù)按順序在執(zhí)行以下操作，
事務(wù)1:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
事務(wù)2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
事務(wù)3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
事務(wù)1:
ROLLBACK;
事務(wù)1的第一個操作會獲取一個排他鎖；事務(wù)2和事務(wù)3的操作會導(dǎo)致主鍵重復(fù)錯誤，同時兩者都會請求獲取一個共享鎖在該行。當事務(wù)1進行回滾，會釋放在該行上的排他鎖，同時事務(wù)2和事務(wù)3的排隊的共享鎖請求會成功。在這個點，事務(wù)2和事務(wù)3會形成死鎖。沒有一方會獲取該行的排他鎖由于各自都持有共享鎖。
INSERT...ON DUPLICATE KEY UPDATE 該操作與簡單的INSERT操作不同。當發(fā)生key重復(fù)錯誤，該操作會在要更新的行上加上排他鎖而非共享鎖。而對重復(fù)主鍵值這是一個排他索引記錄鎖；對重復(fù)的唯一鍵值，這回事一個排他next-key鎖。
INSERT INTO T SELECT...FROM S WHERE ... 該操作會在要插入的表T的每一行加上排他索引記錄鎖。如果表的事務(wù)隔離模型是READ_COMMITTED或innodb_locks_unsafe_for_binlog始能同時手誤隔離級別不是SERIALIZZABLE,InnoDB會在表S上進行一致性讀（無鎖）。否則，InnoDB會在表S上的行加上共享next-key鎖。
