MySQL&InnoDB鎖機制全面決議

目錄

• • 一、前言
  • 二、鎖的型別
  • • 2.1 全域鎖
    • 2.2 表級鎖
    • • 2.2.1 表鎖
      • 2.2.2 元資料鎖（Meta Data Locks）
      • 2.2.3 自增列鎖（AUTO-INC Locks）
      • 2.2.4 意向鎖 （Intention Locks）
    • 2.3 行級鎖
    • • 2.3.1 Record Locks
      • 2.3.2 Gap Locks
      • 2.3.3 Next-Key Locks
      • 2.3.4 插入意向鎖（Insert Intention Locks ）
  • 三、死鎖
  • 四、小結

一、前言

資料庫使用鎖是為了支持對共享資源的并發訪問，同時保證資料的完整性和一致性，其中，MySQL在Server層和InnoDB引擎設計了多種型別的鎖機制，用于實作不同場景下的并發控制，下面我們分析一下這些鎖的定義和使用場景，

二、鎖的型別

作用范圍劃分

• 全域鎖
  1. FTWRL(Flush tables with read lock)
• 表級鎖
  1. 元資料鎖MDL（meta data lock）
  2. 表鎖
  3. 意向鎖
  4. AUTO-INC Locks
• 行級鎖
  1. Record Locks
  2. Gap Locks
  3. Next-Key Locks
  4. Insert Intention Locks

權限互斥劃分

• 共享鎖
  1. 意向共享鎖IS
  2. 表共享鎖
  3. 行共享鎖
• 排它鎖
  1. 意向排它鎖IX
  2. 表排它鎖
  3. 行排它鎖

2.1 全域鎖

FLUSH TABLES WITH READ LOCK: Closes all open tables and locks all tables for all databases with a global read lock.
This operation is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLES to release the lock.

全域鎖意味著對整個資料庫實體加上鎖，通常使用的是全域讀鎖——Flush tables with read lock (FTWRL)，
使用這個命令，可以使整個庫處于只讀狀態，其他的執行緒無論使用DML、DDL甚至是事務的提交陳述句都會無法正常執行，

使用場景

做全庫邏輯備份，對所有的表資料進行鎖定，保證資料的一致性，

問題

但是在進行備份時使用FTWRL的全域鎖方案有比較嚴重的缺陷：

• 如果是在主庫上進行備份，整個備份期間主庫都不能執行任何資料更新操作，業務無法正常進行，這是不可接受的；
• 如果是在從庫上進行備份，整個備份期間從庫都不能執行主庫同步過來的 binlog，會直接導致主從延遲，

這個方案一般會使用在MyISAM 這種不支持事務的引擎，而對于InnodDB來說，可以在主從備份時使用mysqldump 引數**–single-transaction**開啟一個事務，利用MVCC的特性，拿到一致性視圖資料，保證資料的一致性和業務正常運行，

2.2 表級鎖

2.2.1 表鎖

表鎖通常指的是表級別的S鎖和X鎖，命令是 lock tables … read/write， 當使用lock tables … read時，任何執行緒對該表進行DDL和DML都會失敗；使用lock tables … write時,只允許當前持有表鎖的執行緒才能讀和寫該表，

對于支持行鎖的InnoDB引擎來說，一般不會使用表級別的S鎖和X鎖，因此顯得比較“雞肋”，
而實際專案程序中，經常會有這樣的場景，在對一個表進行DDL表結構變更時，對表記錄的增刪改查操作會被阻塞；反之對表資料進行增刪改查時，也不允許執行表結構變更，如果不使用表鎖怎么實作呢？答案是：通過元資料鎖進行控制，

2.2.2 元資料鎖（Meta Data Locks）

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events).

Meta Data Lock 簡稱MDL，是在MySQL server層使用的一種表級別鎖，并不是InnoDB引擎中實作的，使用時不需要顯式宣告

• 當對表進行增刪改查操作的時候，會自動加 MDL 讀鎖；
• 當要對表做結構變更操作的時候，會自動加 MDL 寫鎖，

讀讀共享，因此可以同時對一張表進行增刪改查；讀寫互斥，寫寫互斥，多個執行緒同時修改表結構時，需要排隊等待執行，保證表結構變更操作的安全性，

元資料鎖的兼容關系如下：

2.2.3 自增列鎖（AUTO-INC Locks）

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

AUTO-INC鎖是一種特殊的表級鎖，當表使用了AUTO_INCREMENT列時，插入資料時需要獲取AUTO-INC鎖，AUTO-INC鎖是作用范圍是陳述句級別，也就是說當執行完成插入陳述句后，哪怕整個事務還沒結束，AUTO-INC鎖也會被釋放，因此會出現：一個事務在持有AUTO-INC鎖進行插入操作時，其他事務的插入操作就會被阻塞，以此來保證自增值是連續的，

問題

使用AUTO-INC Locks會出現這樣的問題：如果一個插入陳述句執行過長（比如insert … select大資料量插入），會導致后面的插入陳述句阻塞時間久，整體性能降低，

解決方案

所以MySQL InnoDB引擎還會采用另一種輕量級鎖（互斥量）的方式，在執行插入陳述句之前先獲取該輕量級鎖，生成AUTO_INCREMENT的值后就釋放鎖，不需要等到插入陳述句執行完成后才釋放，這種方式會大大提高AUTO_INCREMENT值插入的性能，但是也會帶來的問題是——并發時事務的自增列值是不連續的，主從復制時可能是不安全的，

使用innodb_autoinc_lock_mode系統變數可以控制選擇哪一種鎖來為AUTO_INCREMENT賦值

• innodb_autoinc_lock_mode=0:統一使用AUTO-INC 鎖
• innodb_autoinc_lock_mode=2:統一使用輕量級鎖
• innodb_autoinc_lock_mode=1：插入記錄數確定時，采用輕量級鎖；不確定時使用AUTO-INC 鎖

2.2.4 意向鎖 （Intention Locks）

InnoDB supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as LOCK TABLES … WRITE takes an exclusive lock (an X lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks:

• An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.
• An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.

假設有這樣的一種場景：我們想對某張表加X鎖，此時就必須先保證表中的記錄都沒有被加S鎖和X鎖，那么該如何去檢測呢？可以采用回圈遍歷每一條記錄有沒有被上鎖，這種方式明顯效率太低了，所以InnoDB設計了另一種特殊的表級鎖——意向鎖，使用它是為了表在后續被加上X鎖或者S鎖時，能快速判斷表記錄之前是否有被加鎖，從而避免通過遍歷的方式一個個去檢測行鎖的存在，

意向鎖也分為意向共享鎖（IS）和意向排它鎖(IX)，

• 意向共享鎖（IS）：當事務準備給表記錄加S鎖時，需要先對表加上IS鎖
• 意向排它鎖 (IX) ：當事務準備給表記錄加X鎖時，需要先對表加上IX鎖

表級別鎖的兼容性如下：

（表1）

其中，IS鎖和IX鎖、IS鎖和IS鎖、IX鎖和IX鎖之間都是兼容的，這個如何理解呢？

剛剛有提到，意向鎖是為了可以快速判斷表記錄是否被加了鎖，方便判斷事務是否可以對表加鎖，這就意味著，不管有事務對表記錄中加了S鎖，還是加了X鎖，只需要加上對應的IS鎖和IX鎖就好了，不需要關心其他事務加的是IS鎖還是IX鎖，

也就是說，IS鎖和IX鎖只是為了后續對表加S鎖或者X鎖時才起作用，

• IS鎖不兼容表級X鎖，兼容表級S鎖，意思是表中記錄加了S鎖的，只允許對表整體加S鎖
• IX鎖不兼容表級X鎖和S鎖，表中記錄加了X鎖的，不只允許對表整體加S鎖和X鎖

2.3 行級鎖

如果說表級鎖是對整個表進行加鎖的話，那么顧名思義行級鎖就是以行為單位進行加鎖的機制，

• 表級鎖：優點在于加鎖開銷小，速度快，但鎖的粒度粗，缺點是并發性能低，
• 行級鎖：相對開銷較大，速度較慢，但鎖的粒度細，并發性能更高，更適合OLTP的場景，

MySQL 的行級鎖是在引擎層由各個引擎自己來實作的，行級鎖也是 InnoDB引擎對比傳統的MyISAM引擎的一大優勢特性，下面重點介紹一下InnoDB中行級鎖的型別，

2.3.1 Record Locks

A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

Record Lock直譯過來就是記錄鎖，但Record Lock鎖的都是索引的記錄，作用于聚簇索引或者二級索引之上，即使一個表沒有定義索引，InnoDB也會自動創建一個隱藏的聚簇索引并使用該索引進行記錄鎖定，所以Record Lock也稱為索引記錄鎖，

對于下面的例子：

SELECT c1 FROM t WHERE c1 = 10

使用show engine innodb status命令查看：

RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of table test.t
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;

其中記錄鎖也分為共享記錄鎖和排他記錄鎖，同樣遵循讀讀共享，讀寫互斥，寫寫互斥的原則，

2.3.2 Gap Locks

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

Gap Lock直譯過來就是間隙鎖，間隙鎖的引入是作為記錄鎖的補充，我們知道MySQL在可重復讀RR隔離級別下，是可以解決大部分幻讀問題的，

幻讀:指一個事務在前后兩次查詢同一個范圍的時候，后一次查詢看到了前一次查詢沒有看到的行

• RR級別下，事務中如果是使用快照讀（也稱一致性讀）的，如：普通的select查詢，會利用MVCC的一致性視圖方案來避免幻讀，
• RR級別下，事務中如果是使用當前讀的，如：加鎖的select陳述句和更新陳述句（更新資料都是先讀后寫的，此時的【讀】，必須讀當前的值，故稱為“當前讀”）， 只能用加鎖的方案來避免幻讀，

假設在沒有間隙鎖的時候，MySQL只能使用Record Lock記錄鎖來對資料進行加鎖，但是Record Lock只作用在索引行資料上，沒辦法限制住范圍的資料
比如下面這條陳述句：

select * from t where id>1 and id<5 for update
(注：表中只有id=1和id=5這兩條資料)

在RR隔離級別下，如果只對id=1和id=5這兩行記錄加鎖，就沒辦法限制住其他事務在（1,5）這個范圍之間插入新的記錄，所以引入了Gap Lock間隙鎖來對索引行（1,5）之間的空隙，也加上鎖，


對于行級鎖來說，和行鎖產生沖突的是對同一行資料加鎖另外的行鎖，兼容關系如下：

但是對于間隙鎖，他們之間也有共享間隙鎖和排他共享鎖，但是間隙鎖之間是沒有沖突的，與間隙鎖產生沖突的是：向間隙中間插入資料的操作，也就再一次印證了間隙鎖的作用只是為了防止幻讀問題，

2.3.3 Next-Key Locks

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

Next-Key Lock 就是Record Lock+Gap Lock，鎖住行記錄，以及中間的空隙，
還是舉例下面這條陳述句：

select * from t where id>1 and id<5 for update (注：表中只有id=1和id=5這兩條資料)

• Record Lock鎖的范圍就是id=1和id=5
• Gap Lock鎖的范圍就是(1,5)
• Next-Key Lock鎖的范圍就是(1,5]
  （有關記錄鎖和間隙鎖的加鎖情況比較復雜，和隔離級別，索引是二級索引還是聚簇索引直接相關，后續文章會進一步分析）

問題

間隙鎖和 next-key lock 的引入，在為了解決RR隔離級別下出現幻讀的問題，但同時由于鎖住更大的范圍，在一定程度上影響了并發性能，

解決方案

雖然RR是MySQL默認的隔離級別，但是很多線上業務系統都會選擇使用RC讀提交作為默認的隔離級別，同時將binlog_format設定為row，因為RC級別是允許幻讀情況發生的，所以絕大部分場景下RC是不會采用間隙鎖的方式（外鍵場景可能會使用），binlog_format設定為row則是為了防止可能出現資料和日志不一致的問題，

2.3.4 插入意向鎖（Insert Intention Locks ）

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

介紹間隙鎖的時候，我們知道，在某個索引區間如（1,5）加上間隙鎖后，是無法插入id=3和id=4的資料，除非該間隙鎖被釋放，
當兩個事務分別執行插入id=3和id=4的記錄時，會在區間上加插入意向鎖且鎖狀態是等待狀態（is_waiting=true），等到間隙鎖釋放時，將插入意向鎖狀態is_waiting=false，喚醒兩個插入的事務，且這兩個事務之間是不阻塞的，

• 插入意向鎖是在INSERT插入操作時設定的一種特殊間隙鎖 ，注意它并不屬于意向鎖而是屬于間隙鎖，
• 插入意向鎖之間互不排斥，當多個事務在同一區間插入記錄時，只要記錄本身（主鍵索引、唯一索引）不發生沖突，那么事務之間也不會阻塞等待，

三、死鎖

A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds.
死鎖是指不同事務之間每個事務都持有其他事務需要獲取的鎖資源，導致事務無法繼續進行的情況，因為事務都在等待資源變得可用，但都不會釋放它持有的鎖，

也就是當不同執行緒并發執行出現資源依賴回圈，涉及的執行緒都在等待別的執行緒釋放資源時，就會導致這幾個執行緒都進入無限等待的狀態，稱為死鎖，

出現死鎖后，一般有兩種策略，第一種是：

不作處理，直到鎖超時，超時后的事務會進行回滾釋放鎖資源，另外的事務就能繼續執行，鎖超時時間可以通過引數 innodb_lock_wait_timeout 來設定，

innodb_lock_wait_timeout 的默認值是 50s，這對于在線業務而言，是難以接受的，如果將超時時間改小，又可以誤傷到其他正常的操作，

所以一般使用的是第二種策略：

• 使用wait-for graph演算法主動進行發起死鎖檢測，發現死鎖后，主動回滾死鎖鏈條中的某一個事務（一般是回滾影響行最小的事務），從而釋放鎖讓其他事務可以繼續執行，將引數 innodb_deadlock_detect 設定為 on（默認on），表示開啟這個邏輯，

但是如果出現“熱點行”更新的情況——很多事務都要更新同一行的資料，此時死鎖檢測就需要消耗大量的 CPU 資源，此時必須要限制訪問相同資源的并發事務數，

MySQL避免死鎖的方法

1. 一次性鎖定所有需要的資源
2. 按照一致的順序進行加鎖
3. 縮小鎖沖突的范圍

• 避免長事務，將事務拆解，
• 事務需要鎖多個行時，盡量將最可能造成鎖沖突和影響并發度的鎖申請操作放在后面，
• 在業務允許不可重復讀和幻讀的情況下，可使用使用RC的隔離級別，避免間隙鎖鎖定范圍過大造成的死鎖，
• 為DML陳述句加上合適的索引，防止由于不走索引時為表每一行記錄添加上鎖，

四、小結

本文系統性介紹了MySQL&InnoDB的鎖機制，按照鎖的作用范圍，主要分為全域鎖、表鎖和行鎖，而共享鎖和排它鎖則定義了鎖的互斥方式，同時介紹了死鎖的發生、檢測機制和如何避免死鎖的方法，

• 使用共享鎖，可以提高讀操作并發性能；
• InnoDB使用行記錄鎖和間隙鎖，為了保證RR可重復讀級別下的強一致性解決，幻讀問題；
• InnoDB使用插入意向鎖，可以提高插入并發性能；

參考資料

1. MySQL官方檔案
2. 《MySQL技術內幕-InnoDB存盤引擎》
3. 《MySQL是怎樣運行的-從跟上理解MySQL》
4. 極客時間專欄《MySQL實戰45講》

標籤：MySQL

上一篇：MySQL的本地事務、全域事務、分布式事務

下一篇：MySQL高可用架構-MMM、MHA、MGR、PXC