MySQL加鎖機制是什么

這篇文章主要介紹MySQL加鎖機制是什么，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

前言

在數(shù)據(jù)庫中設(shè)計鎖的目的是為了處理并發(fā)問題，在并發(fā)對資源進行訪問時，數(shù)據(jù)庫要合理控制對資源的訪問規(guī)則。

而鎖就是用來實現(xiàn)這些訪問規(guī)則的一個數(shù)據(jù)結(jié)構(gòu)。

在對數(shù)據(jù)并發(fā)操作時，沒有鎖可能會引起數(shù)據(jù)的不一致，導(dǎo)致更新丟失。

鎖的分類

樂觀鎖和悲觀鎖

樂觀鎖： 對于出現(xiàn)更新丟失的可能性比較樂觀，先認為不會出現(xiàn)更新丟失，在最后更新數(shù)據(jù)時進行比較。

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實現(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;

排它鎖（ 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鎖。

按加鎖粒度區(qū)分

全局鎖

顧名思義，全局鎖是對整個數(shù)據(jù)庫加鎖，加鎖之后整個庫對其他事務(wù)都不能進行寫操作。MySQL中提供一種添加全局讀鎖的方式，命令是：flush tables with read lock(FTWRL)。

-- 加全局讀鎖
flush tables with read lock;
-- 解鎖
unlock tables;

使用場景：全庫邏輯備份。

但是使用全局鎖進行備份有以下問題：

• 如果在主庫上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)基本上就得停擺；

• 如果在從庫上備份，那么備份期間從庫不能執(zhí)行主庫同步過來的binlog，會導(dǎo)致主從延遲。

如果全庫中所有的表都是innoDB引擎或其他支持事務(wù)的存儲引擎，可以使用官方的備份工具mysqldump。

當(dāng)mysqldump使用參數(shù)–single-transaction的時候，導(dǎo)數(shù)據(jù)之前就會啟動一個事務(wù)，來確保拿到一致性視圖。因為有MVCC的支持，這個過程中數(shù)據(jù)是可以正常更新的。

如果庫中存在MyISAM這種不支持事務(wù)的存儲引擎，則不能使用mysqldump。

使用set global readonly=true是否可行？

不可以使用set global readonly=true讓全庫只讀后做邏輯備份。主要有兩個原因：

• read-only一般會用來區(qū)分主庫和備庫，修改global變量影響較大，不建議修改。

• 異常處理機制不同，執(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讀鎖，在對表進行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讀鎖之間不互斥，因為一張表可以支持多個事務(wù)同時增刪改查，讀鎖和寫鎖、寫鎖和寫鎖之間互斥，用來保證對表結(jié)構(gòu)變更的安全性。

在對表執(zhí)行DDL時，會導(dǎo)致所有的增刪改查阻塞。所以在對表字段進行修改或增加字段時，一定要特別小心。

一般我們在對大數(shù)據(jù)量表做DDL時都會格外注意，以免對線上業(yè)務(wù)造成影響。但是對小表做DDL操作時同樣要小心，比如以下場景：

• 事務(wù)A先啟動，這時會對表t加一個MDL讀鎖；

• 然后事務(wù)B要對表t增加字段，這是需要獲取一個MDL寫鎖，但是由于這時事務(wù)A還沒有提交，所以MDL讀鎖沒有釋放，所以事務(wù)B會被阻塞；

• 如果僅僅是事務(wù)B阻塞倒也沒什么關(guān)系，頂多是DDL晚點執(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中的所有行是否有鎖，這樣判斷效率很低，非常耗時。

而意向鎖因為是表級別的鎖，在事務(wù)A在更新數(shù)據(jù)添加行鎖之前，會在表級別由數(shù)據(jù)庫自動添加一個IX鎖，那么當(dāng)事務(wù)B在需要獲取X鎖時，只需要檢查表級別是否有IX鎖，如果有IX鎖代表當(dāng)前有其他事務(wù)正在對表或者表中數(shù)據(jù)執(zhí)行寫操作，不能加鎖成功。

行鎖

MySQL中的行鎖是在存儲引擎層實現(xiàn)的，并不是所有的存儲引擎都支持，比如MyISAM引擎中就沒有行鎖。

行鎖顧名思義，是在數(shù)據(jù)行上添加鎖，比如事務(wù)A要更新一行數(shù)據(jù)，先添加了行鎖，然后事務(wù)B也要更新該行數(shù)據(jù)，則必須等事務(wù)A釋放行鎖之后才能更新。

行鎖的加鎖和解鎖時機

在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，會如何進行加鎖和解鎖？

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é)果。

實際驗證一下，得到結(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)致線程都進入無限等待的狀態(tài)，稱之為死鎖。

而在數(shù)據(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ù)進入死鎖狀態(tài)。

當(dāng)出現(xiàn)死鎖后，有兩種處理策略：

• 直接進入等待，直到連接超時，超時時間可通過innodb_lock_wait_timeout設(shè)置。

• 發(fā)起死鎖檢測，發(fā)現(xiàn)死鎖后主動回滾死鎖中的一個事務(wù)，讓其他事務(wù)正常執(zhí)行。將參數(shù)innodb_deadlock_detect設(shè)置為on，表示開啟死鎖檢測。

以上是“MySQL加鎖機制是什么”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道！