【51CTO.com原創(chuàng)稿件】隨著 IT 技術(shù)的飛速發(fā)展，各種技術(shù)層出不窮，讓人眼花繚亂。盡管技術(shù)在不斷更新?lián)Q代，但是有些技術(shù)依舊被一代代 IT 人使用至今。

[[279429]] 

圖片來(lái)自 Pexels

MySQL 就是其中之一，它經(jīng)歷了多個(gè)版本迭代。數(shù)據(jù)庫(kù)鎖是 MySQL 數(shù)據(jù)引擎的一部分，今天我們就一起來(lái)學(xué)習(xí) MySQL 的數(shù)據(jù)庫(kù)鎖和它的優(yōu)化。

MySQL 鎖分類

當(dāng)多個(gè)事務(wù)或者進(jìn)程訪問(wèn)同一個(gè)資源的時(shí)候，為了保證數(shù)據(jù)的一致性，就需要用到鎖機(jī)制。

從鎖定資源的角度來(lái)看，MySQL 中的鎖分為：

• 表級(jí)鎖
• 行級(jí)鎖
• 頁(yè)面鎖

表級(jí)鎖：對(duì)整張表加鎖。開(kāi)銷小，加鎖快;不會(huì)出現(xiàn)死鎖;鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。

行級(jí)鎖：對(duì)某行記錄加鎖。開(kāi)銷大，加鎖慢;會(huì)出現(xiàn)死鎖;鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。

頁(yè)面鎖：開(kāi)銷和加鎖時(shí)間界于表鎖和行鎖之間;會(huì)出現(xiàn)死鎖;鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。

在實(shí)際開(kāi)發(fā)過(guò)程中，主要會(huì)使用到表級(jí)鎖和行級(jí)鎖兩種。既然鎖是針對(duì)資源的，那么這些資源就是數(shù)據(jù)，在 MySQL 提供插件式存儲(chǔ)引擎對(duì)數(shù)據(jù)進(jìn)行存儲(chǔ)。

插件式存儲(chǔ)引擎的好處是，開(kāi)發(fā)人員可以根據(jù)需要選擇適合的存儲(chǔ)引擎。

在眾多的存儲(chǔ)引擎中，有兩種引擎被比較多的使用，他們分別是：

• MyISAM 存儲(chǔ)引擎，它不支持事務(wù)、表鎖設(shè)計(jì)，支持全文索引，主要面向一些在線分析處理(OLAP)數(shù)據(jù)庫(kù)應(yīng)用。說(shuō)白了主要就是查詢數(shù)據(jù)，對(duì)數(shù)據(jù)的插入，更新操作比較少。
• InnoDB 存儲(chǔ)引擎，它支持事務(wù)，其設(shè)計(jì)目標(biāo)主要面向在線事務(wù)處理(OLTP)的應(yīng)用。

其特點(diǎn)是行鎖設(shè)計(jì)、支持外鍵，并支持類似于 Oracle 的非鎖定讀，即默認(rèn)讀取操作不會(huì)產(chǎn)生鎖。

簡(jiǎn)單來(lái)說(shuō)，就是對(duì)數(shù)據(jù)的插入，更新操作比較多。從 MySQL 數(shù)據(jù)庫(kù) 5.5.8 版本開(kāi)始，InnoDB 存儲(chǔ)引擎是默認(rèn)的存儲(chǔ)引擎。

上面兩種存儲(chǔ)引擎在處理多進(jìn)程數(shù)據(jù)操作的時(shí)候是如何表現(xiàn)的，就是我們接下來(lái)要討論的問(wèn)題。

為了讓整個(gè)描述更加清晰，我們將表級(jí)鎖和行級(jí)鎖以及 MyISAM，InnoDB 存儲(chǔ)引擎，就形成了一個(gè) 2*2 的象限。

 

2*2 表行鎖，MyISAM，InnoDB 示意圖

由于 MyISAM 存儲(chǔ)引擎不支持行級(jí)鎖，實(shí)際上后面討論的問(wèn)題會(huì)圍繞三個(gè)象限的討論展開(kāi)。

從內(nèi)容上來(lái)看，InnoDB 作為使用最多的存儲(chǔ)引擎遇到的問(wèn)題和值得注意的地方較多，也是本文的重點(diǎn)。

MyISAM 存儲(chǔ)引擎和表級(jí)鎖

首先，來(lái)看第一象限的內(nèi)容：

 

2*2 表行鎖，MyISAM，InnoDB 示意圖-第一象限

MyISAM 存儲(chǔ)引擎支持表級(jí)鎖，并且支持兩種鎖模式：

• 對(duì) MyISAM 表的讀操作(共享鎖)，不會(huì)阻塞其他進(jìn)程對(duì)同一表的讀請(qǐng)求，但會(huì)阻塞對(duì)其的寫請(qǐng)求。當(dāng)讀鎖釋放后，才會(huì)執(zhí)行其他進(jìn)程的寫操作。
• 對(duì) MyISAM 表的寫操作(排他鎖)，會(huì)阻塞其他進(jìn)程對(duì)同一表的讀寫操作，當(dāng)該鎖釋放后，才會(huì)執(zhí)行其他進(jìn)程的讀寫操作。

MyISAM 優(yōu)化建議

在使用 MyISAM 存儲(chǔ)引擎時(shí)。執(zhí)行 SQL 語(yǔ)句，會(huì)自動(dòng)為 SELECT 語(yǔ)句加上共享鎖，為 UDI(更新，刪除，插入)操作加上排他鎖。

由于這個(gè)特性在多進(jìn)程并發(fā)插入同一張表的時(shí)候，就會(huì)因?yàn)榕潘i而進(jìn)行等待。

因此可以通過(guò)配置 concurrent_insert 系統(tǒng)變量，來(lái)控制其并發(fā)的插入行為。

①concurrent_insert=0 時(shí)，不允許并發(fā)插入。

②concurrent_insert=1 時(shí)，如果 MyISAM 表中沒(méi)有空洞(即表中沒(méi)有被刪除的行)，允許一個(gè)進(jìn)程讀表時(shí)，另一個(gè)進(jìn)程向表的尾部插入記錄(MySQL 默認(rèn)設(shè)置)。

注：空洞是行記錄被刪除以后，只是被標(biāo)記為“已刪除”其存儲(chǔ)空間沒(méi)有被回收，也就是說(shuō)沒(méi)有被物理刪除。由另外一個(gè)進(jìn)程，異步對(duì)這個(gè)數(shù)據(jù)進(jìn)行刪除。

因?yàn)榭臻g長(zhǎng)度問(wèn)題，刪除以后的物理空間不能被新的記錄所使用，從而形成了空洞。

③concurrent_insert=2 時(shí)，無(wú)論 MyISAM 表中有沒(méi)有空洞，都允許在表尾并發(fā)插入記錄。

如果在數(shù)據(jù)插入的時(shí)候，沒(méi)有并發(fā)刪除操作的話，可以嘗試把 concurrent_insert 設(shè)置為 1。

反之，在數(shù)據(jù)插入的時(shí)候有刪除操作且量較大時(shí)，也就是會(huì)產(chǎn)生“空洞”的時(shí)候，就需要把 concurrent_insert 設(shè)置為 2。

另外，當(dāng)一個(gè)進(jìn)程請(qǐng)求某個(gè) MyISAM 表的讀鎖，另一個(gè)進(jìn)程也請(qǐng)求同一表的寫鎖。

即使讀請(qǐng)求先到達(dá)，寫請(qǐng)求后到達(dá)，寫請(qǐng)求也會(huì)插到讀請(qǐng)求之前。因?yàn)?MySQL 的默認(rèn)設(shè)置認(rèn)為，寫請(qǐng)求比讀請(qǐng)求重要。

我們可以通過(guò) low_priority_updates 來(lái)調(diào)節(jié)讀寫行為的優(yōu)先級(jí)：

• 數(shù)據(jù)庫(kù)以讀為主時(shí)，要優(yōu)先保證查詢性能時(shí)，可通過(guò) low_priority_updates=1 設(shè)置讀優(yōu)先級(jí)高于寫優(yōu)先級(jí)。
• 數(shù)據(jù)庫(kù)以寫為主時(shí)，則不用設(shè)置 low_priority_updates 參數(shù)。

InnoDB 存儲(chǔ)引擎和表級(jí)鎖

再來(lái)看看第二象限的內(nèi)容：

 

2*2 表行鎖，MyISAM，InnoDB 示意圖-第二象限

InnoDB 存儲(chǔ)引擎表鎖。當(dāng)沒(méi)有對(duì)數(shù)據(jù)表中的索引數(shù)據(jù)進(jìn)行查詢時(shí)，會(huì)執(zhí)行表鎖操作。

上面是 InnoDB 實(shí)現(xiàn)行鎖，同時(shí)它也可以實(shí)現(xiàn)表鎖。其方式就是意向鎖(Intention Locks)。

這里介紹兩種意向鎖：

• 意向共享鎖(IS)：事務(wù)打算給數(shù)據(jù)行加行共享鎖，事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前，必須先取得該表的 IS 鎖。
• 意向排他鎖(IX)：事務(wù)打算給數(shù)據(jù)行加行排他鎖，事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前，必須先取得該表的 IX 鎖。

注：意向共享鎖和意向排他鎖是數(shù)據(jù)庫(kù)主動(dòng)加的，不需要我們手動(dòng)處理。對(duì)于 UPDATE、DELETE 和 INSERT 語(yǔ)句，InnoDB 會(huì)自動(dòng)給數(shù)據(jù)集加排他鎖。

InnoDB表鎖的實(shí)現(xiàn)方式：假設(shè)有一個(gè)表 test2，有兩個(gè)字段分別是 id 和 name。

沒(méi)有設(shè)置主鍵同時(shí)也沒(méi)有設(shè)置任何索引(index)如下：

 

InnoDB 表鎖實(shí)現(xiàn)方式圖

InnoDB 存儲(chǔ)引擎和行級(jí)鎖

第四象限我們使用的比較多，討論的內(nèi)容也相對(duì)多些：

 

2*2 表行鎖，MyISAM，InnoDB 示意圖-第四象限

InnoDB 存儲(chǔ)引擎行鎖，當(dāng)數(shù)據(jù)查詢時(shí)針對(duì)索引數(shù)據(jù)進(jìn)行時(shí)，會(huì)使用行級(jí)鎖。

共享鎖(S)：當(dāng)一個(gè)事務(wù)讀取一條記錄的時(shí)候，不會(huì)阻塞其他事務(wù)對(duì)同一記錄的讀請(qǐng)求，但會(huì)阻塞對(duì)其的寫請(qǐng)求。當(dāng)讀鎖釋放后，才會(huì)執(zhí)行其他事務(wù)的寫操作。

例如：select … lock in share mode

排他鎖(X)：當(dāng)一個(gè)事務(wù)對(duì)一條記錄進(jìn)行寫操作時(shí)，會(huì)阻塞其他事務(wù)對(duì)同一表的讀寫操作，當(dāng)該鎖釋放后，才會(huì)執(zhí)行其他事務(wù)的讀寫操作。

例如：select … for update

行鎖的實(shí)現(xiàn)方式：假設(shè)有一個(gè)表 test1，有兩個(gè)字段分別是 id 和 name。

id 作為主鍵同時(shí)也是 table 的索引(index)如下：

 

InnoDB 行鎖實(shí)現(xiàn)方式圖

在高并發(fā)的情況下，多個(gè)事務(wù)同時(shí)請(qǐng)求更新數(shù)據(jù)，由于資源被占用等待事務(wù)增多。

如此，會(huì)造成性能問(wèn)題，可以通過(guò) innodb_lock_wait_timeout 來(lái)解決。innodb_lock_wait_timeout 是事務(wù)等待獲取資源的最長(zhǎng)時(shí)間，單位為秒。如果超過(guò)時(shí)間還未分配到資源，則會(huì)返回應(yīng)用失敗。

四種鎖的兼容情況：

 

共享鎖，排他鎖，意向共享鎖，意向排他鎖兼容圖例

如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前的鎖兼容， InnoDB 就將請(qǐng)求的鎖授予該事務(wù);反之， 如果兩者不兼容，該事務(wù)就要等待鎖釋放。

間隙鎖

前面談到行鎖是針對(duì)一條記錄進(jìn)行加鎖。當(dāng)對(duì)一個(gè)范圍內(nèi)的記錄加鎖的時(shí)候，我們稱之為間隙鎖。

當(dāng)使用范圍條件索引數(shù)據(jù)時(shí)，InnoDB 會(huì)對(duì)符合條件的數(shù)據(jù)索引項(xiàng)加鎖。對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙(GAP)”，InnoDB 也會(huì)對(duì)這個(gè)“間隙”加鎖，這就是間隙鎖。間隙鎖和行鎖合稱(Next-Key鎖)。

如果表中只有 11 條記錄，其 id 的值分別是 1,2,...,10，11 下面的 SQL：

Select * from table_gapwhere id > 10 for update;

這是一個(gè)范圍條件的檢索，InnoDB 不僅會(huì)對(duì)符合條件的 id 值為 10 的記錄加鎖，會(huì)對(duì) id 大于 10 的“間隙”加鎖，即使大于 10 的記錄不存在，例如 12，13。

InnoDB 使用間隙鎖的目的：

• 一方面是為了防止幻讀。對(duì)于上例，如果不使用間隙鎖，其他事務(wù)插入了 id 大于 10 的任何記錄，本事務(wù)再次執(zhí)行 select 語(yǔ)句，就會(huì)發(fā)生幻讀。
• 另一方面，也是為了滿足恢復(fù)和復(fù)制的需要。

 

間隙鎖圖

死鎖

兩個(gè)事務(wù)都需要獲得對(duì)方持有的排他鎖才能繼續(xù)完成任務(wù)，這種互相等待對(duì)方釋放資源的情況就是死鎖。

 

死鎖圖

檢測(cè)死鎖：InnoDB 存儲(chǔ)引擎能檢測(cè)到死鎖的循環(huán)依賴并立即返回一個(gè)錯(cuò)誤。

死鎖恢復(fù)：死鎖發(fā)生以后，只有部分或完全回滾其中一個(gè)事務(wù)，才能打破死鎖。

InnoDB 方法是，將持有最少行級(jí)排他鎖的事務(wù)回滾。在應(yīng)用程序設(shè)計(jì)時(shí)必須考慮處理死鎖，多數(shù)情況下重新執(zhí)行因死鎖回滾的事務(wù)即可。

避免死鎖：

• 在事務(wù)開(kāi)始時(shí)，如果有記錄要修改，先使用 SELECT... FOR UPDATE 語(yǔ)句獲取鎖，即使這些修改語(yǔ)句是在后面執(zhí)行。
• 在事務(wù)中，如果要更新記錄，直接申請(qǐng)排他鎖。而不是查詢時(shí)申請(qǐng)共享鎖、更新時(shí)再申請(qǐng)排他鎖。

這樣做會(huì)導(dǎo)致，當(dāng)申請(qǐng)排他鎖時(shí)，其他事務(wù)可能已經(jīng)獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。

簡(jiǎn)單來(lái)說(shuō)，如果你要更新記錄要做兩步操作，第一步查詢，第二步更新。就不要第一步上共享鎖，第二部上排他鎖了，直接在第一步就上排他鎖，搶占先機(jī)。

• 如果事務(wù)需要鎖定多個(gè)表，那么盡量按照相同的順序使用加鎖語(yǔ)句，可以降低產(chǎn)生死鎖的機(jī)會(huì)。
• 通過(guò) SELECT ... LOCK INSHARE MODE(共享鎖)獲取行的讀鎖后，如果當(dāng)前事務(wù)再需要對(duì)該記錄進(jìn)行更新操作，則很有可能造成死鎖。所以，如果要對(duì)行記錄進(jìn)行修改，直接上排他鎖。
• 改變事務(wù)隔離級(jí)別(事務(wù)隔離級(jí)別在后面詳細(xì)說(shuō)明)。

MySQL 鎖定情況的查詢

在實(shí)際開(kāi)發(fā)中無(wú)法避免數(shù)據(jù)被鎖的問(wèn)題，那么我們可以通過(guò)哪些手段來(lái)查詢鎖呢?

表級(jí)鎖可以通過(guò)兩個(gè)變量的查詢：

• Table_locks_immediate，產(chǎn)生表級(jí)鎖的次數(shù)。
• Table_locks_waited，數(shù)顯表級(jí)鎖而等待的次數(shù)。

行級(jí)鎖可以通過(guò)下面幾個(gè)變量查詢：

• Innodb_row_lock_current_waits，當(dāng)前正在等待鎖定的數(shù)量。
• Innodb_row_lock_time(重要)，從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)長(zhǎng)。
• Innodb_row_lock_time_avg(重要)，每次等待所花平均時(shí)間。
• Innodb_row_lock_time_max，從系統(tǒng)啟動(dòng)到現(xiàn)在等待最長(zhǎng)的一次花費(fèi)時(shí)間。
• Innodb_row_lock_waits(重要)，從系統(tǒng)啟動(dòng)到現(xiàn)在總共等待的次數(shù)。

MySQL 事務(wù)隔離級(jí)別

前面講的死鎖是因?yàn)椴l(fā)訪問(wèn)數(shù)據(jù)庫(kù)造成。當(dāng)多個(gè)事務(wù)同時(shí)訪問(wèn)數(shù)據(jù)庫(kù)，做并發(fā)操作的時(shí)候會(huì)發(fā)生以下問(wèn)題。

臟讀(dirty read)，一個(gè)事務(wù)在處理過(guò)程中，讀取了另外一個(gè)事務(wù)未提交的數(shù)據(jù)。未提交的數(shù)據(jù)稱之為臟數(shù)據(jù)。

 

臟讀例子

不可重復(fù)讀(non-repeatable read)，在事務(wù)范圍內(nèi)，多次查詢某條記錄，每次得到不同的結(jié)果。

第一個(gè)事務(wù)中的兩次讀取數(shù)據(jù)之間，由于第二個(gè)事務(wù)的修改，第一個(gè)事務(wù)兩次讀到的數(shù)據(jù)可能不一樣。

 

不可重復(fù)讀例子

幻讀(phantom read)，是事務(wù)非獨(dú)立執(zhí)行時(shí)發(fā)生的一種現(xiàn)象。

 

幻讀的例子

在同一時(shí)間點(diǎn)，數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)，同時(shí)對(duì)數(shù)據(jù)進(jìn)行讀寫操作，會(huì)造成數(shù)據(jù)不一致性。

 

四種隔離級(jí)別，解決事務(wù)并發(fā)問(wèn)題對(duì)照?qǐng)D

隔離性就是用來(lái)防止這種數(shù)據(jù)不一致的。事務(wù)隔離根據(jù)級(jí)別不同，從低到高包括：

• 讀未提交(read uncommitted)：它是最低的事務(wù)隔離級(jí)別，一個(gè)事務(wù)還沒(méi)提交時(shí)，它做的變更就能被別的事務(wù)看到。有臟讀的可能性。
• 讀提交(read committed)：保證一個(gè)事物提交后才能被另外一個(gè)事務(wù)讀取。另外一個(gè)事務(wù)不能讀取該事物未提交的數(shù)據(jù)。可避免臟讀的發(fā)生，但是可能會(huì)造成不可重復(fù)讀。
• 可重復(fù)讀(repeatable read MySQL 默認(rèn)方式)：多次讀取同一范圍的數(shù)據(jù)會(huì)返回第一次查詢的快照，即使其他事務(wù)對(duì)該數(shù)據(jù)做了更新修改。事務(wù)在執(zhí)行期間看到的數(shù)據(jù)前后必須是一致的。
• 串行化(serializable)：是最可靠的事務(wù)隔離級(jí)別。“寫”會(huì)加“排他鎖”，“讀”會(huì)加“共享鎖”。

當(dāng)出現(xiàn)讀寫鎖沖突的時(shí)候，后訪問(wèn)的事務(wù)必須等前一個(gè)事務(wù)執(zhí)行完成，所以事務(wù)執(zhí)行是串行的。可避免臟讀、不可重復(fù)讀、幻讀。

InnoDB 優(yōu)化建議

從鎖機(jī)制的實(shí)現(xiàn)方面來(lái)說(shuō)，InnoDB 的行級(jí)鎖帶來(lái)的性能損耗可能比表級(jí)鎖要高一點(diǎn)，但在并發(fā)方面的處理能力遠(yuǎn)遠(yuǎn)優(yōu)于 MyISAM 的表級(jí)鎖。這也是大多數(shù)公司的 MySQL 都是使用 InnoDB 模式的原因。

但是，InnoDB 也有脆弱的一面，下面提出幾個(gè)優(yōu)化建議供大家參考：

• 盡可能讓數(shù)據(jù)檢索通過(guò)索引完成，避免 InnoDB 因?yàn)闊o(wú)法通過(guò)索引加行鎖，而導(dǎo)致升級(jí)為表鎖的情況。換句話說(shuō)就是，多用行鎖，少用表鎖。
• 加索引的時(shí)候盡量準(zhǔn)確，避免造成不必要的鎖定影響其他查詢。
• 盡量減少給予范圍的數(shù)據(jù)檢索(間隙鎖)，避免因?yàn)殚g隙鎖帶來(lái)的影響，鎖定了不該鎖定的記錄。
• 盡量控制事務(wù)的大小，減少鎖定的資源量和鎖定時(shí)間。
• 盡量使用較低級(jí)別的事務(wù)隔離，減少 MySQL 因?yàn)槭聞?wù)隔離帶來(lái)的成本。

總結(jié)

 

MySQL 數(shù)據(jù)庫(kù)鎖的思維導(dǎo)圖

MySQL 的鎖主要分為表級(jí)鎖和行級(jí)鎖。MyISAM 引擎使用的是表級(jí)鎖，針對(duì)表級(jí)的共享鎖和排他鎖，可以通過(guò) concurrent_insert 和 low_priority_updates 參數(shù)來(lái)優(yōu)化。

InnoDB 支持表鎖和行鎖，根據(jù)索引來(lái)判斷如何選擇。行鎖有，行共享鎖和行排他鎖;表鎖有，意向共享鎖，意向排他鎖，表鎖是系統(tǒng)自己加上的;鎖范圍的是間隙鎖。遇到死鎖，我們?nèi)绾螜z測(cè)，恢復(fù)以及如何避免。

MySQL 有四個(gè)事務(wù)級(jí)別分別是，讀未提交，讀提交，可重復(fù)讀，串行化。他們的隔離級(jí)別依次升高。

通過(guò)隔離級(jí)別的設(shè)置，可以避免，臟讀，不可重復(fù)讀和幻讀的情況。最后，對(duì)于使用比較多的 InnoDB 引擎，提出了一些優(yōu)化建議。

作者：崔皓

簡(jiǎn)介：十六年開(kāi)發(fā)和架構(gòu)經(jīng)驗(yàn)，曾擔(dān)任過(guò)惠普武漢交付中心技術(shù)專家，需求分析師，項(xiàng)目經(jīng)理，后在創(chuàng)業(yè)公司擔(dān)任技術(shù)/產(chǎn)品經(jīng)理。善于學(xué)習(xí)，樂(lè)于分享。目前專注于技術(shù)架構(gòu)與研發(fā)管理。

【51CTO原創(chuàng)稿件，合作站點(diǎn)轉(zhuǎn)載請(qǐng)注明原文作者和出處為51CTO.com】