天天看點
傳回

MySQL——鎖的類型以及加鎖原理、死鎖

前言

使用

insert into on duplicate key update

語句進行插入去重，但是在測試過程中發現了死鎖現象:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

表鎖和行鎖

首先來了解一下表鎖和行鎖：表鎖是指對一整張表加鎖，一般是 DDL 處理時使用；而行鎖則是鎖定某一行或者某幾行，或者行與行之間的間隙。

表鎖由 MySQL Server 實作，行鎖則是存儲引擎實作，不同的引擎實作的不同。在 MySQL 的常用引擎中 InnoDB 支援行鎖，而 MyISAM 則隻能使用 MySQL Server 提供的表鎖。

MySQL——鎖的類型以及加鎖原理、死鎖

表鎖

表鎖由 MySQL Server 實作，一般在執行 DDL 語句時會對整個表進行加鎖，比如說 ALTER TABLE 等操作。在執行 SQL 語句時，也可以明确指定對某個表進行加鎖。

mysql> lock table user read(write); # 分為讀鎖和寫鎖
Query OK, 0 rows affected (0.00 sec)
           
mysql> select * from user where id = 100; # 成功
mysql> select * from role where id = 100; # 失敗，未提前擷取該 role的讀表鎖
mysql> update user set name = 'Tom' where id = 100; # 失敗，未提前獲得user的寫表鎖
           
mysql> unlock tables; # 顯示釋放表鎖
Query OK, 0 rows affected (0.00 sec)

表鎖使用的是一次性鎖技術，也就是說，在會話開始的地方使用 lock 指令将後續需要用到的表都加上鎖，在表釋放前，隻能通路這些加鎖的表，不能通路其他表，直到最後通過 unlock tables 釋放所有表鎖。

除了使用 unlock tables 顯示釋放鎖之外，會話持有其他表鎖時執行lock table 語句會釋放會話之前持有的鎖；會話持有其他表鎖時執行 start transaction 或者 begin 開啟事務時，也會釋放之前持有的鎖。

行鎖

不同存儲引擎的行鎖實作不同，後續沒有特别說明，則行鎖特指 InnoDB 實作的行鎖。

在了解 InnoDB 的加鎖原理前，需要對其存儲結構有一定的了解。InnoDB 是聚簇索引，也就是 B+樹的葉節點既存儲了主鍵索引也存儲了資料行。而 InnoDB 的二級索引的葉節點存儲的則是主鍵值，是以通過二級索引查詢資料時，還需要拿對應的主鍵去聚簇索引中再次進行查詢。關于 InnoDB 和 MyISAM 的索引的詳細知識可以閱讀《Mysql探索(一):B+Tree索引》一文。

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;

上述 SQL 的執行過程如下圖所示。MySQL Server 會根據 WHERE 條件讀取第一條滿足條件的記錄，然後 InnoDB 引擎會将第一條記錄傳回并加鎖，接着 MySQL Server 發起更新改行記錄的 UPDATE 請求，更新這條記錄。一條記錄操作完成，再讀取下一條記錄，直至沒有比對的記錄為止。

MySQL——鎖的類型以及加鎖原理、死鎖

這種場景下的鎖的釋放較為複雜，有多種的優化方式，我對這塊暫時還沒有了解，還請知道的小夥伴在下方留言解釋。

下面主要依次介紹 InnoDB 中鎖的模式和類型，鎖的類型是指鎖的粒度或者鎖具體加在什麼地方；而鎖模式描述的是鎖的相容性，也就是加的是什麼鎖，比如寫鎖或者讀鎖。

行鎖的模式

行鎖的模式有：讀意向鎖，寫意向鎖，讀鎖，寫鎖和自增鎖(auto_inc)，下面我們依次來看。

讀寫鎖

  • 讀鎖：又稱共享鎖（Share locks，簡稱 S 鎖），加了讀鎖的記錄，所有的事務都可以讀取，但是不能修改，并且可同時有多個事務對記錄加讀鎖。
  • 寫鎖：又稱排他鎖（Exclusive locks，簡稱 X 鎖），或獨占鎖，對記錄加了排他鎖之後，隻有擁有該鎖的事務可以讀取和修改，其他事務都不可以讀取和修改，并且同一時間隻能有一個事務加寫鎖。

讀寫意向鎖

由于表鎖和行鎖雖然鎖定範圍不同，但是會互相沖突。是以當你要加表鎖時，勢必要先周遊該表的所有記錄，判斷是否加有排他鎖。這種周遊檢查的方式顯然是一種低效的方式，MySQL 引入了意向鎖，來檢測表鎖和行鎖的沖突。

意向鎖也是表級鎖，也可分為讀意向鎖（IS 鎖）和寫意向鎖（IX 鎖）。當事務要在記錄上加上讀鎖或寫鎖時，要首先在表上加上意向鎖。這樣判斷表中是否有記錄加鎖就很簡單了，隻要看下表上是否有意向鎖就行了。

意向鎖之間是不會産生沖突的，也不和 AUTO_INC 表鎖沖突，它隻會阻塞表級讀鎖或表級寫鎖，另外，意向鎖也不會和行鎖沖突，行鎖隻會和行鎖沖突。

自增鎖

AUTO_INC 鎖：又叫自增鎖（一般簡寫成 AI 鎖），是一種表鎖，當表中有自增列（AUTO_INCREMENT）時出現。當插入表中有自增列時，資料庫需要自動生成自增值，它會先為該表加 AUTO_INC 表鎖，阻塞其他事務的插入操作，這樣保證生成的自增值肯定是唯一的。

AUTO_INC 鎖具有如下特點：

  • AUTO_INC 鎖互不相容，也就是說同一張表同時隻允許有一個自增鎖；
  • 自增值一旦配置設定了就會 +1，如果事務復原，自增值也不會減回去，是以自增值可能會出現中斷的情況。

顯然，AUTO_INC 表鎖會導緻并發插入的效率降低，為了提高插入的并發性，MySQL 從 5.1.22 版本開始，引入了一種可選的輕量級鎖（mutex）機制來代替 AUTO_INC 鎖，可以通過參數 innodb_autoinc_lock_mode 來靈活控制配置設定自增值時的并發政策。具體可以參考 MySQL 的 AUTO_INCREMENT Handling in InnoDB 一文，連結在文末。

不同模式鎖的相容矩陣

下面是各個表鎖之間的相容矩陣。

MySQL——鎖的類型以及加鎖原理、死鎖

總結起來有下面幾點：

  • 意向鎖之間互不沖突；
  • S 鎖隻和 S/IS 鎖相容，和其他鎖都沖突；
  • X 鎖和其他所有鎖都沖突；
  • AI 鎖隻和意向鎖相容；

行鎖的類型

根據鎖的粒度可以把鎖細分為表鎖和行鎖，行鎖根據場景的不同又可以進一步細分，依次為 Next-Key Lock，Gap Lock 間隙鎖，Record Lock 記錄鎖和插入意向 GAP 鎖。

不同的鎖鎖定的位置是不同的，比如說記錄鎖隻鎖住對應的記錄，而間隙鎖鎖住記錄和記錄之間的間隔，Next-Key Lock 則所屬記錄和記錄之前的間隙。不同類型鎖的鎖定範圍大緻如下圖所示。

MySQL——鎖的類型以及加鎖原理、死鎖

下面我們來依次了解一下不同的類型的鎖。

記錄鎖

記錄鎖是最簡單的行鎖，并沒有什麼好說的。上邊描述 InnoDB 加鎖原理中的鎖就是記錄鎖，隻鎖住 id = 49 或者 name = ‘Tom’ 這一條記錄。

當 SQL 語句無法使用索引時，會進行全表掃描，這個時候 MySQL 會給整張表的所有資料行加記錄鎖，再由 MySQL Server 層進行過濾。但是，在 MySQL Server 層進行過濾的時候，如果發現不滿足 WHERE 條件，會釋放對應記錄的鎖。這樣做，保證了最後隻會持有滿足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略的。

是以更新操作必須要根據索引進行操作，沒有索引時，不僅會消耗大量的鎖資源，增加資料庫的開銷，還會極大的降低了資料庫的并發性能。

間隙鎖

還是最開始更新使用者年齡的例子，如果 id = 49 這條記錄不存在，這個 SQL 語句還會加鎖嗎？答案是可能有，這取決于資料庫的隔離級别。這種情況下，在 RC 隔離級别不會加任何鎖，在 RR 隔離級别會在 id = 49 前後兩個索引之間加上間隙鎖。

間隙鎖是一種加在兩個索引之間的鎖，或者加在第一個索引之前，或最後一個索引之後的間隙。這個間隙可以跨一個索引記錄，多個索引記錄，甚至是空的。使用間隙鎖可以防止其他事務在這個範圍内插入或修改記錄，保證兩次讀取這個範圍内的記錄不會變，進而不會出現幻讀現象。

值得注意的是，間隙鎖和間隙鎖之間是互不沖突的，間隙鎖唯一的作用就是為了防止其他事務的插入，是以加間隙 S 鎖和加間隙 X 鎖沒有任何差別。

Next-Key 鎖

Next-key鎖是記錄鎖和間隙鎖的組合，它指的是加在某條記錄以及這條記錄前面間隙上的鎖。假設一個索引包含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 的記錄。

插入意向鎖

插入意向鎖是一種特殊的間隙鎖（簡寫成 II GAP）表示插入的意向，隻有在 INSERT 的時候才會有這個鎖。注意，這個鎖雖然也叫意向鎖，但是和上面介紹的表級意向鎖是兩個完全不同的概念，不要搞混了。

插入意向鎖和插入意向鎖之間互不沖突，是以可以在同一個間隙中有多個事務同時插入不同索引的記錄。譬如在上面的例子中，id = 30 和 id = 49 之間如果有兩個事務要同時分别插入 id = 32 和 id = 33 是沒問題的，雖然兩個事務都會在 id = 30 和 id = 50 之間加上插入意向鎖，但是不會沖突。

插入意向鎖隻會和間隙鎖或 Next-key 鎖沖突，正如上面所說，間隙鎖唯一的作用就是防止其他事務插入記錄造成幻讀，正是由于在執行 INSERT 語句時需要加插入意向鎖，而插入意向鎖和間隙鎖沖突，進而阻止了插入操作的執行。

不同類型鎖的相容矩陣

不同類型鎖的相容下如下圖所示。

MySQL——鎖的類型以及加鎖原理、死鎖

其中，第一行表示已有的鎖，第一清單示要加的鎖。插入意向鎖較為特殊，是以我們先對插入意向鎖做個總結，如下：

  • 插入意向鎖不影響其他事務加其他任何鎖。也就是說，一個事務已經擷取了插入意向鎖，對其他事務是沒有任何影響的；
  • 插入意向鎖與間隙鎖和 Next-key 鎖沖突。也就是說，一個事務想要擷取插入意向鎖，如果有其他事務已經加了間隙鎖或 Next-key 鎖，則會阻塞。

其他類型的鎖的規則較為簡單：

  • 間隙鎖不和其他鎖（不包括插入意向鎖）沖突；
  • 記錄鎖和記錄鎖沖突，Next-key 鎖和 Next-key 鎖沖突，記錄鎖和 Next-key 鎖沖突；

##死鎖

在解決Mysql 死鎖的問題之前，還是先來了解一下什麼是死鎖。

死鎖是指兩個或兩個以上的程序在執行過程中，因争奪資源而造成的一種互相等待的現象，若無外力作用，它們都将無法推進下去，此時稱系統處于死鎖狀态或系統産生了死鎖，這些永遠在互相等的程序稱為死鎖程序。

死鎖的表現

死鎖的具體表現有兩種：

Mysql 增改語句無法正常生效，使用Mysql GUI 工具編輯字段的值時，會出現異常。 如何避免死鎖

阻止死鎖的途徑就是避免滿足死鎖條件的情況發生，為此我們在開發的過程中需要遵循如下原則：

  • 1、盡量避免并發的執行涉及到修改資料的語句。
  • 2、要求每一個事務一次就将所有要使用到的資料全部加鎖，否則就不允許執行。
  • 3、預先規定一個加鎖順序，所有的事務都必須按照這個順序對資料執行封鎖。如不同的過程在事務内部對對象的更新執行順序應盡量保證一緻。

檢視死鎖

Mysql 查詢是否存在鎖表有多種方式，這裡隻介紹一種最常用的。

  • 1、檢視正在進行中的事務 
SELECT * FROM information_schema.INNODB_TRX
  • 2、檢視正在鎖的事務 
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
  • 3、檢視等待鎖的事務 
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
  • 4、查詢是否鎖表 
SHOW OPEN TABLES where In_use > 0;

在發生死鎖時，這幾種方式都可以查詢到和目前死鎖相關的資訊。

  • 5、檢視最近死鎖的日志 
show engine innodb status

解除死鎖

如果需要解除死鎖，有一種最簡單粗暴的方式，那就是找到程序id之後，直接幹掉。

檢視目前正在進行中的程序

show processlist
           
// 也可以使用
SELECT * FROM information_schema.INNODB_TRX;

這兩個指令找出來的程序id 是同一個。

殺掉程序對應的程序 id

kill id

驗證（kill後再看是否還有鎖）

SHOW OPEN TABLES where In_use > 0;

Oracle檢視鎖表sql

檢視鎖表程序SQL語句1： 

select sess.sid,
    sess.serial#,
    lo.oracle_username,
    lo.os_user_name,
    ao.object_name,
    lo.locked_mode
    from v$locked_object lo,
    dba_objects ao,
    v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

檢視鎖表程序SQL語句2： 

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

殺掉鎖表程序：

如有記錄則表示有lock，記錄下SID和serial# ，將記錄的ID替換下面的738,1429，即可解除LOCK

alter system kill session '738,1429';

檢視導緻鎖表的sql語句是那一條 

select l.session_id sid, 
    s.serial#, 
    l.locked_mode, 
    l.oracle_username, 
    s.user#, 
    l.os_user_name, 
    s.machine, 
    s.terminal, 
    a.sql_text, 
    a.action 
    from v$sqlarea a, v$session s, v$locked_object l 
where l.session_id = s.sid 
    and s.prev_sql_addr = a.address 
    order by sid, s.serial#;
意向鎖 mysql 死鎖 資料庫
上一篇: 遊戲開發入門之五子棋遊戲分析程式源代碼下載下傳
下一篇: 五子棋遊戲 五子棋遊戲

繼續閱讀