前言

在Mysql中，鎖定讀、更新、刪除操作通常會對SQL語句處理過程中掃描到的每條索引記錄設(shè)置記錄鎖。語句中是否存在排除該行的WHERE條件并不重要。InnoDB不記得確切的WHERE條件，只知道掃描了哪些索引范圍。next-key鎖通常會阻塞往記錄之前的間隙插入數(shù)據(jù)?？梢燥@示禁用間隙鎖，使得next-key鎖不可用。事務(wù)的隔離級別也會影響鎖的設(shè)置。

如果在搜索中使用了二級索引，并且在二級索引上設(shè)置的是排他鎖，InnoDB還會檢索相應(yīng)的聚集索引，并在聚集索引上也設(shè)置鎖。如果在SQL語句中沒有使用合適的索引，此時(shí)MySQL必須掃描整個(gè)表來處理該語句，表中的每一行都會被鎖住，這將導(dǎo)致其他用戶往該表的插入操作全部阻塞。所以，給表創(chuàng)建合適的索引非常重要，可以在查詢時(shí)不掃描多余的行。

InnoDB 設(shè)置特定類型的鎖如下

SELECT ... FROM：

是一致性讀取，該語句讀取的是數(shù)據(jù)庫快照并且不設(shè)置鎖，除非事務(wù)的隔離級別設(shè)置為SERIALIZABLE。在SERIALIZABLE隔離級別下執(zhí)行該語句會在滿足條件的索引記錄上設(shè)置共享next-key鎖。但是，在使用唯一索引查找唯一行的語句時(shí)，只需要索引記錄鎖。

SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE：

掃描行時(shí)為行加鎖，對于不滿足條件的行會釋放鎖（例如，不滿足WHERE字句中給出的條件）。但是在某些情況下，可能不會立即釋放鎖，因?yàn)樵诓樵兤陂g，結(jié)果行與原始源之間的關(guān)系會丟失。例如，在UNION查詢中，可能會先將表中掃描和鎖定的行插入到臨時(shí)表中，然后在評估它們是否符合結(jié)果集。在這種情況下，臨時(shí)表中的行與原始表中的行的關(guān)系丟失，所以要在查詢執(zhí)行結(jié)束后才能解鎖原始表中的行。

SELECT ... LOCK IN SHARE MODE：

給查詢過程中遇到的所有索引記錄設(shè)置共享的next-key鎖。但是，對于使用唯一索引鎖定行以搜索唯一行的語句，只需要使用記錄鎖。

SELECT ... FOR UPDATE：

給查詢過程中遇到的每條記錄設(shè)置排他的next-key 鎖。但是，對于使用唯一索引鎖定行以搜索唯一行的語句，只需要使用記錄鎖。對于查詢過程中遇到的索引記錄，SELECT ... FOR UPDATE會阻塞其他會話執(zhí)行SELECT ... LOCK IN SHARE MODE或在某個(gè)事務(wù)隔離級別下讀取數(shù)據(jù)。一致性讀將忽略存在于讀取視圖中的記錄上的任何鎖。

UPDATE ... WHERE ... ：

在搜索過程中遇到的每條記錄上設(shè)置排他的next-key鎖。如果WHERE子句中使用了唯一索引，則將使用記錄鎖。如果UPDATE的是聚集索引，則與其相關(guān)的二級索引也會被隱式上鎖。當(dāng)插入新的二級索引記錄之前執(zhí)行重復(fù)性檢查掃描和正在插入新的二級索引記錄時(shí)，UPDATE操作會對受影響的二級索引記錄設(shè)置共享鎖。

DELETE FROM ... WHERE ...：

對搜索遇到的每條記錄上設(shè)置排他的next-key鎖。但是，對于使用唯一索引鎖定行以搜索唯一行的語句，只需要使用記錄鎖。

INSERT：

對插入的行設(shè)置排他鎖。這個(gè)鎖是記錄鎖，而不是next-key鎖（也就是說，你沒有間隙鎖），并且不會阻塞其他會話往插入行之前的間隙插入記錄。插入語句在執(zhí)行插入前會先獲得插入意向鎖。插入意向鎖表示插入的意圖，多個(gè)事務(wù)往相同間隙中的不同位置插入時(shí)則他們無需相互等待。假設(shè)有值為4和7的索引記錄。若兩個(gè)單獨(dú)事務(wù)分別插入5和6，在獲得插入行的排它鎖之前會分別獲得4和7之間間隙的插入意向鎖。因?yàn)檫@兩個(gè)事務(wù)插入的行不同，所以不會阻塞。

如果出現(xiàn)重復(fù)鍵錯誤，則對重復(fù)索引記錄加共享鎖。此時(shí)，如果有一個(gè)事務(wù)已經(jīng)獲得了該行的排他鎖，此時(shí)，有多個(gè)事務(wù)試圖插入同一行， 那么共享鎖的使用可能會導(dǎo)致死鎖。

如果有一個(gè)事務(wù)要刪除該行，此時(shí)，有多個(gè)事務(wù)試圖插入同一行，也有可能發(fā)生這種情況。

示例

假設(shè)InnoDB中有如下結(jié)構(gòu)表t1:

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

假設(shè)有三個(gè)事務(wù)按順序執(zhí)行以下操作：

Session1：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session2：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session3：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session1：

ROLLBACK;

事務(wù)1的插入操作獲取該行的排他鎖。事務(wù)2和事務(wù)3的操作都會導(dǎo)致重復(fù)鍵錯誤，他們都請求該行的共享鎖。當(dāng)事務(wù)1回滾時(shí)，它釋放該行上的排他鎖，事務(wù)2和事務(wù)3獲得該行的共享鎖。此時(shí)，事務(wù)2和事務(wù)3就會出現(xiàn)死鎖：由于另一方持有共享鎖，雙方都不能獲得該行的排他鎖。

如果表中已經(jīng)包含鍵值為1的行，并且三個(gè)會話按順序執(zhí)行以下操作也會出現(xiàn)類似的情況：

Session1：

START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

Session2：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session3：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session1：

COMMIT;

事務(wù)1的第一個(gè)操作獲取該行的排他鎖。事務(wù)2和事務(wù)3的操作都會導(dǎo)致重復(fù)鍵錯誤，此時(shí)這兩個(gè)事務(wù)都請求該行的共享鎖。當(dāng)事務(wù)1提交時(shí)，它釋放該行上的排他鎖，并且授權(quán)事務(wù)2和事務(wù)3的共享鎖請求。此時(shí)，事務(wù)2和事務(wù)3就會出現(xiàn)死鎖：由于另一方持有共享鎖，雙方都不能獲得該行的排他鎖。

INSERT ... ON DUPLICATE KEY UPDATE：

不同于簡單的INSERT，當(dāng)重復(fù)鍵錯誤發(fā)生時(shí)，請求的是該行的排他鎖而不是共享鎖。如果是主鍵索引的值重復(fù)，則采用排他的記錄鎖；如果是唯一索引的值重復(fù)，則采用next-key鎖。

REPLACE：如果唯一鍵中沒有該值，和REPLACE操作和INSERT操作一樣。否則，需先獲得該行排他的next-key鎖。

INSERT INTO T SELECT ... FROM S WHERE ...：

對插入到T中的每一行設(shè)置排他的記錄鎖（沒有間隙鎖）。如果事務(wù)的隔離級別是讀已提交，或者啟用innodb_locks_unsafe_for_binlog并且事務(wù)的隔離級別不是SERIALZABLE，InnoDB會將S作為一致性讀進(jìn)行搜索（沒有鎖）。否則，InnoDB會對來自S的行設(shè)置共享的next-key鎖。如下情景中，InnoDB必須設(shè)置鎖：在使用基于語句的二進(jìn)制日志進(jìn)行回滾恢復(fù)時(shí)，每條SQL語句都必須以與原來完全相同的方式進(jìn)行。

CREATE TABLE ... SELECT ...：

執(zhí)行SELECT語句時(shí)使用共享的next-key 鎖，或者作為一致性讀取，類似于INSERT ... SELECT。當(dāng)SELECT用于如下語句中時(shí)，InnoDB會對滿足條件的S表中的行設(shè)置共享的next-key鎖：REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...)。

當(dāng)初始化表中AUTO_INCREMENT列時(shí)，InnoDB會在與AUTO_INCREMENT關(guān)聯(lián)的索引的最后上設(shè)置排他鎖。在innodb_autoinc_lock_mode=0的情況下，當(dāng)訪問auto-increment計(jì)數(shù)器時(shí)，InnoDB使用特殊的AUTO-INC表鎖模式，在該模式下當(dāng)前 SQL 語句的末尾獲取并持有鎖（而不是整個(gè)事務(wù)的結(jié)尾持有鎖）。當(dāng)某一事務(wù)持有AUTO-INC表鎖時(shí)，將阻塞其它事務(wù)往表中插入的操作。當(dāng)innodb_autoinc_lock_mode=1時(shí)，批量插入也會出現(xiàn)這種情況。當(dāng)innodb_autoinc_lock_mode=2時(shí)，不能使用表級鎖AUTO-INC。當(dāng)InnoDB獲取已初始化的AUTO_INCREMENT列的值時(shí)無需設(shè)置任何鎖。

如果在一個(gè)表上定義了一個(gè)外鍵約束，任何需要檢查約束條件的操作，如：插入，更新，刪除，都會對其檢查約束的過程中查看的記錄上設(shè)置共享的記錄鎖。InnoDB也會在約束失敗的情況下設(shè)置這些鎖。

LOCK TABLES：

設(shè)置表鎖，該鎖是由InnoDB層之上的MySQL層設(shè)置的。如果innodb_table_locks = 1(默認(rèn)值)和autocommit = 0,InnoDB能夠識別表鎖，InnoDB層之上的MySQL層能夠識別行級鎖。否則，InnoDB 的自動死鎖檢測無法檢測到涉及表鎖的死鎖。此外，因?yàn)樵谶@種情況下，更高的MySQL層識別不了行級鎖，所以，在另一個(gè)事務(wù)當(dāng)前持某表的行級鎖的情況下獲得該表的表鎖是有可能的。這并不會破壞事務(wù)的完整性。

LOCK TABLES：

如果innodb_table_locks=1（默認(rèn)值），那么使用LOCK TABLE鎖表時(shí)，每個(gè)表上都會有兩個(gè)表鎖。除了MySQL層上的表鎖，還需要InnoDB層的表鎖?？梢酝ㄟ^設(shè)置innodb_table_locks=0，避免獲取InnoDB層的表鎖。如果沒有獲得InnoDB層的表鎖，即使表中的一些記錄正被其他事務(wù)上鎖，LOCK TABLES操作依然可以完成。

在MySQL5.7中，如果innodb_table_locks=0，那么當(dāng)使用 LOCK TABLES ... WRITE給表顯示加鎖將不會起作用，當(dāng)讀操作或?qū)懖僮鲿r(shí)使用 LOCK TABLES ... WRITE給表隱式加鎖時(shí)（例如，通過觸發(fā)器）或使用LOCK TABLES ... READ時(shí)將會起作用。

當(dāng)事務(wù)提交或終止時(shí)，該事物將釋放其持有的所有InnoDB鎖。因此，在autocommit = 1的情況下使用LOCK TABLES沒有任何意義，因?yàn)楂@得的InnoDB表鎖會立即被釋放。

不能在事務(wù)執(zhí)行中鎖定其他表，因?yàn)長OCK TABLES會隱式地執(zhí)行COMMIT和UNLOCK TABLES從而釋放表鎖。

到此這篇關(guān)于InnoDB中不同SQL語句設(shè)置鎖的情況詳解的文章就介紹到這了,更多相關(guān)InnoDB的SQL語句設(shè)置鎖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論