一篇文章搞懂MySQL加鎖機(jī)制
前言
在數(shù)據(jù)庫中設(shè)計(jì)鎖的目的是為了處理并發(fā)問題,在并發(fā)對資源進(jìn)行訪問時,數(shù)據(jù)庫要合理控制對資源的訪問規(guī)則。
而鎖就是用來實(shí)現(xiàn)這些訪問規(guī)則的一個數(shù)據(jù)結(jié)構(gòu)。
在對數(shù)據(jù)并發(fā)操作時,沒有鎖可能會引起數(shù)據(jù)的不一致,導(dǎo)致更新丟失。
鎖的分類
樂觀鎖和悲觀鎖
樂觀鎖: 對于出現(xiàn)更新丟失的可能性比較樂觀,先認(rèn)為不會出現(xiàn)更新丟失,在最后更新數(shù)據(jù)時進(jìn)行比較。
CREATE TABLE `t` ( `id` int(11) NOT NULL, `value` int(11) DEFAULT NULL, `version` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB ; insert into t values(1,1,1); select id,value,version from t where id=1; update t set value=2,version=version+1 where id=1 and version=1;
**悲觀鎖: ** 對于出現(xiàn)更新丟失的可能性比較悲觀,在查詢時就加鎖,保證不被其他事務(wù)操作;可通過select...fot update實(shí)現(xiàn)。
select * from t where id = 1 for update;
共享鎖(S鎖)和排他鎖(X鎖)
共享鎖(shared lock)是指多個事務(wù)之間可以共享鎖資源,一般都是在讀取數(shù)據(jù)時添加,也稱為讀鎖(read lock)。
select * from t where id = 1 lock in share mode; 復(fù)制代碼
排它鎖( exclusive lock,X鎖),也稱為寫鎖(write lock)。
當(dāng)事務(wù)A對數(shù)據(jù)添加上X鎖后,其他事務(wù)則不能再對該數(shù)據(jù)添加任何鎖,直到事務(wù)A釋放數(shù)據(jù)上的X鎖。
增、刪、改都會對數(shù)據(jù)添加X鎖,在查詢語句中使用for update也會添加X鎖。
S鎖 | X鎖 | |
---|---|---|
S鎖 | √ | × |
X鎖 | × | × |
按加鎖粒度區(qū)分
全局鎖
顧名思義,全局鎖是對整個數(shù)據(jù)庫加鎖,加鎖之后整個庫對其他事務(wù)都不能進(jìn)行寫操作。MySQL中提供一種添加全局讀鎖的方式,命令是:flush tables with read lock
(FTWRL)。
-- 加全局讀鎖 flush tables with read lock; -- 解鎖 unlock tables;
使用場景:全庫邏輯備份。
但是使用全局鎖進(jìn)行備份有以下問題:
- 如果在主庫上備份,那么在備份期間都不能執(zhí)行更新,業(yè)務(wù)基本上就得停擺;
- 如果在從庫上備份,那么備份期間從庫不能執(zhí)行主庫同步過來的binlog,會導(dǎo)致主從延遲。
如果全庫中所有的表都是innoDB引擎或其他支持事務(wù)的存儲引擎,可以使用官方的備份工具mysqldump。
當(dāng)mysqldump使用參數(shù)–single-transaction的時候,導(dǎo)數(shù)據(jù)之前就會啟動一個事務(wù),來確保拿到一致性視圖。因?yàn)橛蠱VCC的支持,這個過程中數(shù)據(jù)是可以正常更新的。
如果庫中存在MyISAM這種不支持事務(wù)的存儲引擎,則不能使用mysqldump。
使用set global readonly=true是否可行?
不可以使用set global readonly=true
讓全庫只讀后做邏輯備份。主要有兩個原因:
- read-only一般會用來區(qū)分主庫和備庫,修改global變量影響較大,不建議修改。
- 異常處理機(jī)制不同,執(zhí)行FTWRL命令后如果客戶端異常斷開連接服務(wù)器會自動釋放全局鎖。但是將read-ony設(shè)置為true之后則會永久生效,如果客戶端異常斷開,數(shù)據(jù)庫會一直保持read-only狀態(tài)。
表級鎖(表鎖和MDL鎖)
MySQL里面表級別的鎖有兩種:一種是表鎖,一種是元數(shù)據(jù)鎖(meta data lock,MDL)。
表鎖
lock tables ... read; lock tables ... write; -- 解鎖 unlock tables;
使用表鎖的開銷相對較小,加鎖快,不會產(chǎn)生死鎖;但是加鎖粒度大,發(fā)生鎖沖突的概率更高,并發(fā)度更低。
在innoDB存儲引擎中不推薦使用表鎖,只有在沒有事務(wù)支持的存儲引擎中才會使用,如MyISAM。
元數(shù)據(jù)鎖(MDL)
元數(shù)據(jù)鎖是在MySQL5.5中引入的,MDL不需要顯式添加,在對表數(shù)據(jù)做增刪改查操作時添加MDL讀鎖,在對表進(jìn)行DDL操作時添加MDL寫鎖。
元數(shù)據(jù)鎖是為了保證在多個事務(wù)操作同一張表時表的元數(shù)據(jù)一致性。
如果沒有元數(shù)據(jù)鎖會存在什么問題呢?
事務(wù)隔離問題: 比如在可重復(fù)讀隔離級別下,會話A在兩次查詢期間,會話B對表結(jié)構(gòu)做了修改,兩次查詢結(jié)果就會不一致,無法滿足可重復(fù)讀的要求。
數(shù)據(jù)同步問題: 比如會話A執(zhí)行了多條更新語句期間,另外一個會話B做了表結(jié)構(gòu)變更并且先提交,就會導(dǎo)致備庫在重做時,先重做
alter table
語句,再重做update語句時就會出現(xiàn)復(fù)制錯誤的現(xiàn)象。
MDL讀鎖之間不互斥,因?yàn)橐粡埍砜梢灾С侄鄠€事務(wù)同時增刪改查,讀鎖和寫鎖、寫鎖和寫鎖之間互斥,用來保證對表結(jié)構(gòu)變更的安全性。
在對表執(zhí)行DDL時,會導(dǎo)致所有的增刪改查阻塞。所以在對表字段進(jìn)行修改或增加字段時,一定要特別小心。
一般我們在對大數(shù)據(jù)量表做DDL時都會格外注意,以免對線上業(yè)務(wù)造成影響。但是對小表做DDL操作時同樣要小心,比如以下場景:
事務(wù)A先啟動,這時會對表t加一個MDL讀鎖;
然后事務(wù)B要對表t增加字段,這是需要獲取一個MDL寫鎖,但是由于這時事務(wù)A還沒有提交,所以MDL讀鎖沒有釋放,所以事務(wù)B會被阻塞;
如果僅僅是事務(wù)B阻塞倒也沒什么關(guān)系,頂多是DDL晚點(diǎn)執(zhí)行;但是在這之后的所有對表t的增刪改查都會被阻塞,導(dǎo)致表t不能執(zhí)行任何讀寫操作。
意向鎖
意向鎖是加在表級別的一個鎖,分為意向共享鎖(IS鎖)和意向排它鎖(IX鎖)。
意向鎖,顧名思義,就是指明接下來要做的是一個什么類型的操作。
意向共享鎖(IS):在準(zhǔn)備給表數(shù)據(jù)添加一個S鎖時,需要先獲得該表的IS鎖。
意向排他鎖(IX):在準(zhǔn)備給表數(shù)據(jù)添加一個X鎖時,需要先獲得該表的IX鎖。
之所以有意向鎖的存在,所以在上面的例子中:
意向鎖的出現(xiàn)還有一個主要原因是為了在支持不同粒度鎖時,能有更高的效率。
事務(wù)A對表T中的某一數(shù)據(jù)行添加了行鎖,這時事務(wù)B要對表T添加表鎖,但是在添加之前需要先檢查是否有其他事務(wù)持有該表的X鎖,如果持有則要阻塞;
事務(wù)B通過遍歷表T中的所有行是否有鎖,這樣判斷效率很低,非常耗時。
而意向鎖因?yàn)槭潜砑墑e的鎖,在事務(wù)A在更新數(shù)據(jù)添加行鎖之前,會在表級別由數(shù)據(jù)庫自動添加一個IX鎖,那么當(dāng)事務(wù)B在需要獲取X鎖時,只需要檢查表級別是否有IX鎖,如果有IX鎖代表當(dāng)前有其他事務(wù)正在對表或者表中數(shù)據(jù)執(zhí)行寫操作,不能加鎖成功。
行鎖
MySQL中的行鎖是在存儲引擎層實(shí)現(xiàn)的,并不是所有的存儲引擎都支持,比如MyISAM引擎中就沒有行鎖。
行鎖顧名思義,是在數(shù)據(jù)行上添加鎖,比如事務(wù)A要更新一行數(shù)據(jù),先添加了行鎖,然后事務(wù)B也要更新該行數(shù)據(jù),則必須等事務(wù)A釋放行鎖之后才能更新。
行鎖的加鎖和解鎖時機(jī)
在InnoDB事務(wù)中,行鎖是在需要的時候才加上的,但并不是不需要了就立刻釋放,而是要等到事務(wù)結(jié)束時才釋放。這個就是兩階段鎖協(xié)議。
begin; update t set value = value + 1 where id = 1; update t set value = value + 1 where id = 2; begin ; update t set value = value + 1 where id = 1;
因此,如果你的事務(wù)中需要鎖多個行,要把最可能造成鎖沖突、最可能影響并發(fā)度的鎖盡量往后放。
間隙鎖
間隙鎖,鎖的就是兩個值之間的空隙。
我們都知道每個技術(shù)的出現(xiàn)都是為了解決某個問題,那么間隙鎖又是為了解決什么問題呢?
假設(shè)沒有間隙鎖,會怎么樣,我們來看下面的例子,以下內(nèi)容都是在可重復(fù)讀隔離級別的前提下。
有如下一張表:
CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB; insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20);
假設(shè)我們要執(zhí)行如下SQL,會如何進(jìn)行加鎖和解鎖?
begin; select * from t where d=5 for update; commit;
比較好理解的是,這個語句會命中d=5的這一行,對應(yīng)的主鍵id=5,因此在select 語句執(zhí)行完成后,id=5這一行會加一個寫鎖,而且由于兩階段鎖協(xié)議,這個寫鎖會在執(zhí)行commit語句的時候釋放。
由于字段d上沒有索引,因此這條查詢語句會做全表掃描。那么,其他被掃描到的,但是不滿足條件的5行記錄上,會不會被加鎖呢?
在事務(wù)A中執(zhí)行了3次查詢,都是通過for update
獲取寫鎖,并且是當(dāng)前讀。
假設(shè)只有id=5這一行加鎖,那么三個查詢的執(zhí)行結(jié)果如下:
- Q1返回結(jié)果為(5,5,5);
- Q2返回結(jié)果為(0,0,5),(5,5,5);
- Q3返回結(jié)果為(0,0,5)(1,1,5)(5,5,5);
那么Q3的結(jié)果中查詢到id=1的數(shù)據(jù),這個現(xiàn)象被稱為“幻讀”。
這破壞了事務(wù)A中select * from t where d=5 fot update;
要把所有d=5的數(shù)據(jù)鎖住的語義。
其次,會存在數(shù)據(jù)一致性問題。
如果在事務(wù)B中將binlog拿到備庫執(zhí)行會得到不一樣的結(jié)果。
實(shí)際驗(yàn)證一下,得到結(jié)果并不是只對id=5這一行加鎖,并且對所有的間隙也加了鎖。這樣就保證不能再插入新的數(shù)據(jù)。
next-key lock(臨鍵鎖)
間隙鎖和行鎖合稱next-key lock,每個next-key lock是前開后閉區(qū)間。也就是說,我們的表t初始化以后,如果用select * from t where for update
要把整個表所有記錄鎖起來,就形成了7個next-key lock,分別是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。
間隙鎖和臨建鎖的目的都是用來解決可重復(fù)讀的問題,如果在讀提交級別,間隙鎖和臨建鎖都會失效。
加鎖規(guī)則
MySQL中數(shù)據(jù)加鎖的規(guī)則可以歸納為以下三種:
兩個原則
- 加鎖的基本單位是next-key lock
- 查找過程中訪問到的對象才會加鎖
兩個優(yōu)化
- 索引上的等值查詢,給唯一索引加鎖的時候,next-key lock退化為行鎖
- 索引上的等值查詢,向右遍歷時且最后一個值不滿足等值條件的時候,next-key lock退化為間隙鎖
一個BUG
- 唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止
死鎖和死鎖檢測
什么是死鎖?
在支持并發(fā)操作的系統(tǒng)中,不同的線程對資源出現(xiàn)循環(huán)依賴,線程之間互相持有對方需要的資源,導(dǎo)致線程都進(jìn)入無限等待的狀態(tài),稱之為死鎖。
而在數(shù)據(jù)庫中因?yàn)橛墟i機(jī)制的存在,同樣會導(dǎo)致死鎖。比如:
- 事務(wù)A先獲取到id=1的行鎖,然后事務(wù)B獲取到id=2的行鎖;
- 接著事務(wù)A要獲取id=2的行鎖,發(fā)現(xiàn)被事務(wù)B持有,阻塞;
- 事務(wù)B要獲取id=1的行鎖,發(fā)現(xiàn)被事務(wù)A持有,阻塞;
- 兩個事務(wù)進(jìn)入死鎖狀態(tài)。
當(dāng)出現(xiàn)死鎖后,有兩種處理策略:
- 直接進(jìn)入等待,直到連接超時,超時時間可通過
innodb_lock_wait_timeout
設(shè)置。 - 發(fā)起死鎖檢測,發(fā)現(xiàn)死鎖后主動回滾死鎖中的一個事務(wù),讓其他事務(wù)正常執(zhí)行。將參數(shù)
innodb_deadlock_detect
設(shè)置為on,表示開啟死鎖檢測。
總結(jié)
到此這篇關(guān)于一篇文章搞懂MySQL加鎖機(jī)制的文章就介紹到這了,更多相關(guān)MySQL加鎖機(jī)制內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
對MySQL慢查詢?nèi)罩具M(jìn)行分析的基本教程
這篇文章主要介紹了對MySQL慢查詢?nèi)罩具M(jìn)行分析的基本教程,文中提到的Query-Digest-UI這個基于B/S的圖形化查看工具非常好用,需要的朋友可以參考下2015-12-12配置Mysql主從服務(wù)實(shí)現(xiàn)實(shí)例
這篇文章主要介紹了配置Mysql主從服務(wù)實(shí)現(xiàn)實(shí)例的相關(guān)資料,需要的朋友可以參考下2017-05-05SQL使用聚集函數(shù)實(shí)現(xiàn)對數(shù)據(jù)匯總
這篇文章主要介紹了SQL使用聚集函數(shù)實(shí)現(xiàn)對數(shù)據(jù)匯總,文章圍繞主題展開詳細(xì)的內(nèi)容介紹,具有一定的參考價(jià)值,需要的小伙伴可以參考一下2022-09-09ERROR 1406 : Data too long for column 解決辦法
導(dǎo)入數(shù)據(jù)的時候,mysql報(bào)錯 ERROR 1406 : Data too long for column Data too long for column2011-04-04Mysql聯(lián)合查詢UNION和UNION ALL的使用介紹
本文詳細(xì)介紹了Mysql的聯(lián)合查詢命令UNION和UNION ALL,總結(jié)了使用語法和注意事項(xiàng),以及學(xué)習(xí)例子和項(xiàng)目例子,需要的朋友可以參考下2014-04-04詳解MySQL主從復(fù)制實(shí)戰(zhàn) - 基于GTID的復(fù)制
本篇文章主要介紹了MySQL主從復(fù)制實(shí)戰(zhàn) - 基于GTID的復(fù)制,基于GTID的復(fù)制是MySQL 5.6后新增的復(fù)制方式.有興趣的可以了解一下。2017-03-03centos7.2下安裝mysql5.7數(shù)據(jù)庫的命令詳解
這篇文章主要介紹了centos7.2下安裝mysql5.7數(shù)據(jù)庫,文中給出了所有的命令,按照命令執(zhí)行就會安裝上 ,需要的朋友可以參考下2019-07-07