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

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

1 什么是鎖

1.1 鎖的概述

在生活中鎖的例子多的不能再多了，從古老的簡(jiǎn)單的門鎖，到密碼鎖，再到現(xiàn)在的指紋解鎖，人臉識(shí)別鎖，這都是鎖的鮮明的例子，所以，我們理解鎖應(yīng)該是非常簡(jiǎn)單的。

再到MySQL中的鎖，對(duì)于MySQL來說，鎖是一個(gè)很重要的特性，數(shù)據(jù)庫(kù)的鎖是為了支持對(duì)共享資源進(jìn)行并發(fā)訪問，提供數(shù)據(jù)的完整性和一致性，這樣才能保證在高并發(fā)的情況下，訪問數(shù)據(jù)庫(kù)的時(shí)候，數(shù)據(jù)不會(huì)出現(xiàn)問題。

1.2 鎖的兩個(gè)概念

在數(shù)據(jù)庫(kù)中，lock和latch都可以稱為鎖，但是意義卻不同。

Latch一般稱為閂鎖（輕量級(jí)的鎖），因?yàn)槠湟箧i定的時(shí)間必須非常短。若持續(xù)的時(shí)間長(zhǎng)，則應(yīng)用的性能會(huì)非常差，在InnoDB引擎中，Latch又可以分為mutex（互斥量）和rwlock（讀寫鎖）。其目的是用來保證并發(fā)線程操作臨界資源的正確性，并且通常沒有死鎖檢測(cè)的機(jī)制。

Lock的對(duì)象是事務(wù)，用來鎖定的是數(shù)據(jù)庫(kù)中的對(duì)象，如表、頁(yè)、行。并且一般lock的對(duì)象僅在事務(wù)commit或rollback后進(jìn)行釋放（不同事務(wù)隔離級(jí)別釋放的時(shí)間可能不同）。

2 InnoDB存儲(chǔ)引擎中的鎖

2.1 鎖的粒度

在數(shù)據(jù)庫(kù)中，鎖的粒度的不同可以分為表鎖、頁(yè)鎖、行鎖，這些鎖的粒度之間也是會(huì)發(fā)生升級(jí)的，鎖升級(jí)的意思就是講當(dāng)前鎖的粒度降低，數(shù)據(jù)庫(kù)可以把一個(gè)表的1000個(gè)行鎖升級(jí)為一個(gè)頁(yè)鎖，或者將頁(yè)鎖升級(jí)為表鎖，下面分別介紹一下這三種鎖的粒度（參考自博客：https://blog.csdn.net/baolingye/article/details/102506072）。

表鎖

表級(jí)別的鎖定是MySQL各存儲(chǔ)引擎中最大顆粒度的鎖定機(jī)制。該鎖定機(jī)制最大的特點(diǎn)是實(shí)現(xiàn)邏輯非常簡(jiǎn)單，帶來的系統(tǒng)負(fù)面影響最小。所以獲取鎖和釋放鎖的速度很快。由于表級(jí)鎖一次會(huì)將整個(gè)表鎖定，所以可以很好的避免困擾我們的死鎖問題。

當(dāng)然，鎖定顆粒度大所帶來最大的負(fù)面影響就是出現(xiàn)鎖定資源爭(zhēng)用的概率也會(huì)最高，致使并大度大打折扣。

使用表級(jí)鎖定的主要是MyISAM，MEMORY，CSV等一些非事務(wù)性存儲(chǔ)引擎。

特點(diǎn)： 開銷小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。

頁(yè)鎖

頁(yè)級(jí)鎖定是MySQL中比較獨(dú)特的一種鎖定級(jí)別，在其他數(shù)據(jù)庫(kù)管理軟件中也并不是太常見。頁(yè)級(jí)鎖定的特點(diǎn)是鎖定顆粒度介于行級(jí)鎖定與表級(jí)鎖之間，所以獲取鎖定所需要的資源開銷，以及所能提供的并發(fā)處理能力也同樣是介于上面二者之間。另外，頁(yè)級(jí)鎖定和行級(jí)鎖定一樣，會(huì)發(fā)生死鎖。
在數(shù)據(jù)庫(kù)實(shí)現(xiàn)資源鎖定的過程中，隨著鎖定資源顆粒度的減小，鎖定相同數(shù)據(jù)量的數(shù)據(jù)所需要消耗的內(nèi)存數(shù)量是越來越多的，實(shí)現(xiàn)算法也會(huì)越來越復(fù)雜。不過，隨著鎖定資源 顆粒度的減小，應(yīng)用程序的訪問請(qǐng)求遇到鎖等待的可能性也會(huì)隨之降低，系統(tǒng)整體并發(fā)度也隨之提升。
使用頁(yè)級(jí)鎖定的主要是BerkeleyDB存儲(chǔ)引擎。

特點(diǎn)： 開銷和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。

行鎖

行級(jí)鎖定最大的特點(diǎn)就是鎖定對(duì)象的粒度很小，也是目前各大數(shù)據(jù)庫(kù)管理軟件所實(shí)現(xiàn)的鎖定顆粒度最小的。由于鎖定顆粒度很小，所以發(fā)生鎖定資源爭(zhēng)用的概率也最小，能夠給予應(yīng)用程序盡可能大的并發(fā)處理能力而提高一些需要高并發(fā)應(yīng)用系統(tǒng)的整體性能。

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

特點(diǎn)： 開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。

比較表鎖我們可以發(fā)現(xiàn)，這兩種鎖的特點(diǎn)基本都是相反的，而從鎖的角度來說，表級(jí)鎖更適合于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如Web應(yīng)用；而行級(jí)鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時(shí)又有并發(fā)查詢的應(yīng)用，如一些在線事務(wù)處理（OLTP）系統(tǒng)。

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

2.2 鎖的類型

InnoDB存儲(chǔ)引擎中存在著不同類型的鎖，下面一一介紹一下。

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

數(shù)據(jù)的操作其實(shí)只有兩種，也就是讀和寫，而數(shù)據(jù)庫(kù)在實(shí)現(xiàn)鎖時(shí)，也會(huì)對(duì)這兩種操作使用不同的鎖；InnoDB 實(shí)現(xiàn)了標(biāo)準(zhǔn)的行級(jí)鎖，也就是共享鎖（Shared Lock）和互斥鎖（Exclusive Lock）。

• 共享鎖（讀鎖）（S Lock），允許事務(wù)讀一行數(shù)據(jù)。
• 排他鎖（寫鎖）（X Lock），允許事務(wù)刪除或更新一行數(shù)據(jù)。

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

為了允許行鎖和表鎖共存，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB存儲(chǔ)引擎支持一種額外的鎖方式，就稱為意向鎖，意向鎖在 InnoDB 中是表級(jí)鎖，意向鎖分為：

• 意向共享鎖：表達(dá)一個(gè)事務(wù)想要獲取一張表中某幾行的共享鎖。
• 意向排他鎖：表達(dá)一個(gè)事務(wù)想要獲取一張表中某幾行的排他鎖。

另外，這些鎖之間的并不是一定可以共存的，有些鎖之間是不兼容的，所謂兼容性就是指事務(wù) A 獲得一個(gè)某行某種鎖之后，事務(wù) B 同樣的在這個(gè)行上嘗試獲取某種鎖，如果能立即獲取，則稱鎖兼容，反之叫沖突。

下面我們?cè)倏匆幌逻@兩種鎖的兼容性。

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

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

3 前面小結(jié)

這里用一個(gè)思維導(dǎo)圖把前面的概念做一個(gè)小結(jié)。

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

一致性鎖定讀（Locking Reads）

在一個(gè)事務(wù)中查詢數(shù)據(jù)時(shí)，普通的SELECT語句不會(huì)對(duì)查詢的數(shù)據(jù)進(jìn)行加鎖，其他事務(wù)仍可以對(duì)查詢的數(shù)據(jù)執(zhí)行更新和刪除操作。因此，InnoDB提供了兩種類型的鎖定讀來保證額外的安全性：

• SELECT ... LOCK IN SHARE MODE

• SELECT ... FOR UPDATE

  SELECT ... LOCK IN SHARE MODE: 對(duì)讀取的行添加S鎖，其他事物可以對(duì)這些行添加S鎖，若添加X鎖，則會(huì)被阻塞。

  SELECT ... FOR UPDATE: 會(huì)對(duì)查詢的行及相關(guān)聯(lián)的索引記錄加X鎖，其他事務(wù)請(qǐng)求的S鎖或X鎖都會(huì)被阻塞。 當(dāng)事務(wù)提交或回滾后，通過這兩個(gè)語句添加的鎖都會(huì)被釋放。 注意：只有在自動(dòng)提交被禁用時(shí)，SELECT FOR UPDATE才可以鎖定行，若開啟自動(dòng)提交，則匹配的行不會(huì)被鎖定。

  一致性非鎖定讀

  一致性非鎖定讀(consistent nonlocking read) 是指InnoDB存儲(chǔ)引擎通過多版本控制(MVVC)讀取當(dāng)前數(shù)據(jù)庫(kù)中行數(shù)據(jù)的方式。如果讀取的行正在執(zhí)行DELETE或UPDATE操作，這時(shí)讀取操作不會(huì)因此去等待行上鎖的釋放。相反地，InnoDB會(huì)去讀取行的一個(gè)快照。所以，非鎖定讀機(jī)制大大提高了數(shù)據(jù)庫(kù)的并發(fā)性。

  

一致性非鎖定讀是InnoDB默認(rèn)的讀取方式，即讀取不會(huì)占用和等待行上的鎖。在事務(wù)隔離級(jí)別READ COMMITTED和REPEATABLE READ下，InnoDB使用一致性非鎖定讀。

然而，對(duì)于快照數(shù)據(jù)的定義卻不同。在READ COMMITTED事務(wù)隔離級(jí)別下，一致性非鎖定讀總是讀取被鎖定行的最新一份快照數(shù)據(jù)。而在REPEATABLE READ事務(wù)隔離級(jí)別下，則讀取事務(wù)開始時(shí)的行數(shù)據(jù)版本。

下面我們通過一個(gè)簡(jiǎn)單的例子來說明一下這兩種方式的區(qū)別。

首先創(chuàng)建一張表;

插入一條數(shù)據(jù)；

insert into lock_test values(1);

查看隔離級(jí)別；

select @@tx_isolation;

下面分為兩種事務(wù)進(jìn)行操作。

在REPEATABLE READ事務(wù)隔離級(jí)別下；

在REPEATABLE READ事務(wù)隔離級(jí)別下，讀取事務(wù)開始時(shí)的行數(shù)據(jù)，所以當(dāng)會(huì)話B修改了數(shù)據(jù)之后，通過以前的查詢，還是可以查詢到數(shù)據(jù)的。

在READ COMMITTED事務(wù)隔離級(jí)別下;

在READ COMMITTED事務(wù)隔離級(jí)別下，讀取該行版本最新的一個(gè)快照數(shù)據(jù)，所以，由于B會(huì)話修改了數(shù)據(jù)，并且提交了事務(wù)，所以，A讀取不到數(shù)據(jù)了。

5 行鎖的算法

InnoDB存儲(chǔ)引擎有3種行鎖的算法，其分別是：

• Record Lock：?jiǎn)蝹€(gè)行記錄上的鎖。
• Gap Lock：間隙鎖，鎖定一個(gè)范圍，但不包含記錄本身。
• Next-Key Lock：Gap Lock+Record Lock，鎖定一個(gè)范圍，并且鎖定記錄本身。

Record Lock：總是會(huì)去鎖住索引記錄，如果InnoDB存儲(chǔ)引擎表在建立的時(shí)候沒有設(shè)置任何一個(gè)索引，那么這時(shí)InnoDB存儲(chǔ)引擎會(huì)使用隱式的主鍵來進(jìn)行鎖定。

Next-Key Lock：結(jié)合了Gap Lock和Record Lock的一種鎖定算法，在Next-Key Lock算法下，InnoDB對(duì)于行的查詢都是采用這種鎖定算法。舉個(gè)例子10，20，30，那么該索引可能被Next-Key Locking的區(qū)間為：

除了Next-Key Locking，還有Previous-Key Locking技術(shù)，這種技術(shù)跟Next-Key Lock正好相反，鎖定的區(qū)間是區(qū)間范圍和前一個(gè)值。同樣上述的值，使用Previous-Key Locking技術(shù)，那么可鎖定的區(qū)間為：

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

接下來，我們來通過一個(gè)例子解釋一下。

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;

我們現(xiàn)在會(huì)話A中執(zhí)行如下語句；

SELECT * FROM test WHERE y = 3 FOR UPDATE

我們分析一下這時(shí)候的加鎖情況。

• 對(duì)于主鍵x

• 輔助索引y

用戶可以通過以下兩種方式來顯示的關(guān)閉Gap Lock：

• 將事務(wù)的隔離級(jí)別設(shè)為 READ COMMITED。
• 將參數(shù)innodb_locks_unsafe_for_binlog設(shè)置為1。

Gap Lock的作用：是為了阻止多個(gè)事務(wù)將記錄插入到同一個(gè)范圍內(nèi)，設(shè)計(jì)它的目的是用來解決Phontom Problem（幻讀問題）。在MySQL默認(rèn)的隔離級(jí)別（Repeatable Read）下，InnoDB就是使用它來解決幻讀問題。

幻讀：是指在同一事務(wù)下，連續(xù)執(zhí)行兩次同樣的SQL語句可能導(dǎo)致不同的結(jié)果，第二次的SQL可能會(huì)返回之前不存在的行，也就是第一次執(zhí)行和第二次執(zhí)行期間有其他事務(wù)往里插入了新的行。

6 鎖帶來的問題

6.1 臟讀

臟讀： 在不同的事務(wù)下，當(dāng)前事務(wù)可以讀到另外事務(wù)未提交的數(shù)據(jù)。另外我們需要注意的是默認(rèn)的MySQL隔離級(jí)別是REPEATABLE READ是不會(huì)發(fā)生臟讀的，臟讀發(fā)生的條件是需要事務(wù)的隔離級(jí)別為READ UNCOMMITTED，所以如果出現(xiàn)臟讀，可能就是這種隔離級(jí)別導(dǎo)致的。

下面我們通過一個(gè)例子看一下。

從上面這個(gè)例子可以看出，當(dāng)我們的事務(wù)的隔離級(jí)別為READ UNCOMMITTED的時(shí)候，在會(huì)話A還沒有提交時(shí)，會(huì)話B就能夠查詢到會(huì)話A沒有提交的數(shù)據(jù)。

6.2 不可重復(fù)讀

不可重復(fù)讀： 是指在一個(gè)事務(wù)內(nèi)多次讀取同一集合的數(shù)據(jù)，但是多次讀到的數(shù)據(jù)是不一樣的，這就違反了數(shù)據(jù)庫(kù)事務(wù)的一致性的原則。但是，這跟臟讀還是有區(qū)別的，臟讀的數(shù)據(jù)是沒有提交的，但是不可重復(fù)讀的數(shù)據(jù)是已經(jīng)提交的數(shù)據(jù)。

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

從上面的例子可以看出，在A的一次會(huì)話中，由于會(huì)話B插入了數(shù)據(jù)，導(dǎo)致兩次查詢的結(jié)果不一致，所以就出現(xiàn)了不可重復(fù)讀的問題。

我們需要注意的是不可重復(fù)讀讀取的數(shù)據(jù)是已經(jīng)提交的數(shù)據(jù)，事務(wù)的隔離級(jí)別為READ COMMITTED，這種問題我們是可以接受的。

如果我們需要避免不可重復(fù)讀的問題的發(fā)生，那么我們可以使用Next-Key Lock算法（設(shè)置事務(wù)的隔離級(jí)別為READ REPEATABLE）來避免，在MySQL中，不可重復(fù)讀問題就是Phantom Problem，也就是幻像問題。

6.3 丟失更新

丟失更新：指的是一個(gè)事務(wù)的更新操作會(huì)被另外一個(gè)事務(wù)的更新操作所覆蓋，從而導(dǎo)致數(shù)據(jù)的不一致。在當(dāng)前數(shù)據(jù)庫(kù)的任何隔離級(jí)別下都不會(huì)導(dǎo)致丟失更新問題，要出現(xiàn)這個(gè)問題，在多用戶計(jì)算機(jī)系統(tǒng)環(huán)境下有可能出現(xiàn)這種問題。

如何避免丟失更新的問題呢，我們只需要讓事務(wù)的操作變成串行化，不要并行執(zhí)行就可以。

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

6.4 小結(jié)

這里我們做一個(gè)小結(jié)，主要是在不同的事務(wù)的隔離級(jí)別下出現(xiàn)的問題的對(duì)照，這樣就更加清晰了。

文章有不當(dāng)之處，歡迎指正，如果喜歡微信閱讀，你也可×××學(xué)java`，獲取優(yōu)質(zhì)學(xué)習(xí)資源。