MySQL的又一神器-鎖，MySQL面試必備

歐陽思海發表於2019-10-22
MySql面試

原文連結：blog.ouyangsihai.cn >> MySQL的又一神器-鎖，MySQL面試必備

1 什麼是鎖

1.1 鎖的概述

在生活中鎖的例子多的不能再多了，從古老的簡單的門鎖，到密碼鎖，再到現在的指紋解鎖，人臉識別鎖，這都是鎖的鮮明的例子，所以，我們理解鎖應該是非常簡單的。

再到MySQL中的鎖，對於MySQL來說，鎖是一個很重要的特性，資料庫的鎖是為了支援對共享資源進行併發訪問，提供資料的完整性和一致性，這樣才能保證在高併發的情況下，訪問資料庫的時候，資料不會出現問題。

1.2 鎖的兩個概念

在資料庫中，lock和latch都可以稱為鎖，但是意義卻不同。

Latch一般稱為閂鎖（輕量級的鎖），因為其要求鎖定的時間必須非常短。若持續的時間長，則應用的效能會非常差，在InnoDB引擎中，Latch又可以分為mutex（互斥量）和rwlock（讀寫鎖）。其目的是用來保證併發執行緒操作臨界資源的正確性，並且通常沒有死鎖檢測的機制。

Lock的物件是事務，用來鎖定的是資料庫中的物件，如表、頁、行。並且一般lock的物件僅在事務commit或rollback後進行釋放（不同事務隔離級別釋放的時間可能不同）。

2 InnoDB儲存引擎中的鎖

2.1 鎖的粒度

在資料庫中，鎖的粒度的不同可以分為表鎖、頁鎖、行鎖，這些鎖的粒度之間也是會發生升級的，鎖升級的意思就是講當前鎖的粒度降低，資料庫可以把一個表的1000個行鎖升級為一個頁鎖，或者將頁鎖升級為表鎖，下面分別介紹一下這三種鎖的粒度（參考自部落格：https://blog.csdn.net/baolingye/article/details/102506072）。

表鎖

表級別的鎖定是MySQL各儲存引擎中最大顆粒度的鎖定機制。該鎖定機制最大的特點是實現邏輯非常簡單，帶來的系統負面影響最小。所以獲取鎖和釋放鎖的速度很快。由於表級鎖一次會將整個表鎖定，所以可以很好的避免困擾我們的死鎖問題。

當然，鎖定顆粒度大所帶來最大的負面影響就是出現鎖定資源爭用的概率也會最高，致使並大度大打折扣。

使用表級鎖定的主要是MyISAM，MEMORY，CSV等一些非事務性儲存引擎。

特點： 開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，併發度最低。

頁鎖

頁級鎖定是MySQL中比較獨特的一種鎖定級別，在其他資料庫管理軟體中也並不是太常見。頁級鎖定的特點是鎖定顆粒度介於行級鎖定與表級鎖之間，所以獲取鎖定所需要的資源開銷，以及所能提供的併發處理能力也同樣是介於上面二者之間。另外，頁級鎖定和行級鎖定一樣，會發生死鎖。在資料庫實現資源鎖定的過程中，隨著鎖定資源顆粒度的減小，鎖定相同資料量的資料所需要消耗的記憶體數量是越來越多的，實現演算法也會越來越複雜。不過，隨著鎖定資源 顆粒度的減小，應用程式的訪問請求遇到鎖等待的可能性也會隨之降低，系統整體併發度也隨之提升。使用頁級鎖定的主要是BerkeleyDB儲存引擎。

特點： 開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般。

行鎖

行級鎖定最大的特點就是鎖定物件的粒度很小，也是目前各大資料庫管理軟體所實現的鎖定顆粒度最小的。由於鎖定顆粒度很小，所以發生鎖定資源爭用的概率也最小，能夠給予應用程式儘可能大的併發處理能力而提高一些需要高併發應用系統的整體效能。

雖然能夠在併發處理能力上面有較大的優勢，但是行級鎖定也因此帶來了不少弊端。由於鎖定資源的顆粒度很小，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來的消耗自然也就更大了。此外，行級鎖定也最容易發生死鎖。

特點： 開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度也最高。

比較表鎖我們可以發現，這兩種鎖的特點基本都是相反的，而從鎖的角度來說，表級鎖更適合於以查詢為主，只有少量按索引條件更新資料的應用，如Web應用；而行級鎖則更適合於有大量按索引條件併發更新少量不同資料，同時又有併發查詢的應用，如一些線上事務處理（OLTP）系統。

MySQL 不同引擎支援的鎖的粒度

2.2 鎖的型別

InnoDB儲存引擎中存在著不同型別的鎖，下面一一介紹一下。

S or X (共享鎖、排他鎖)

資料的操作其實只有兩種，也就是讀和寫，而資料庫在實現鎖時，也會對這兩種操作使用不同的鎖；InnoDB 實現了標準的行級鎖，也就是共享鎖（Shared Lock）和互斥鎖（Exclusive Lock）。

  • 共享鎖（讀鎖）（S Lock），允許事務讀一行資料。
  • 排他鎖（寫鎖）（X Lock），允許事務刪除或更新一行資料。

IS or IX (共享、排他)意向鎖

為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB儲存引擎支援一種額外的鎖方式，就稱為意向鎖，意向鎖在 InnoDB 中是表級鎖，意向鎖分為：

  • 意向共享鎖：表達一個事務想要獲取一張表中某幾行的共享鎖。
  • 意向排他鎖：表達一個事務想要獲取一張表中某幾行的排他鎖。

另外，這些鎖之間的並不是一定可以共存的，有些鎖之間是不相容的，所謂相容性就是指事務 A 獲得一個某行某種鎖之後，事務 B 同樣的在這個行上嘗試獲取某種鎖，如果能立即獲取，則稱鎖相容，反之叫衝突。

下面我們再看一下這兩種鎖的相容性。

  • S or X (共享鎖、排他鎖)的相容性

  • IS or IX (共享、排他)意向鎖的相容性

3 前面小結

這裡用一個思維導圖把前面的概念做一個小結。

4 一致性非鎖定讀和一致性鎖定讀

一致性鎖定讀（Locking Reads）

在一個事務中查詢資料時，普通的SELECT語句不會對查詢的資料進行加鎖，其他事務仍可以對查詢的資料執行更新和刪除操作。因此，InnoDB提供了兩種型別的鎖定讀來保證額外的安全性：

  • SELECT ... LOCK IN SHARE MODE
  • SELECT ... FOR UPDATE

SELECT ... LOCK IN SHARE MODE: 對讀取的行新增S鎖，其他事物可以對這些行新增S鎖，若新增X鎖，則會被阻塞。

SELECT ... FOR UPDATE: 會對查詢的行及相關聯的索引記錄加X鎖，其他事務請求的S鎖或X鎖都會被阻塞。 當事務提交或回滾後，通過這兩個語句新增的鎖都會被釋放。 注意：只有在自動提交被禁用時，SELECT FOR UPDATE才可以鎖定行，若開啟自動提交，則匹配的行不會被鎖定。

一致性非鎖定讀

一致性非鎖定讀(consistent nonlocking read) 是指InnoDB儲存引擎通過多版本控制(MVVC)讀取當前資料庫中行資料的方式。如果讀取的行正在執行DELETE或UPDATE操作，這時讀取操作不會因此去等待行上鎖的釋放。相反地，InnoDB會去讀取行的一個快照。所以，非鎖定讀機制大大提高了資料庫的併發性。

來自網路：侵權刪

一致性非鎖定讀是InnoDB預設的讀取方式，即讀取不會佔用和等待行上的鎖。在事務隔離級別READ COMMITTED和REPEATABLE READ下，InnoDB使用一致性非鎖定讀。

然而，對於快照資料的定義卻不同。在READ COMMITTED事務隔離級別下，一致性非鎖定讀總是讀取被鎖定行的最新一份快照資料。而在REPEATABLE READ事務隔離級別下，則讀取事務開始時的行資料版本。

下面我們通過一個簡單的例子來說明一下這兩種方式的區別。

首先建立一張表;

插入一條資料；

insert into lock_test values(1);複製程式碼

檢視隔離級別；

select @@tx_isolation;複製程式碼

下面分為兩種事務進行操作。

在REPEATABLE READ事務隔離級別下；

在REPEATABLE READ事務隔離級別下，讀取事務開始時的行資料，所以當會話B修改了資料之後，通過以前的查詢，還是可以查詢到資料的。

在READ COMMITTED事務隔離級別下;

在READ COMMITTED事務隔離級別下，讀取該行版本最新的一個快照資料，所以，由於B會話修改了資料，並且提交了事務，所以，A讀取不到資料了。

5 行鎖的演算法

InnoDB儲存引擎有3種行鎖的演算法，其分別是：

  • Record Lock：單個行記錄上的鎖。
  • Gap Lock：間隙鎖，鎖定一個範圍，但不包含記錄本身。
  • Next-Key Lock：Gap Lock+Record Lock，鎖定一個範圍，並且鎖定記錄本身。

Record Lock：總是會去鎖住索引記錄，如果InnoDB儲存引擎表在建立的時候沒有設定任何一個索引，那麼這時InnoDB儲存引擎會使用隱式的主鍵來進行鎖定。

Next-Key Lock：結合了Gap Lock和Record Lock的一種鎖定演算法，在Next-Key Lock演算法下，InnoDB對於行的查詢都是採用這種鎖定演算法。舉個例子10，20，30，那麼該索引可能被Next-Key Locking的區間為：

除了Next-Key Locking，還有Previous-Key Locking技術，這種技術跟Next-Key Lock正好相反，鎖定的區間是區間範圍和前一個值。同樣上述的值，使用Previous-Key Locking技術，那麼可鎖定的區間為：

不是所有索引都會加上Next-key Lock的，這裡有一種特殊的情況，在查詢的列是唯一索引（包含主鍵索引）的情況下，Next-key Lock會降級為Record Lock。

接下來，我們來通過一個例子解釋一下。

CREATE TABLE test (
    x INT,
    y INT,
    PRIMARY KEY(x),    // x是主鍵索引
    KEY(y)    // y是普通索引
);
INSERT INTO test select 3, 2;
INSERT INTO test select 5, 3;
INSERT INTO test select 7, 6;
INSERT INTO test select 10, 8;複製程式碼

我們現在會話A中執行如下語句；

SELECT * FROM test WHERE y = 3 FOR UPDATE複製程式碼

我們分析一下這時候的加鎖情況。

  • 對於主鍵x

  • 輔助索引y

使用者可以通過以下兩種方式來顯示的關閉Gap Lock：

  • 將事務的隔離級別設為 READ COMMITED。
  • 將引數innodblocksunsafeforbinlog設定為1。

Gap Lock的作用：是為了阻止多個事務將記錄插入到同一個範圍內，設計它的目的是用來解決Phontom Problem（幻讀問題）。在MySQL預設的隔離級別（Repeatable Read）下，InnoDB就是使用它來解決幻讀問題。

幻讀：是指在同一事務下，連續執行兩次同樣的SQL語句可能導致不同的結果，第二次的SQL可能會返回之前不存在的行，也就是第一次執行和第二次執行期間有其他事務往裡插入了新的行。

6 鎖帶來的問題

6.1 髒讀

髒讀： 在不同的事務下，當前事務可以讀到另外事務未提交的資料。另外我們需要注意的是預設的MySQL隔離級別是REPEATABLE READ是不會發生髒讀的，髒讀發生的條件是需要事務的隔離級別為READ UNCOMMITTED，所以如果出現髒讀，可能就是這種隔離級別導致的。

下面我們通過一個例子看一下。

從上面這個例子可以看出，當我們的事務的隔離級別為READ UNCOMMITTED的時候，在會話A還沒有提交時，會話B就能夠查詢到會話A沒有提交的資料。

6.2 不可重複讀

不可重複讀： 是指在一個事務內多次讀取同一集合的資料，但是多次讀到的資料是不一樣的，這就違反了資料庫事務的一致性的原則。但是，這跟髒讀還是有區別的，髒讀的資料是沒有提交的，但是不可重複讀的資料是已經提交的資料。

我們通過下面的例子來看一下這種問題的發生。

從上面的例子可以看出，在A的一次會話中，由於會話B插入了資料，導致兩次查詢的結果不一致，所以就出現了不可重複讀的問題。

我們需要注意的是不可重複讀讀取的資料是已經提交的資料，事務的隔離級別為READ COMMITTED，這種問題我們是可以接受的。

如果我們需要避免不可重複讀的問題的發生，那麼我們可以使用Next-Key Lock演算法（設定事務的隔離級別為READ REPEATABLE）來避免，在MySQL中，不可重複讀問題就是Phantom Problem，也就是幻像問題。

6.3 丟失更新

丟失更新：指的是一個事務的更新操作會被另外一個事務的更新操作所覆蓋，從而導致資料的不一致。在當前資料庫的任何隔離級別下都不會導致丟失更新問題，要出現這個問題，在多使用者計算機系統環境下有可能出現這種問題。

如何避免丟失更新的問題呢，我們只需要讓事務的操作變成序列化，不要並行執行就可以。

我們一般使用SELECT ... FOR UPDATE語句，給操作加上一個排他X鎖。

6.4 小結

這裡我們做一個小結，主要是在不同的事務的隔離級別下出現的問題的對照，這樣就更加清晰了。

文章有不當之處，歡迎指正，如果喜歡微信閱讀，你也可以關注我的微信公眾號：好好學java，獲取優質學習資源。

本文由部落格一文多發平臺 OpenWrite 釋出！

相關文章