一篇文章搞懂MySQL加鎖機制

Posted on by

前言

在數據庫中設計鎖的目的是為瞭處理並發問題，在並發對資源進行訪問時，數據庫要合理控制對資源的訪問規則。

而鎖就是用來實現這些訪問規則的一個數據結構。

在對數據並發操作時，沒有鎖可能會引起數據的不一致，導致更新丟失。

鎖的分類

樂觀鎖和悲觀鎖

樂觀鎖： 對於出現更新丟失的可能性比較樂觀，先認為不會出現更新丟失，在最後更新數據時進行比較。

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;

**悲觀鎖： ** 對於出現更新丟失的可能性比較悲觀，在查詢時就加鎖，保證不被其他事務操作；可通過select…fot update實現。

select * from t where id = 1 for update;

共享鎖（S鎖）和排他鎖（X鎖）

共享鎖（shared lock）是指多個事務之間可以共享鎖資源，一般都是在讀取數據時添加，也稱為讀鎖（read lock）。

select * from t where id = 1 lock in share mode;
復制代碼

排它鎖（ exclusive lock，X鎖），也稱為寫鎖（write lock）。

當事務A對數據添加上X鎖後，其他事務則不能再對該數據添加任何鎖，直到事務A釋放數據上的X鎖。

增、刪、改都會對數據添加X鎖，在查詢語句中使用for update也會添加X鎖。

  S鎖 X鎖
S鎖 √ ×
X鎖 × ×

按加鎖粒度區分

全局鎖

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

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

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

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

  • 如果在主庫上備份，那麼在備份期間都不能執行更新，業務基本上就得停擺；
  • 如果在從庫上備份，那麼備份期間從庫不能執行主庫同步過來的binlog，會導致主從延遲。

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

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

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

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

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

  • read-only一般會用來區分主庫和備庫，修改global變量影響較大，不建議修改。
  • 異常處理機制不同，執行FTWRL命令後如果客戶端異常斷開連接服務器會自動釋放全局鎖。但是將read-ony設置為true之後則會永久生效，如果客戶端異常斷開，數據庫會一直保持read-only狀態。

表級鎖（表鎖和MDL鎖）

MySQL裡面表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（meta data lock，MDL)。

表鎖

lock tables ... read;
lock tables ... write;
-- 解鎖
unlock tables;

使用表鎖的開銷相對較小，加鎖快，不會產生死鎖；但是加鎖粒度大，發生鎖沖突的概率更高，並發度更低。

在innoDB存儲引擎中不推薦使用表鎖，隻有在沒有事務支持的存儲引擎中才會使用，如MyISAM。

元數據鎖（MDL）

元數據鎖是在MySQL5.5中引入的，MDL不需要顯式添加，在對表數據做增刪改查操作時添加MDL讀鎖，在對表進行DDL操作時添加MDL寫鎖。

元數據鎖是為瞭保證在多個事務操作同一張表時表的元數據一致性。

如果沒有元數據鎖會存在什麼問題呢？

  • 事務隔離問題： 比如在可重復讀隔離級別下，會話A在兩次查詢期間，會話B對表結構做瞭修改，兩次查詢結果就會不一致，無法滿足可重復讀的要求。

  • 數據同步問題： 比如會話A執行瞭多條更新語句期間，另外一個會話B做瞭表結構變更並且先提交，就會導致備庫在重做時，先重做alter table語句，再重做update語句時就會出現復制錯誤的現象。

MDL讀鎖之間不互斥，因為一張表可以支持多個事務同時增刪改查，讀鎖和寫鎖、寫鎖和寫鎖之間互斥，用來保證對表結構變更的安全性。

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

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

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

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

  • 如果僅僅是事務B阻塞倒也沒什麼關系，頂多是DDL晚點執行；但是在這之後的所有對表t的增刪改查都會被阻塞，導致表t不能執行任何讀寫操作。

意向鎖

意向鎖是加在表級別的一個鎖，分為意向共享鎖（IS鎖）和意向排它鎖（IX鎖）。

意向鎖，顧名思義，就是指明接下來要做的是一個什麼類型的操作。

意向共享鎖（IS）：在準備給表數據添加一個S鎖時，需要先獲得該表的IS鎖。

意向排他鎖（IX）：在準備給表數據添加一個X鎖時，需要先獲得該表的IX鎖。

之所以有意向鎖的存在，所以在上面的例子中：

意向鎖的出現還有一個主要原因是為瞭在支持不同粒度鎖時，能有更高的效率。

事務A對表T中的某一數據行添加瞭行鎖，這時事務B要對表T添加表鎖，但是在添加之前需要先檢查是否有其他事務持有該表的X鎖，如果持有則要阻塞；

事務B通過遍歷表T中的所有行是否有鎖，這樣判斷效率很低，非常耗時。

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

行鎖

MySQL中的行鎖是在存儲引擎層實現的，並不是所有的存儲引擎都支持，比如MyISAM引擎中就沒有行鎖。

行鎖顧名思義，是在數據行上添加鎖，比如事務A要更新一行數據，先添加瞭行鎖，然後事務B也要更新該行數據，則必須等事務A釋放行鎖之後才能更新。

行鎖的加鎖和解鎖時機

在InnoDB事務中，行鎖是在需要的時候才加上的，但並不是不需要瞭就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協議。

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;

因此，如果你的事務中需要鎖多個行，要把最可能造成鎖沖突、最可能影響並發度的鎖盡量往後放。

間隙鎖

間隙鎖，鎖的就是兩個值之間的空隙。

我們都知道每個技術的出現都是為瞭解決某個問題，那麼間隙鎖又是為瞭解決什麼問題呢？

假設沒有間隙鎖，會怎麼樣，我們來看下面的例子，以下內容都是在可重復讀隔離級別的前提下。

有如下一張表：

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);

假設我們要執行如下SQL，會如何進行加鎖和解鎖？

begin;
select * from t where d=5 for update;
commit;

比較好理解的是，這個語句會命中d=5的這一行，對應的主鍵id=5，因此在select 語句執行完成後，id=5這一行會加一個寫鎖，而且由於兩階段鎖協議，這個寫鎖會在執行commit語句的時候釋放。

由於字段d上沒有索引，因此這條查詢語句會做全表掃描。那麼，其他被掃描到的，但是不滿足條件的5行記錄上，會不會被加鎖呢？

在事務A中執行瞭3次查詢，都是通過for update獲取寫鎖，並且是當前讀。

假設隻有id=5這一行加鎖，那麼三個查詢的執行結果如下：

  • Q1返回結果為(5,5,5);
  • Q2返回結果為(0,0,5),(5,5,5)；
  • Q3返回結果為(0,0,5)(1,1,5)(5,5,5)；

那麼Q3的結果中查詢到id=1的數據，這個現象被稱為“幻讀”。

這破壞瞭事務A中select * from t where d=5 fot update;要把所有d=5的數據鎖住的語義。

其次，會存在數據一致性問題。

如果在事務B中將binlog拿到備庫執行會得到不一樣的結果。

實際驗證一下，得到結果並不是隻對id=5這一行加鎖，並且對所有的間隙也加瞭鎖。這樣就保證不能再插入新的數據。

next-key lock(臨鍵鎖)

間隙鎖和行鎖合稱next-key lock，每個next-key lock是前開後閉區間。也就是說，我們的表t初始化以後，如果用select * from t where for update要把整個表所有記錄鎖起來，就形成瞭7個next-key lock，分別是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

間隙鎖和臨建鎖的目的都是用來解決可重復讀的問題，如果在讀提交級別，間隙鎖和臨建鎖都會失效。

加鎖規則

MySQL中數據加鎖的規則可以歸納為以下三種：

兩個原則

  • 加鎖的基本單位是next-key lock
  • 查找過程中訪問到的對象才會加鎖

兩個優化

  • 索引上的等值查詢，給唯一索引加鎖的時候，next-key lock退化為行鎖
  • 索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，next-key lock退化為間隙鎖

一個BUG

  • 唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止

死鎖和死鎖檢測

什麼是死鎖？

在支持並發操作的系統中，不同的線程對資源出現循環依賴，線程之間互相持有對方需要的資源，導致線程都進入無限等待的狀態，稱之為死鎖。

而在數據庫中因為有鎖機制的存在，同樣會導致死鎖。比如：

  • 事務A先獲取到id=1的行鎖，然後事務B獲取到id=2的行鎖；
  • 接著事務A要獲取id=2的行鎖，發現被事務B持有，阻塞；
  • 事務B要獲取id=1的行鎖，發現被事務A持有，阻塞；
  • 兩個事務進入死鎖狀態。

當出現死鎖後，有兩種處理策略：

  • 直接進入等待，直到連接超時，超時時間可通過innodb_lock_wait_timeout設置。
  • 發起死鎖檢測，發現死鎖後主動回滾死鎖中的一個事務，讓其他事務正常執行。將參數innodb_deadlock_detect設置為on，表示開啟死鎖檢測。

總結

到此這篇關於一篇文章搞懂MySQL加鎖機制的文章就介紹到這瞭,更多相關MySQL加鎖機制內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：