（五）MySQL InnoDB中各類語句加鎖方式

MySQL InnoDB中各類語句加鎖方式

（一）MySQL InnoDB事務模型

（二）MySQL InnoDB鎖模型

（三）MySQL InnoDB非鎖定一緻性讀與鎖定讀

（四）MySQL InnoDB鎖類型及幻象讀問題

（五）MySQL InnoDB中各類語句加鎖方式

（六）事務的送出與復原極死鎖檢測、處理和預防

鎖定讀、UPDATE、DELETE通常在處理SQL語句的過程中在掃描到的每個索引記錄上加鎖，不關心WHERE條件中可能排除行的非索引條件。比如，A表有兩列i和j，i列有索引，j列沒索引，目前存在(1,1)，（1,2），（1,3），（1,4），（2,1），（2,2），（2,3），（2,4）……等記錄，語句SELECT * FROM A WHERE i=1 AND j=3;會在所有i=1的索引記錄上加鎖，而不考慮j=3這個條件。如果查詢中使用了輔助索引，InnoDB除了給掃描到的輔助索引加鎖，還會查找到對應的聚集索引并在其上加鎖。若語句用不到合适的索引，則MySQL會掃描整個表，每個表行都會被加鎖，會阻塞其他使用者的插入操作。

InnoDB對不同的SQL語句加不同的鎖：

SELECT...FROM讀資料庫快照，不對記錄加鎖，除非使用的是SERIALLIZABE隔離級别，此時對索引記錄加S Next-key Lock。

SELECT...FROM...IN SHARE MODE加S Next-key Lock。

SELECT...FROM...FOR UPDATR /  UPDATE ... WHERE ...  / DELETE FROM ... WHERE ... /加X Next-key Lock。

INSERT在插入的索引記錄上加X鎖，不會阻止其他事物在插入的記錄前的“間隙”插入新的記錄。插入記錄前，會設定一把 insertion intention gap lock用以表明：不同的事務可以向同一索引“間隙”插入記錄而無需互相等待，隻要其插入的位置不同。一個事務中insert語句會在插入的行的索引記錄上設定一把排它鎖。如果有鍵重複的錯誤發生，則會在重複的索引記錄上設定一把共享鎖。在多個session同時插入同一行，且另外的某個session已經持有了該索引記錄的排它鎖時，共享鎖的使用可能導緻死鎖的出現。

舉個例子：

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Session 1:

START TRANSACTION;

INSERT INTO t1 VALUES(1);

Session 2:

START TRANSACTION;

INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;

INSERT INTO t1 VALUES(1);

Session 1:

ROLLBACK;

三個session：session1會擷取行上的X鎖，且一直持有到事務結束（送出或者復原）。session2和session3的操作會引起鍵重複的錯誤，是以會請求索引記錄上的S鎖。因為S鎖和X鎖不相容，這兩個鎖請求會阻塞直到session1中事務結束。當session1復原後，釋放了持有的X鎖，隊列中的兩個S鎖請求會同時成功。此時session2與session便會發生死鎖。因為兩個session中的事務都需要在插入的行上加X鎖，而此時因其他session所持有的S鎖，誰也擷取不到這個X鎖。于是會復原代價較小的事務解除死鎖。如果session是送出而非復原，則送出後成功插入該行，事務結束、釋放X鎖。session2.session3中的插入因鍵重複而發生錯誤語句復原，持有的S鎖被釋放。

還有類似的情形，比如t1表中已經存在某行記錄。同時session1中某個事務對改行進行删除操作。session2、session3中對改行進行插入操作。session1的事務中會在改行對應的索引記錄上添加X鎖。session2、session3中的事務因鍵沖突會申請索引記錄上的S鎖。sesession1中的事務送出後，記錄被删除、X鎖釋放。session2和session3中的事務獲得S鎖。因為此時表中沒有對應的記錄是以session2和session3中的事務認為可以插入。但此時會發生死鎖，因為各自在申請插入記錄上的X鎖時互相等待對方的X鎖。

INSERT ... ON DUPLICATE KEY UPDATE 與普通INSERT語句不同，在發生鍵重複的錯誤後，在記錄上加X Next-key Lock而非 S鎖。

REPLACE語句，無唯一鍵沖突時同INSERT，否則在記錄上加X Next-key Lock。

INSERT INTO T SELECT ... FROM S WHERE ...對插入在T表的記錄加X鎖。若隔離級别為READ COMMITTED或者打開了innodb_locks_unsafe_for_binlog且隔離級别不為 SERIALIZABLE則不對從S中搜尋到的索引記錄加鎖，否則添加S Next-key Lock。

CREATE TABLE ... SELECT ... ， REPLACE INTO t SELECT ... FROM s WHERE ...， UPDATE t ... WHERE col IN (SELECT ... FROM s ...)與INSERT INTO T SELECT ... FROM S WHERE ...類似。

自增長與鎖： InnoDB初始化自增列時會在自增列相關索引的最後一條索引記錄加鎖。這裡使用一種特殊的鎖叫AUTO-INC table lock。該鎖會在目前語句就結束後釋放而非整個事務結束後釋放，在有事務持有AUTO-INC table lock時其他事物session不能插入記錄。

外鍵與鎖：若表上定義有外鍵限制，任何需要檢查限制條件的insert、update、delete操作均會在待檢查記錄上添加S鎖。