InnoDB中不同SQL語句設(shè)置鎖的情況詳解
前言
在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)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Mysql實(shí)現(xiàn)null值排在最前/最后的方法示例
這篇文章主要給大家介紹了關(guān)于Mysql實(shí)現(xiàn)null值排在最前/最后的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-02-02CentOS7下 MySQL定時(shí)自動備份的實(shí)現(xiàn)方法
這篇文章主要介紹了CentOS7 下MySQL定時(shí)自動備份的實(shí)現(xiàn)方法,主要實(shí)現(xiàn)了數(shù)據(jù)庫備份,清理過期備份文件功能,需要的朋友可以參考下2019-12-12