前言
最近,同僚在生産上遇到一個 MySQL 死鎖的問題,于是在幫忙解決問題後,特意花了一周的時間,把 MySQL 所有的鎖都整理了一遍,今天就來一起聊聊 MySQL 鎖。
聲明:本文基于 MySQL 8.0.30 版本,InnoDB 引擎
MySQL 資料庫鎖設計的初衷是處理并發問題,保證資料安全。MySQL 資料庫鎖可以從下面 3 個次元進行劃分:
按照鎖的使用方式,MySQL 鎖可以分成共享鎖、排他鎖兩種;
根據加鎖的範圍,MySQL 鎖大緻可以分成全局鎖、表級鎖和行鎖三類;
從思想層面上看,MySQL 鎖可以分為悲觀鎖、樂觀鎖兩種;
我們會先講解共享鎖和排它鎖,然後講解全局鎖、表級鎖和行鎖,因為這三種類别的鎖中,有些是共享鎖,有些是排他鎖,最後,我們再講解 悲觀鎖和樂觀鎖。
1. 共享鎖&排他鎖
1.1 共享鎖
共享鎖,Share lock,也叫讀鎖。它是指當對象被鎖定時,允許其它事務讀取該對象,也允許其它事務從該對象上再次擷取共享鎖,但不能對該對象進行寫入。加鎖方式是:
# 方式1
select ... lock in share mode;
# 方式2
select ... for share;
如果事務 T1 在某對象持有共享(S)鎖,則事務 T2 需要再次擷取該對象的鎖時,會出現下面兩種情況:
如果 T2 擷取該對象的共享(S)鎖,則可以立即擷取鎖;
如果 T2 擷取該對象的排他(X)鎖,則無法擷取鎖;
為了更好的了解上述兩種情況,可以參照下面的執行順序流和執行個體圖:
給 user 表加共享鎖
加鎖線程 sessionA | 線程 B sessionB |
#開啟事務 begin; | |
#對 user 整張表加共享鎖 select * from user lock in share mode; | |
#擷取 user 表上的共享鎖 ok,select 操作成功執行 select * from user; | |
#擷取 user 表上的排他鎖失敗,操作被堵塞 delete from user where id = 1; | |
#送出事務 #user 表上的共享鎖被釋放 commit; | |
#擷取 user 表上的排他鎖成功,delete 操作執行 ok delete from user where id = 1; |
給 user 表 id=3 的行加共享鎖
加鎖線程 sessionA | 線程 B sessionB | 線程 C sessionC |
#開啟事務begin; | ||
#給 user 表 id=3 的行加共享鎖select * from userwhere id = 3 lock in share mode; | ||
#擷取 user 表 id=3 行上的共享鎖 ok#select 操作執行成功select * from user where id=3; | #擷取 user 表 id=3 行上的共享鎖 ok#select 操作執行成功select * from user where id=3; | |
#擷取 user 表 id=3 行上的排它鎖失敗#delete 操作被堵塞delete from user where id = 3; | #擷取 user 表 id=4 行上的排它鎖成功#delete 操作執行成功delete from user where id = 4; | |
#送出事務#user 表 id=3 的行上共享鎖被釋放commit; | ||
#擷取 user 表 id=3 行上的排它鎖成功#被堵塞的 delete 操作執行 okdelete from user where id = 3; |
通過上述兩個執行個體可以看出:
當共享鎖加在 user 表上,則其它事務可以再次擷取 user 表的共享鎖,其它事務再次擷取 user 表的排他鎖失敗,操作被堵塞;
當共享鎖加在 user 表 id=3 的行上,則其它事務可以再次擷取 user 表 id=3 行上的共享鎖,其它事務再次擷取 user 表 id=3 行上的排他鎖失敗,操作被堵塞,但是事務可以再次擷取 user 表 id!=3 行上的排他鎖;
1.2 排他鎖
排它鎖,Exclusive Lock,也叫寫鎖或者獨占鎖,主要是防止其它事務和目前加鎖事務鎖定同一對象。同一對象主要有兩層含義:
當排他鎖加在表上,則其它事務無法對該表進行 insert,update,delete,alter,drop 等更新操作;
當排他鎖加在表的行上,則其它事務無法對該行進行 insert,update,delete,alter,drop 等更新操作;
排它鎖加鎖方式為:
select ... for update;
為了更好地說明排他鎖,可以參照下面的執行順序流和執行個體圖:
給 user 表對象加排他鎖
加鎖線程 sessionA | 線程 B sessionB |
#開啟事務 begin; | |
#對 user 整張表加排他鎖 select * from user for update; | |
#擷取 user 表上的共享鎖 ok,select 執行成功 select * from user; | |
#擷取 user 表上的排他鎖失敗,操作被堵塞 delete from user where id=3; | |
#送出事務 #user 表上的排他被釋放 commit; | |
#擷取 user 表上的排他鎖成功,操作執行 ok delete from user where id = 1; |
給 user 表 id=3 的行對象加排他鎖
加鎖線程 sessionA | 線程 B sessionB | 線程 C sessionC |
#開啟事務 begin; | ||
#給 user 表 id=3 的行加排他鎖 select * from user where id = 3 for update; | ||
#擷取 user 表 id=3 行上的共享鎖 ok select * from user where id=3; | #擷取 user 表 id=3 行上的共享鎖 ok select * from user where id=3; | |
#擷取 user 表 id=3 行上的排它鎖失敗 delete from user where id = 3; | #擷取 user 表 id=4 行上的排它鎖成功 delete from user where id = 4; | |
#送出事務 #user 表 id=3 的行上排他鎖被釋放 commit; | ||
#擷取 user 表 id=3 行上的排它鎖成功 #被堵塞的 delete 操作執行 ok delete from user where id = 3; |
2. 全局鎖&表級鎖&行鎖
2.1 全局鎖
定義:全局鎖,顧名思義,就是對整個資料庫執行個體加鎖。它是粒度最大的鎖。
加鎖:在 MySQL 中,通過執行 flush tables with read lock 指令加全局鎖:
flush tables with read lock
指令執行完,整個資料庫就處于隻讀狀态了,其他線程執行以下操作,都會被阻塞:
資料更新語句被阻塞,包括 insert, update, delete 語句;
資料定義語句被阻塞,包括建表 create table,alter table、drop table 語句;
更新操作事務 commit 語句被阻塞;
釋放鎖:MySQl 釋放鎖有 2 種方式:
執行 unlock tables 指令unlock tables
加鎖的會話斷開,全局鎖也會被自動釋放
為了更好地說明全局鎖,可以參照下面的執行順序流和執行個體圖:
加鎖線程 sessionA | 線程 B sessionB |
flush tables with read lock; 加全局鎖 | |
select user 表 ok | select user 表 ok |
insert user 表堵塞 | insert user 表堵塞 |
delete user 表堵塞 | delete user 表堵塞 |
drop user 表堵塞 | drop user 表堵塞 |
alter user 表 堵塞 | alter user 表 堵塞 |
unlock tables;解鎖 | |
被堵塞的修改操作執行 ok | 被堵塞的修改操作執行 ok |
通過上述的執行個體可以看出,當加全局鎖時,庫下面所有的表都處于隻讀狀态,不管是目前事務還是其他事務,對于庫下面所有的表隻能讀,不能執行 insert,update,delete,alter,drop 等更新操作。
使用場景:全局鎖的典型使用場景是做全庫邏輯備份,在備份過程中整個庫完全處于隻讀狀态。如下圖:
假如在主庫上備份,備份期間,業務伺服器不能對資料庫執行更新操作,是以涉及到更新操作的業務就癱瘓了;
假如在從庫上備份,備份期間,從庫不能執行主庫同步過來的 binlog,會導緻主從延遲越來越大,如果做了讀寫分離,那麼從庫上擷取資料就會出現延時,影響業務;
從上述分析可以看出,使用全局鎖進行資料備份,不管是在主庫還是在從庫上進行備份操作,對業務總是不太友好。那不加鎖行不行?我們可以通過下面還錢轉賬的例子,看看不加鎖會不會出現問題:
備份前:賬戶 A 有 1000,賬戶 B 有 500
此時,發起邏輯備份
假如資料備份時不加鎖,此時,用戶端 A 發起一個還錢轉賬的操作:賬戶 A 往賬戶 B 轉 200
當賬戶 A 轉出 200 完成,賬戶 B 轉入 200 還未完成時,整個資料備份完成
如果用該備份資料做恢複,會發現賬戶 A 轉出了 200,賬戶 B 卻沒有對應的轉入記錄,這樣就會産生糾紛:A 說我賬戶少了 200, B 說我沒有收到,最後,A,B 誰都不幹。
既然不加鎖會産生錯誤,加全局鎖又會影響業務,那麼有沒有兩全其美的方式呢?
有,MySQL 官方自帶的邏輯備份工具 mysqldump,具體指令如下:
mysqldump –single-transaction
執行該指令,在備份資料之前會先啟動一個事務,來確定拿到一緻性視圖, 加上 MVCC 的支援,保證備份過程中資料是可以正常更新。但是,single-transaction 方法隻适用于庫中所有表都使用了事務引擎,如果有表使用了不支援事務的引擎,備份就隻能用 FTWRL 方法。
2.2 表級鎖
MySQL 表級鎖有兩種:
表鎖
中繼資料鎖(metadata lock,MDL)
2.2.1 表鎖
表鎖就是對整張表加鎖,包含讀鎖和寫鎖,由 MySQL Server 實作,表鎖需要顯示加鎖或釋放鎖,具體指令如下:
# 給表加寫鎖
lock tables tablename write;
# 給表加讀鎖
lock tables tablename read;
# 釋放鎖
unlock tables;
讀鎖:代表目前表為隻讀狀态,讀鎖是一種共享鎖。需要注意的是,讀鎖除了會限制其它線程的操作外,也會限制加鎖線程的行為,具體限制如下:
加鎖線程隻能對目前表進行讀操作,不能對目前表進行更新操作,不能對其它表進行所有操作;
其它線程隻能對目前表進行讀操作,不能對目前表進行更新操作,可以對其它表進行所有操作;
為了更好地說明讀鎖,可以參照下面的執行順序流和執行個體圖:
加鎖線程 sessionA | 線程 B sessionB |
#給 user 表加讀鎖 lock tables user read; | |
select user 表 ok | select user 表 ok |
insert user 表被拒絕 | insert user 表堵塞 |
insert address 表被拒絕 | insert address 表 ok |
select address 表被拒絕 | alter user 表堵塞 |
unlock tables; 釋放鎖 | |
被堵塞的修改操作執行 ok |
寫鎖:寫鎖是一種獨占鎖,需要注意的是,寫鎖除了會限制其它線程的操作外,也會限制加鎖線程的行為,具體限制如下:
加鎖線程對目前表能進行所有操作,不能對其它表進行任何操作;
其它線程不能對目前表進行任何操作,可以對其它表進行任何操作;
為了更好的說明寫鎖,可以參照下面的執行順序流和執行個體圖:
加鎖線程 sessionA | 線程 B sessionB |
#給 user 表加寫鎖 lock tables user write; | |
select user 表 ok | select user 表 ok |
insert user 表被拒絕 | insert user 表堵塞 |
insert address 表被拒絕 | insert address 表 ok |
select address 表被拒絕 | alter user 表堵塞 |
unlock tables; 釋放鎖 | |
堵塞在 user 表的上更新操作執行 ok |
2.2.2 MDL 中繼資料鎖
中繼資料鎖:metadata lock,簡稱 MDL,它是在 MySQL 5.5 版本引進的。中繼資料鎖不用像表鎖那樣顯式的加鎖和釋放鎖,而是在通路表時被自動加上,以保證讀寫的正确性。加鎖和釋放鎖規則如下:
MDL 讀鎖之間不互斥,也就是說,允許多個線程同時對加了 MDL 讀鎖的表進行 CRUD(增删改查)操作;
MDL 寫鎖,它和讀鎖、寫鎖都是互斥的,目的是用來保證變更表結構操作的安全性。也就是說,當對表結構進行變更時,會被預設加 MDL 寫鎖,是以,如果有兩個線程要同時給一個表加字段,其中一個要等另一個執行完才能開始執行。
MDL 讀寫鎖是在事務 commit 之後才會被釋放;
為了更好的說明 MDL 讀鎖規則,可以參照下面的順序執行流和執行個體圖:
加鎖線程 sessionA | 其它線程 sessionB |
開啟事務 begin; | |
select user 表,user 表會預設加上 MDL 讀鎖 | |
select user 表 ok | select user 表 ok |
insert user 表 ok | insert user 表 ok |
update user 表 ok | update user 表 ok |
delete user 表 ok | delete user 表 ok |
alter user 表,擷取 MDL 寫鎖失敗,操作被堵塞 | |
commit;送出事務,MDL 讀鎖被釋放 | |
被堵塞的修改操作執行 ok |
為了更好的說明 MDL 寫鎖規則,可以參照下面的順序執行流和執行個體圖:
加鎖線程 sessionA | 線程 B sessionB | 線程 C sessionC |
#開啟事務 begin; | ||
#user 表會預設加上 MDL 讀鎖 select user 表, | ||
select user 表 ok | select user 表 ok | select user 表 ok |
#擷取 MDL 寫鎖失敗 alter user 表操作被堵塞 | #擷取 MDL 讀鎖失敗 select * from user; | |
送出事務,MDL 讀鎖被釋放 | ||
#MDL 寫鎖被釋放 被堵塞的 alter user 操作執行 ok | ||
#被堵塞的 select 操作執行 ok |
2.2.3 意向鎖
由于 InnoDB 引擎支援多粒度鎖定,允許行鎖和表鎖共存,為了快速的判斷表中是否存在行鎖,InnoDB 推出了意向鎖。
意向鎖,Intention lock,它是一種表鎖,用來辨別事務打算在表中的行上擷取什麼類型的鎖。不同的事務可以在同一張表上擷取不同種類的意向鎖,但是第一個擷取表上意向排他(IX) 鎖的事務會阻止其它事務擷取該表上的任何 S 鎖 或 X 鎖。反之,第一個獲得表上意向共享鎖(IS) 的事務可防止其它事務擷取該表上的任何 X 鎖。
意向鎖通常有兩種類型:
意向共享鎖(IS),表示事務打算在表中的各個行上設定共享鎖。
意向排他鎖(IX),表示事務打算對表中的各個行設定排他鎖。
意向鎖是 InnoDB 自動加上的,加鎖時遵從下面兩個協定:
事務在擷取表中行的共享鎖之前,必須先擷取表上的 IS 鎖或更強的鎖。
事務在擷取表中行的排他鎖之前,必須先擷取表上的 IX 鎖。
為了更好地說明意向共享鎖,可以參照下面的順序執行流和執行個體圖:
加鎖線程 sessionA | 線程 B sessionB |
#開啟事務 begin; | |
#user 表 id=6 加共享行鎖 ,預設 user 表會 加上 IS 鎖 select * from user where id = 6 for share; | |
# 觀察 IS 鎖 select* from performance_schema.data_locks\G |
加鎖線程 sessionA | 線程 B sessionB |
#開啟事務 begin; | |
#user 表 id=6 加排他鎖,預設 user 表會 加上 IX 鎖 select * from user where id = 6 for update; | |
# 觀察 IX 鎖 select* from performance_schema.data_locks\G |
2.3 AUTO-INC 鎖
AUTO-INC 鎖是一種特殊的表級鎖,當表中有 AUTO_INCREMENT 的列時,如果向這張表插入資料時,InnoDB 會先擷取這張表的 AUTO-INC 鎖,等插入語句執行完成後,AUTO-INC 鎖會被釋放。
AUTO-INC 鎖可以使用 innodb_autoinc_lock_mode 變量來配置自增鎖的算法,innodb_autoinc_lock_mode 變量可以選擇三種值如下表:
innodb_autoinc_lock_mode | 含義 |
傳統鎖模式,采用 AUTO-INC 鎖 | |
1 | 連續鎖模式,采用輕量級鎖 |
2 | 交錯鎖模式(MySQL8 預設),AUTO-INC 和輕量級鎖之間靈活切換 |
為了更好的說明 AUTO-INC 鎖,可以參照下面的順序執行流和執行個體圖:
2.4 鎖的相容性
下面的圖表總結了表級鎖類型的相容性
X | IX | S | IS | |
X | 沖突 | 沖突 | 沖突 | 沖突 |
IX | 沖突 | 相容 | 沖突 | 相容 |
S | 沖突 | 沖突 | 相容 | 相容 |
IS | 沖突 | 相容 | 相容 | 相容 |
3. 行鎖
行鎖是針對資料表中行記錄的鎖。MySQL 的行鎖是在引擎層實作的,并不是所有的引擎都支援行鎖,比如,InnoDB 引擎支援行鎖而 MyISAM 引擎不支援。
InnoDB 引擎的行鎖主要有四類:
Record Lock:記錄鎖,是在索引記錄上加鎖;
Gap Lock:間隙鎖,鎖定一個範圍,但不包含記錄;
Next-key Lock:Gap Lock + Record Lock,鎖定一個範圍(Gap Lock 實作),并且鎖定記錄本身(Record Lock 實作);插入意向鎖;
3.1 Record Lock
Record Lock:記錄鎖,是針對索引記錄的鎖,鎖定的總是索引記錄。
例如,select id from user where id = 1 for update; for update 就顯式在索引 id 上加行鎖(排他鎖),防止其它任何事務 update 或 delete id=1 的行,但是對 user 表的 insert、alter、drop 操作還是可以正常執行。
為了更好的說明 Record Lock 鎖,可以參照下面的執行順序流和執行個體圖:
加鎖線程 sessionA | 線程 B sessionB | 線程 B sessionC |
#開啟事務 begin; | ||
給 user 表 id=1 加寫鎖 select id from user where id = 1 for update; | ||
update user set name = 'name121' where id = 1; | ||
檢視 InnoDB 螢幕中記錄鎖資料 show engine innodb status\G | ||
commit 送出事務 record lock 被釋放 | ||
被堵塞的 update 操作執行 ok |
3.2 Gap Lock
Gap Lock:間隙鎖,鎖住兩個索引記錄之間的間隙上,由 InnoDB 隐式添加。比如(1,3) 表示鎖住記錄 1 和記錄 3 之間的間隙,這樣記錄 2 就無法插入,間隙可能跨越單個索引值、多個索引值,甚至是空。
為了更好的說明 Gap Lock 間隙鎖,可以參照下面的順序執行流和執行個體圖:
加鎖線程 sessionA | 線程 B sessionB | 線程 C sessionC |
#開啟事務 begin; | ||
加鎖 select * from user where age = 10 for share; | ||
insert into user(id,age) values(2,20); | ||
#檢視 InnoDB 螢幕中記錄鎖資料 show engine innodb status\G | ||
commit 送出事務 Gap Lock 被釋放 | ||
被堵塞的 insert 操作執行 ok |
上圖中,事務 A(sessionA)在加共享鎖的時候産生了間隙鎖(Gap Lock),事務 B(sessionB)對間隙中進行 insert/update 操作,需要先擷取排他鎖(X),導緻阻塞。事務 C(sessionC)通過"show engine innodb status\G" 指令可以檢視到間隙鎖的存在。需要說明的,間隙鎖隻是鎖住間隙内部的範圍,在間隙外的 insert/update 操作不會受影響。
Gap Lock 鎖,隻存在于可重複讀隔離級别,目的是為了解決可重複讀隔離級别下幻讀的現象。
3.3 Next-Key Lock
Next-Key 鎖,稱為臨鍵鎖,它是 Record Lock + Gap Lock 的組合,用來鎖定一個範圍,并且鎖定記錄本身鎖,它是一種左開右閉的範圍,可以用符号表示為:(a,b]。
為了更好的說明 Next-Key Lock 間隙鎖,可以參照下面的順序執行流和執行個體圖:
加鎖線程 sessionA | 線程 B sessionB | 線程 C sessionC | 線程 D sessionD |
#開啟事務 begin; | |||
加鎖 select * from user where age = 10 for share; | |||
#擷取鎖失敗,insert 操作被堵塞 insert into user(id,age) values(2,20); | |||
update user set name='name1' where age = 10; | #檢視 InnoDB 螢幕中記錄鎖資料 show engine innodb status\G | ||
送出事務 Gap Lock 被釋放 commit | |||
被堵塞的 insert 操作執行 ok | 被堵塞的 update 操作執行 ok |
上圖中,事務 A(sessionA)在加共享鎖的時候産生了間隙鎖(Gap Lock),事務 B(sessionB)對間隙中進行 insert 操作,需要先擷取排他鎖(X),導緻阻塞。事務 C(sessionC)對間隙中進行 update 操作,需要先擷取排他鎖(X),導緻阻塞。事務 D(sessionD)通過"show engine innodb status\G" 指令可以檢視到間隙鎖的存在。需要說明的,間隙鎖隻是鎖住間隙内部的範圍,在間隙外的 insert/update 操作不會受影響。
3.4 Insert Intention Lock
插入意向鎖,它是一種特殊的間隙鎖,特指插入操作産生的間隙鎖。
為了更好的說明 Insert Intention Lock 鎖,可以參照下面的順序執行流和執行個體圖:
加鎖線程 sessionA | 線程 B sessionB | 線程 C sessionC |
#開啟事務 begin; | ||
加鎖 select * from user where age = 10 for share; | ||
#擷取鎖失敗,insert 操作被堵塞 insert into user(id,age) values(2,20); | ||
#檢視 InnoDB 螢幕中記錄鎖資料 show engine innodb status\G | ||
commit 送出事務 Gap Lock 被釋放 | ||
#被堵塞的 insert 操作執行 ok insert into user(id,age) values(2,20); |
4. 樂觀鎖&悲觀鎖
在 MySQL 中,無論是悲觀鎖還是樂觀鎖,都是人們對概念的一種思想抽象,它們本身還是利用 MySQL 提供的鎖機制來實作的。其實,除了在 MySQL 資料,像 Java 語言裡面也有樂觀鎖和悲觀鎖的概念。
悲觀鎖,可以了解成:在對任意記錄進行修改前,先嘗試為該記錄加上排他鎖(exclusive locking),采用的是先擷取鎖再操作資料的政策,可能會産生死鎖;
樂觀鎖,是相對悲觀鎖而言,一般不會利用資料庫的鎖機制,而是采用類似版本号比較之類的操作,是以樂觀鎖不會産生死鎖的問題;
5. 死鎖和死鎖檢測
當并發系統中不同線程出現循環資源依賴,涉及的線程都在等待别的線程釋放資源時,就會導緻這幾個線程都進入無限等待的狀态,稱為死鎖。可以通過下面的指令檢視死鎖
show engine innodb status\G
當出現死鎖以後,有兩種政策:
一種政策是,直接進入等待,直到逾時。這個逾時時間可以通過參數 innodb_lock_wait_timeout 來設定,InnoDB 中 innodb_lock_wait_timeout 的預設值是 50s。
另一種政策是,發起死鎖檢測,發現死鎖後,主動復原死鎖鍊條中的某一個事務,讓其它事務得以繼續執行。将參數 innodb_deadlock_detect 設定為 on,表示開啟死鎖檢測。
6. 總結
本文基于 MySQL 8.0.30 版本和 InnoDB 引擎,對 MySQL 中的鎖進行了講解,每種鎖都有其特定的使用場景。作為經常和 MySQL 打交道的 Java 程式員來說,對 MySQL 鎖了解的越深,越可以幫助我們更好的去寫出高性能的 SQL 語句。