您好,登錄后才能下訂單哦!
這篇文章主要介紹了MySQL鎖類型和加鎖原理是什么,具有一定借鑒價值,需要的朋友可以參考下。希望大家閱讀完這篇文章后大有收獲。下面讓小編帶著大家一起了解一下。
首先對mysql鎖進行劃分:
表級鎖是 MySQL 鎖中粒度最大的一種鎖,表示當前的操作對整張表加鎖,資源開銷比行鎖少,不會出現死鎖的情況,但是發生鎖沖突的概率很大。被大部分的mysql引擎支持,MyISAM和InnoDB都支持表級鎖,但是InnoDB默認的是行級鎖。
表鎖由 MySQL Server 實現,一般在執行 DDL 語句時會對整個表進行加鎖,比如說 ALTER TABLE 等操作。在執行 SQL 語句時,也可以明確指定對某個表進行加鎖。
表鎖使用的是一次性鎖技術,也就是說,在會話開始的地方使用 lock 命令將后續需要用到的表都加上鎖,在表釋放前,只能訪問這些加鎖的表,不能訪問其他表,直到最后通過 unlock tables 釋放所有表鎖。
除了使用 unlock tables 顯示釋放鎖之外,會話持有其他表鎖時執行lock table 語句會釋放會話之前持有的鎖;會話持有其他表鎖時執行 start transaction 或者 begin 開啟事務時,也會釋放之前持有的鎖。
共享鎖用法:
LOCK TABLE table_name [ AS alias_name ] READ復制代碼
排它鎖用法:
LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE復制代碼
解鎖用法:
unlock tables;復制代碼
行級鎖是Mysql中鎖定粒度最細的一種鎖,表示只針對當前操作的行進行加鎖。行級鎖能大大減少數據庫操作的沖突。其加鎖粒度最小,但加鎖的開銷也最大。有可能會出現死鎖的情況。 行級鎖按照使用方式分為共享鎖和排他鎖。
不同存儲引擎的行鎖實現不同,后續沒有特別說明,則行鎖特指 InnoDB 實現的行鎖。
在了解 InnoDB 的加鎖原理前,需要對其存儲結構有一定的了解。InnoDB 是聚簇索引,也就是 B+樹的葉節點既存儲了主鍵索引也存儲了數據行。而 InnoDB 的二級索引的葉節點存儲的則是主鍵值,所以通過二級索引查詢數據時,還需要拿對應的主鍵去聚簇索引中再次進行查詢。關于MySQL索引的詳細知識可以查看《MySQL索引底層數據結構與算法》。
下面以兩條 SQL 的執行為例,講解一下 InnoDB 對于單行數據的加鎖原理。
update user set age = 10 where id = 49; update user set age = 10 where name = 'Tom';復制代碼
第一條 SQL 使用主鍵索引來查詢,則只需要在 id = 49 這個主鍵索引上加上寫鎖;
第二條 SQL 則使用二級索引來查詢,則首先在 name = Tom 這個索引上加寫鎖,然后由于使用 InnoDB 二級索引還需再次根據主鍵索引查詢,所以還需要在 id = 49 這個主鍵索引上加寫鎖,如上圖所示。
也就是說使用主鍵索引需要加一把鎖,使用二級索引需要在二級索引和主鍵索引上各加一把鎖。
根據索引對單行數據進行更新的加鎖原理了解了,那如果更新操作涉及多個行呢,比如下面 SQL 的執行場景。
update user set age = 10 where id > 49;復制代碼
這種場景下的鎖的釋放較為復雜,有多種的優化方式,我對這塊暫時還沒有了解,還請知道的小伙伴在下方留言解釋。
頁級鎖是MySQL中鎖定粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快,但沖突多,行級沖突少,但速度慢。所以取了折衷的頁級,一次鎖定相鄰的一組記錄。BDB支持頁級鎖。
共享鎖又稱讀鎖,是讀取操作創建的鎖。其他用戶可以并發讀取數據,但任何事務都不能對數據進行修改(獲取數據上的排他鎖),直到已釋放所有共享鎖。
如果事務T對數據A加上共享鎖后,則其他事務只能對A再加共享鎖,不能加排他鎖。獲準共享鎖的事務只能讀數據,不能修改數據。
SELECT ... LOCK IN SHARE MODE;
在查詢語句后面增加LOCK IN SHARE MODE
,Mysql會對查詢結果中的每行都加共享鎖,當沒有其他線程對查詢結果集中的任何一行使用排他鎖時,可以成功申請共享鎖,否則會被阻塞。其他線程也可以讀取使用了共享鎖的表,而且這些線程讀取的是同一個版本的數據。
排他鎖又稱寫鎖,如果事務T對數據A加上排他鎖后,則其他事務不能再對A加任任何類型的封鎖。獲準排他鎖的事務既能讀數據,又能修改數據。
SELECT ... FOR UPDATE;
在查詢語句后面增加FOR UPDATE
,Mysql會對查詢結果中的每行都加排他鎖,當沒有其他線程對查詢結果集中的任何一行使用排他鎖時,可以成功申請排他鎖,否則會被阻塞。
在數據庫的鎖機制中介紹過,數據庫管理系統(DBMS)中的并發控制的任務是確保在多個事務同時存取數據庫中同一數據時不破壞事務的隔離性和統一性以及數據庫的統一性。
樂觀并發控制(樂觀鎖)和悲觀并發控制(悲觀鎖)是并發控制主要采用的技術手段。
無論是悲觀鎖還是樂觀鎖,都是人們定義出來的概念,可以認為是一種思想。其實不僅僅是關系型數據庫系統中有樂觀鎖和悲觀鎖的概念,像memcache、hibernate、tair等都有類似的概念。
針對于不同的業務場景,應該選用不同的并發控制方式。所以,不要把樂觀并發控制和悲觀并發控制狹義的理解為DBMS中的概念,更不要把他們和數據中提供的鎖機制(行鎖、表鎖、排他鎖、共享鎖)混為一談。其實,在DBMS中,悲觀鎖正是利用數據庫本身提供的鎖機制來實現的。
在關系數據庫管理系統里,悲觀并發控制(又名“悲觀鎖”,Pessimistic Concurrency Control,縮寫“PCC”)是一種并發控制的方法。它可以阻止一個事務以影響其他用戶的方式來修改數據。如果一個事務執行的操作對某行數據應用了鎖,那只有當這個事務把鎖釋放,其他事務才能夠執行與該鎖沖突的操作。悲觀并發控制主要用于數據爭用激烈的環境,以及發生并發沖突時使用鎖保護數據的成本要低于回滾事務的成本的環境中。
悲觀鎖,正如其名,它指的是對數據被外界(包括本系統當前的其他事務,以及來自外部系統的事務處理)修改持保守態度(悲觀),因此,在整個數據處理過程中,將數據處于鎖定狀態。 悲觀鎖的實現,往往依靠數據庫提供的鎖機制 (也只有數據庫層提供的鎖機制才能真正保證數據訪問的排他性,否則,即使在本系統中實現了加鎖機制,也無法保證外部系統不會修改數據)
悲觀鎖實際上是采取了“先取鎖在訪問”的策略,為數據的處理安全提供了保證,但是在效率方面,由于額外的加鎖機制產生了額外的開銷,并且增加了死鎖的機會。并且降低了并發性;當一個事物所以一行數據的時候,其他事物必須等待該事務提交之后,才能操作這行數據。
在關系數據庫管理系統里,樂觀并發控制(又名“樂觀鎖”,Optimistic Concurrency Control,縮寫“OCC”)是一種并發控制的方法。它假設多用戶并發的事務在處理時不會彼此互相影響,各事務能夠在不產生鎖的情況下處理各自影響的那部分數據。在提交數據更新之前,每個事務會先檢查在該事務讀取數據后,有沒有其他事務又修改了該數據。如果其他事務有更新的話,正在提交的事務會進行回滾。
樂觀鎖( Optimistic Locking ) 相對悲觀鎖而言,樂觀鎖假設認為數據一般情況下不會造成沖突,所以在數據進行提交更新的時候,才會正式對數據的沖突與否進行檢測,如果發現沖突了,則讓返回用戶錯誤的信息,讓用戶決定如何去做。
相對于悲觀鎖,在對數據庫進行處理的時候,樂觀鎖并不會使用數據庫提供的鎖機制。一般的實現樂觀鎖的方式就是記錄數據版本。
數據版本,為數據增加的一個版本標識。當讀取數據時,將版本標識的值一同讀出,數據每更新一次,同時對版本標識進行更新。當我們提交更新的時候,判斷數據庫表對應記錄的當前版本信息與第一次取出來的版本標識進行比對,如果數據庫表當前版本號與第一次取出來的版本標識值相等,則予以更新,否則認為是過期數據。
樂觀并發控制相信事務之間的數據競爭(data race)的概率是比較小的,因此盡可能直接做下去,直到提交的時候才去鎖定,所以不會產生任何鎖和死鎖。但如果直接簡單這么做,還是有可能會遇到不可預期的結果,例如兩個事務都讀取了數據庫的某一行,經過修改以后寫回數據庫,這時就遇到了問題。
由于表鎖和行鎖雖然鎖定范圍不同,但是會相互沖突。所以當你要加表鎖時,勢必要先遍歷該表的所有記錄,判斷是否加有排他鎖。這種遍歷檢查的方式顯然是一種低效的方式,MySQL 引入了意向鎖,來檢測表鎖和行鎖的沖突。
意向鎖也是表級鎖,也可分為讀意向鎖(IS 鎖)和寫意向鎖(IX 鎖)。當事務要在記錄上加上讀鎖或寫鎖時,要首先在表上加上意向鎖。這樣判斷表中是否有記錄加鎖就很簡單了,只要看下表上是否有意向鎖就行了。
意向鎖之間是不會產生沖突的,也不和 AUTO_INC 表鎖沖突,它只會阻塞表級讀鎖或表級寫鎖,另外,意向鎖也不會和行鎖沖突,行鎖只會和行鎖沖突。
意向鎖是InnoDB自動加的,不需要用戶干預。
對于insert、update、delete,InnoDB會自動給涉及的數據加排他鎖(X);
對于一般的Select語句,InnoDB不會加任何鎖,事務可以通過以下語句給顯示加共享鎖或排他鎖。
意向共享鎖(IS):表示事務準備給數據行加入共享鎖,也就是說一個數據行加共享鎖前必須先取得該表的IS鎖
意向排他鎖(IX):類似上面,表示事務準備給數據行加入排他鎖,說明事務在一個數據行加排他鎖前必須先取得該表的IX鎖。
記錄鎖是最簡單的行鎖,并沒有什么好說的。上邊描述 InnoDB 加鎖原理中的鎖就是記錄鎖,只鎖住 id = 49 或者 name = 'Tom' 這一條記錄。
當 SQL 語句無法使用索引時,會進行全表掃描,這個時候 MySQL 會給整張表的所有數據行加記錄鎖,再由 MySQL Server 層進行過濾。但是,在 MySQL Server 層進行過濾的時候,如果發現不滿足 WHERE 條件,會釋放對應記錄的鎖。這樣做,保證了最后只會持有滿足條件記錄上的鎖,但是每條記錄的加鎖操作還是不能省略的。
所以更新操作必須要根據索引進行操作,沒有索引時,不僅會消耗大量的鎖資源,增加數據庫的開銷,還會極大的降低了數據庫的并發性能。
當我們使用范圍條件而不是相等條件檢索數據,并請求共享或排他鎖時,InnoDB會給符合條件的已有數據記錄的索引項加鎖;對于鍵值在條件范圍內但并不存在的記錄,InnoDB 也會對這個“間隙”加鎖,這種鎖機制就是所謂的間隙鎖。
間隙鎖是鎖索引記錄中的間隔,或者第一條索引記錄之前的范圍,又或者最后一條索引記錄之后的范圍。
間隙鎖在 InnoDB 的唯一作用就是防止其它事務的插入操作,以此來達到防止幻讀的發生,所以間隙鎖不分什么共享鎖與排他鎖。
要禁止間隙鎖,可以把隔離級別降為讀已提交,或者開啟參數 innodb_locks_unsafe_for_binlog
show variables like 'innodb_locks_unsafe_for_binlog';復制代碼
innodb_locks_unsafe_for_binlog
:默認
值為OFF,即啟用間隙鎖。因為此參數是只讀模式,如果想要禁用間隙鎖,需要修改 my.cnf(windows是my.ini) 重新啟動才行。
# 在 my.cnf 里面的[mysqld]添加 [mysqld] innodb_locks_unsafe_for_binlog = 1復制代碼
測試環境:
MySQL5.7,InnoDB,默認的隔離級別(RR)
示例表:
CREATE TABLE `my_gap` ( `id` int(1) NOT NULL AUTO_INCREMENT, `name` varchar(8) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `my_gap` VALUES ('1', '張三');INSERT INTO `my_gap` VALUES ('5', '李四');INSERT INTO `my_gap` VALUES ('7', '王五');INSERT INTO `my_gap` VALUES ('11', '趙六');復制代碼
在進行測試之前,我們先看看 my_gap 表中存在的隱藏間隙:
/* 開啟事務1 */BEGIN;/* 查詢 id = 5 的數據并加記錄鎖 */SELECT * FROM `my_gap` WHERE `id` = 5 FOR UPDATE;/* 延遲30秒執行,防止鎖釋放 */SELECT SLEEP(30); # 注意:以下的語句不是放在一個事務中執行,而是分開多次執行,每次事務中只有一條添加語句/* 事務2插入一條 name = '杰倫' 的數據 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '杰倫'); # 正常執行/* 事務3插入一條 name = '學友' 的數據 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '學友'); # 正常執行/* 提交事務1,釋放事務1的鎖 */COMMIT;復制代碼
上述案例,由于主鍵是唯一索引,而且只使用一個索引查詢,并且只鎖定了一條記錄,所以只會對 id = 5
的數據加上記錄鎖(行鎖),而不會產生間隙鎖。
恢復初始化的4條記錄,繼續在 id 唯一索引列上做以下測試:
/* 開啟事務1 */BEGIN;/* 查詢 id 在 7 - 11 范圍的數據并加記錄鎖 */SELECT * FROM `my_gap` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;/* 延遲30秒執行,防止鎖釋放 */SELECT SLEEP(30); # 注意:以下的語句不是放在一個事務中執行,而是分開多次執行,每次事務中只有一條添加語句/* 事務2插入一條 id = 3,name = '思聰3' 的數據 */INSERT INTO `my_gap` (`id`, `name`) VALUES (3, '思聰3'); # 正常執行/* 事務3插入一條 id = 4,name = '思聰4' 的數據 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '思聰4'); # 正常執行/* 事務4插入一條 id = 6,name = '思聰6' 的數據 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '思聰6'); # 阻塞/* 事務5插入一條 id = 8, name = '思聰8' 的數據 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '思聰8'); # 阻塞/* 事務6插入一條 id = 9, name = '思聰9' 的數據 */INSERT INTO `my_gap` (`id`, `name`) VALUES (9, '思聰9'); # 阻塞/* 事務7插入一條 id = 11, name = '思聰11' 的數據 */INSERT INTO `my_gap` (`id`, `name`) VALUES (11, '思聰11'); # 阻塞/* 事務8插入一條 id = 12, name = '思聰12' 的數據 */INSERT INTO `my_gap` (`id`, `name`) VALUES (12, '思聰12'); # 正常執行/* 提交事務1,釋放事務1的鎖 */COMMIT;復制代碼
從上面可以看到,(5,7]、(7,11] 這兩個區間,都不可插入數據,其它區間,都可以正常插入數據。所以可以得出結論:當我們給(5,7] 這個區間加鎖的時候,會鎖住(5,7]、(7,11] 這兩個區間。
恢復初始化的4條記錄,我們再來測試如果鎖住不存在的數據時,會如何?
/* 開啟事務1 */BEGIN;/* 查詢 id = 3 這一條不存在的數據并加記錄鎖 */SELECT * FROM `my_gap` WHERE `id` = 3 FOR UPDATE;/* 延遲30秒執行,防止鎖釋放 */SELECT SLEEP(30); # 注意:以下的語句不是放在一個事務中執行,而是分開多次執行,每次事務中只有一條添加語句/* 事務2插入一條 id = 3,name = '小張' 的數據 */INSERT INTO `my_gap` (`id`, `name`) VALUES (2, '小張'); # 阻塞/* 事務3插入一條 id = 4,name = '小白' 的數據 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '小白'); # 阻塞/* 事務4插入一條 id = 6,name = '小東' 的數據 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '小東'); # 正常執行/* 事務5插入一條 id = 8, name = '大羅' 的數據 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '大羅'); # 正常執行/* 提交事務1,釋放事務1的鎖 */COMMIT;復制代碼
從上面可以看出,指定查詢某一條記錄時,如果這條記錄不存在,會產生間隙鎖。
示例表:id 是主鍵,在 number 上,建立了一個普通索引。
# 注意:number 不是唯一值CREATE TABLE `my_gap1` ( `id` int(1) NOT NULL AUTO_INCREMENT, `number` int(1) NOT NULL COMMENT '數字', PRIMARY KEY (`id`), KEY `number` (`number`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO `my_gap1` VALUES (1, 1);INSERT INTO `my_gap1` VALUES (5, 3);INSERT INTO `my_gap1` VALUES (7, 8);INSERT INTO `my_gap1` VALUES (11, 12);復制代碼
在進行測試之前,我們先來看看 my_gap1 表中 number 索引存在的隱藏間隙:
我們執行以下的事務(事務1最后提交),分別執行下面的語句:
/* 開啟事務1 */BEGIN;/* 查詢 number = 3 的數據并加記錄鎖 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延遲30秒執行,防止鎖釋放 */SELECT SLEEP(30); # 注意:以下的語句不是放在一個事務中執行,而是分開多次執行,每次事務中只有一條添加語句/* 事務2插入一條 number = 0 的數據 */INSERT INTO `my_gap1` (`number`) VALUES (0); # 正常執行/* 事務3插入一條 number = 1 的數據 */INSERT INTO `my_gap1` (`number`) VALUES (1); # 被阻塞/* 事務4插入一條 number = 2 的數據 */INSERT INTO `my_gap1` (`number`) VALUES (2); # 被阻塞/* 事務5插入一條 number = 4 的數據 */INSERT INTO `my_gap1` (`number`) VALUES (4); # 被阻塞/* 事務6插入一條 number = 8 的數據 */INSERT INTO `my_gap1` (`number`) VALUES (8); # 正常執行/* 事務7插入一條 number = 9 的數據 */INSERT INTO `my_gap1` (`number`) VALUES (9); # 正常執行/* 事務8插入一條 number = 10 的數據 */INSERT INTO `my_gap1` (`number`) VALUES (10); # 正常執行/* 提交事務1 */COMMIT;復制代碼
我們會發現有些語句可以正常執行,有些語句被阻塞來。查看表中的數據:
這里可以看到,number(1,8) 的間隙中,插入語句都被阻塞來,而不在這個范圍內的語句,正常執行,這就是因為有間隙鎖的原因。
我們再進行以下測試,這里將數據還原成初始化那樣
/* 開啟事務1 */BEGIN;/* 查詢 number = 3 的數據并加記錄鎖 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延遲30秒執行,防止鎖釋放 */SELECT SLEEP(30);/* 事務1插入一條 id = 2, number = 1 的數據 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (2, 1); # 阻塞/* 事務2插入一條 id = 3, number = 2 的數據 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (3, 2); # 阻塞/* 事務3插入一條 id = 6, number = 8 的數據 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (6, 8); # 阻塞/* 事務4插入一條 id = 8, number = 8 的數據 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (8, 8); # 正常執行/* 事務5插入一條 id = 9, number = 9 的數據 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (9, 9); # 正常執行/* 事務6插入一條 id = 10, number = 12 的數據 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (10, 12); # 正常執行/* 事務7修改 id = 11, number = 12 的數據 */UPDATE `my_gap1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞/* 提交事務1 */COMMIT;復制代碼
查看表中的數據;
這里有一個奇怪的現象:
這是為什么?我們來看看下面的圖:
從圖中庫看出,當 number 相同時,會根據主鍵 id 來排序
臨鍵鎖,是記錄鎖(行鎖)與間隙鎖的組合,它的鎖范圍,即包含索引記錄,又包含索引區間。它指的是加在某條記錄以及這條記錄前面間隙上的鎖。假設一個索引包含 15、18、20 ,30,49,50 這幾個值,可能的 Next-key 鎖如下:
(-∞, 15],(15, 18],(18, 20],(20, 30],(30, 49],(49, 50],(50, +∞)復制代碼
通常我們都用這種左開右閉區間來表示 Next-key 鎖,其中,圓括號表示不包含該記錄,方括號表示包含該記錄。前面四個都是 Next-key 鎖,最后一個為間隙鎖。和間隙鎖一樣,在 RC 隔離級別下沒有 Next-key 鎖,只有 RR 隔離級別才有。還是之前的例子,如果 id 不是主鍵,而是二級索引,且不是唯一索引,那么這個 SQL 在 RR 隔離級別下就會加如下的 Next-key 鎖 (30, 49](49, 50)
此時如果插入一條 id = 31 的記錄將會阻塞住。之所以要把 id = 49 前后的間隙都鎖住,仍然是為了解決幻讀問題,因為 id 是非唯一索引,所以 id = 49 可能會有多條記錄,為了防止再插入一條 id = 49 的記錄。
注意:臨鍵鎖的主要目的,也是為了避免幻讀(Phantom Read)。如果把事務隔離級別降級為 RC,臨鍵鎖則也會失效。
插入意向鎖是一種特殊的間隙鎖(簡稱II GAP)表示插入的意向,只有在 INSERT 的時候才會有這個鎖。注意,這個鎖雖然也叫意向鎖,但是和上面介紹的表級意向鎖是兩個完全不同的概念,不要搞混了。
插入意向鎖和插入意向鎖之間互不沖突,所以可以在同一個間隙中有多個事務同時插入不同索引的記錄。譬如在例子中,id = 30 和 id = 49 之間如果有兩個事務要同時分別插入 id = 32 和 id = 33 是沒問題的,雖然兩個事務都會在 id = 30 和 id = 50 之間加上插入意向鎖,但是不會沖突。
插入意向鎖只會和間隙鎖或 Next-key 鎖沖突,正如上面所說,間隙鎖唯一的作用就是防止其他事務插入記錄造成幻讀,正是由于在執行 INSERT 語句時需要加插入意向鎖,而插入意向鎖和間隙鎖沖突,從而阻止了插入操作的執行。
插入意向鎖的作用:
AUTO_INC 鎖又叫自增鎖(一般簡寫成 AI 鎖),是一種表鎖,當表中有自增列(AUTO_INCREMENT)時出現。當插入表中有自增列時,數據庫需要自動生成自增值,它會先為該表加 AUTO_INC 表鎖,阻塞其他事務的插入操作,這樣保證生成的自增值肯定是唯一的。AUTO_INC 鎖具有如下特點:
使用AUTO_INCREMENT
函數實現自增操作,自增幅度通過 auto_increment_offset
和auto_increment_increment
這2個參數進行控制:
通過使用last_insert_id()函數可以獲得最后一個插入的數字
select last_insert_id();復制代碼
首先insert大致上可以分成三類:
如果存在自增字段,MySQL 會維護一個自增鎖,和自增鎖相關的一個參數為(5.1.22 版本后加入) innodb_autoinc_lock_mode
,可以設定 3 值:
MyISam引擎均為 traditonal,每次均會進行表鎖。但是InnoDB引擎會視參數不同產生不同的鎖,默認為 1:consecutive。
show variables like 'innodb_autoinc_lock_mode';復制代碼
innodb_autoinc_lock_mode
為 0 時,也就是 traditional 級別。該自增鎖時表鎖級別,且必須等待當前 SQL 執行完畢后或者回滾才會釋放,在高并發的情況下可想而知自增鎖競爭時比較大的。
innodb_autoinc_lock_mode 為 1 時,也就是 consecutive 級別。這是如果是單一的 insert SQL,可以立即獲得該鎖,并立即釋放,而不必等待當前SQL執行完成(除非在其它事務中已經有 session 獲取了自增鎖)。另外當SQL是一些批量 insert SQL 時,比如 insert into ... select ...
, load data
, replace ... select ...
時,這時還是表級鎖,可以理解為退化為必須等待當前 SQL 執行完才釋放。可以認為,該值為 1 時相對比較輕量級的鎖,也不會對復制產生影響,唯一的缺陷是產生自增值不一定是完全連續的。
innodb_autoinc_lock_mode 為 2 時,也就是 interleaved 級別。所有 insert 種類的 SQL 都可以立馬獲得鎖并釋放,這時的效率最高。但是會引入一個新的問題:當 binlog_format 為 statement 時,這是復制沒法保證安全,因為批量的 insert,比如 insert ... select ...
語句在這個情況下,也可以立馬獲取到一大批的自增 id 值,不必鎖整個表, slave 在回放這個 SQL 時必然會產生錯亂。
如果你的二進制文件格式是mixed | row 那么這三個值中的任何一個對于你來說都是復制安全的。
由于現在mysql已經推薦把二進制的格式設置成row,所以在binlog_format不是statement的情況下最好是innodb_autoinc_lock_mode=2 這樣可能知道更好的性能。
鎖的模式有:讀意向鎖,寫意向鎖,讀鎖,寫鎖和自增鎖(auto_inc)。
IS | IX | S | X | AI | |
---|---|---|---|---|---|
IS | 兼容 | 兼容 | 兼容 | 兼容 | |
IX | 兼容 | 兼容 | 兼容 | ||
S | 兼容 | 兼容 | |||
X | |||||
AI | 兼容 | 兼容 |
總結起來有下面幾點:
根據鎖的粒度可以把鎖細分為表鎖和行鎖,行鎖根據場景的不同又可以進一步細分,依次為 Next-Key Lock,Gap Lock 間隙鎖,Record Lock 記錄鎖和插入意向 GAP 鎖。
不同的鎖鎖定的位置是不同的,比如說記錄鎖只鎖住對應的記錄,而間隙鎖鎖住記錄和記錄之間的間隔,Next-Key Lock 則所屬記錄和記錄之前的間隙。不同類型鎖的鎖定范圍大致如下圖所示。
RECORD | GAP | NEXT-KEY | II GAP | |
---|---|---|---|---|
RECORD | 兼容 | 兼容 | ||
GAP | 兼容 | 兼容 | 兼容 | 兼容 |
NEXT-KEY | 兼容 | 兼容 | ||
II GAP | 兼容 | 兼容 |
其中,第一行表示已有的鎖,第一列表示要加的鎖。插入意向鎖較為特殊,所以我們先對插入意向鎖做個總結,如下:
其他類型的鎖的規則較為簡單:
記錄鎖和記錄鎖沖突,Next-key 鎖和 Next-key 鎖沖突,記錄鎖和 Next-key 鎖沖突;
感謝你能夠認真閱讀完這篇文章,希望小編分享MySQL鎖類型和加鎖原理是什么內容對大家有幫助,同時也希望大家多多支持億速云,關注億速云行業資訊頻道,遇到問題就找億速云,詳細的解決方法等著你來學習!
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。