MySQL存儲引擎InnoDB 中各 SQL 語句的鎖定設置

SELECT ... FROM ...: 這(zhè)是一個 consistent read，不以鎖定方式讀取數據庫的快照，除非事(shì)務的隔離級被(bèi)設置爲SERIALIZABLE，在這(zhè)種(zhǒng)情況下將(jiāng)在它所讀取的記錄索引上設置共享的 next-key locks。

SELECT ... FROM ... LOCK IN SHARE MODE: 在所讀取的所有記錄索引上設置同享的鎖定。

SELECT ... FROM ... FOR UPDATE: 在所讀取的所胡記錄索引上設置獨占地(exclusive)鎖定。

INSERT INTO ... VALUES (...): 在插入的記錄行上設置一個獨占地鎖定；注意這(zhè)個鎖定并不是一個 next-key lock ，并不會(huì)阻止其它用戶在所插入行之前的間隙(gap)中插入新記錄。如果産生一個重複鍵值錯誤， 在重複索引記錄上設置一個共享的鎖定。

如果在一個表中定義了一個AUTO_INCREMENT列，InnoDB 在初始化自增計數器時(shí)將(jiāng)在與自增列最後(hòu)一個記錄相對(duì)應的索引上設置一個獨占的鎖定。在訪問自增計數器時(shí)， InnoDB 將(jiāng)設置一個特殊的表鎖定模式AUTO-INC，這(zhè)個鎖定隻持續到該 SQL 語句的結束而不是整個事(shì)務的結束。

INSERT INTO T SELECT ... FROM S WHERE ...在已插入到表T中的每個記錄上設置一個獨占的(無 next-key)鎖定。以一個 consistent read 搜索表S，但是如果 MySQL 打開(kāi)了日志開(kāi)關將(jiāng)在表S上設置一個共享的鎖定。 在從備份中進(jìn)行前滾(roll-forward)修複時(shí)，每個 SQL 語句必須嚴格按照原先所執行的順序運行，所以 InnoDB 不得不設置鎖定。

CREATE TABLE ... SELECT ...與上項相似，以 consistent read 或鎖定方式完成(chéng)SELECT。

REPLACE如果沒(méi)有一個 unique key 沖突，它的執行與 insert 一緻。否則將(jiāng)在它所要更新的記錄上設置一個獨占的鎖定。

UPDATE ... SET ... WHERE ...: 在搜索時(shí)所遭遇到的記錄上設置一個獨占的鎖定。

DELETE FROM ... WHERE ...: 在搜索時(shí)所遭遇到的每一個記錄上設置一個獨占的鎖定。

如果一個表上有FOREIGN KEY約束，所有需要檢查約束條件的 insert, update, 或 delete 將(jiāng)在它所要檢查約束的記錄上設置記錄共享級的鎖定。同樣(yàng)在約束失敗時(shí)，InnoDB 也設置這(zhè)個鎖定。

LOCK TABLES ...: 設置表鎖定。在 MySQL 的代碼層(layer of code)設置這(zhè)些鎖定。InnoDB 的自動死鎖檢測無法檢測出有關下列情形的表鎖定：查看下面(miàn)的一個章節。同時(shí)查看第 14 章節 'InnoDB 限制與不足' 有關下列内容： 自從 MySQL 提供行鎖以來，將(jiāng)有可能(néng)發(fā)生當其他用戶設置了行級鎖定時(shí)你又對(duì)該表設置了鎖定。But that does not put transaction integerity into danger.

在 早期 版本以前，SHOW TABLE STATUS應用于一個自增表時(shí)將(jiāng)在自增列的最大記錄索引上設置一個獨占的行級鎖定。 這(zhè)就(jiù)意味著(zhe)SHOW TABLE STATUS可能(néng)會(huì)引起(qǐ)一個事(shì)務的死鎖，這(zhè)可能(néng)是我們所意想不到的。從 3.23.50 開(kāi)始，在讀取自增列值時(shí)將(jiāng)不再設置任何鎖定，除非在某些情況下，比如在數據庫啓動後(hòu)沒(méi)有任何記錄。