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

[[279429]] 

圖片來自 Pexels

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

MySQL 鎖分類

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

從鎖定資源的角度來看，MySQL 中的鎖分為：

• 表級鎖
• 行級鎖
• 頁面鎖

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

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

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

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

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

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

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

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

簡單來說，就是對數(shù)據(jù)的插入，更新操作比較多。從 MySQL 數(shù)據(jù)庫 5.5.8 版本開始，InnoDB 存儲引擎是默認的存儲引擎。

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

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

 

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

由于 MyISAM 存儲引擎不支持行級鎖，實際上后面討論的問題會圍繞三個象限的討論展開。

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

MyISAM 存儲引擎和表級鎖

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

 

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

MyISAM 存儲引擎支持表級鎖，并且支持兩種鎖模式：

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

MyISAM 優(yōu)化建議

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

由于這個特性在多進程并發(fā)插入同一張表的時候，就會因為排他鎖而進行等待。

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

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

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

注：空洞是行記錄被刪除以后，只是被標記為“已刪除”其存儲空間沒有被回收，也就是說沒有被物理刪除。由另外一個進程，異步對這個數(shù)據(jù)進行刪除。

因為空間長度問題，刪除以后的物理空間不能被新的記錄所使用，從而形成了空洞。

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

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

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

另外，當一個進程請求某個 MyISAM 表的讀鎖，另一個進程也請求同一表的寫鎖。

即使讀請求先到達，寫請求后到達，寫請求也會插到讀請求之前。因為 MySQL 的默認設置認為，寫請求比讀請求重要。

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

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

InnoDB 存儲引擎和表級鎖

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

 

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

InnoDB 存儲引擎表鎖。當沒有對數(shù)據(jù)表中的索引數(shù)據(jù)進行查詢時，會執(zhí)行表鎖操作。

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

這里介紹兩種意向鎖：

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

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

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

沒有設置主鍵同時也沒有設置任何索引(index)如下：

 

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

InnoDB 存儲引擎和行級鎖

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

 

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

InnoDB 存儲引擎行鎖，當數(shù)據(jù)查詢時針對索引數(shù)據(jù)進行時，會使用行級鎖。

共享鎖(S)：當一個事務讀取一條記錄的時候，不會阻塞其他事務對同一記錄的讀請求，但會阻塞對其的寫請求。當讀鎖釋放后，才會執(zhí)行其他事務的寫操作。

例如：select … lock in share mode

排他鎖(X)：當一個事務對一條記錄進行寫操作時，會阻塞其他事務對同一表的讀寫操作，當該鎖釋放后，才會執(zhí)行其他事務的讀寫操作。

例如：select … for update

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

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

 

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

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

如此，會造成性能問題，可以通過 innodb_lock_wait_timeout 來解決。innodb_lock_wait_timeout 是事務等待獲取資源的最長時間，單位為秒。如果超過時間還未分配到資源，則會返回應用失敗。

四種鎖的兼容情況：

 

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

如果一個事務請求的鎖模式與當前的鎖兼容， InnoDB 就將請求的鎖授予該事務;反之， 如果兩者不兼容，該事務就要等待鎖釋放。

間隙鎖

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

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

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

Select * from table_gapwhere id > 10 for update;

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

InnoDB 使用間隙鎖的目的：

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

 

間隙鎖圖

死鎖

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

 

死鎖圖

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

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

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

避免死鎖：

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

這樣做會導致，當申請排他鎖時，其他事務可能已經(jīng)獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。

簡單來說，如果你要更新記錄要做兩步操作，第一步查詢，第二步更新。就不要第一步上共享鎖，第二部上排他鎖了，直接在第一步就上排他鎖，搶占先機。

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

MySQL 鎖定情況的查詢

在實際開發(fā)中無法避免數(shù)據(jù)被鎖的問題，那么我們可以通過哪些手段來查詢鎖呢?

表級鎖可以通過兩個變量的查詢：

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

行級鎖可以通過下面幾個變量查詢：

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

MySQL 事務隔離級別

前面講的死鎖是因為并發(fā)訪問數(shù)據(jù)庫造成。當多個事務同時訪問數(shù)據(jù)庫，做并發(fā)操作的時候會發(fā)生以下問題。

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

 

臟讀例子

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

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

 

不可重復讀例子

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

 

幻讀的例子

在同一時間點，數(shù)據(jù)庫允許多個并發(fā)事務，同時對數(shù)據(jù)進行讀寫操作，會造成數(shù)據(jù)不一致性。

 

四種隔離級別，解決事務并發(fā)問題對照圖

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

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

當出現(xiàn)讀寫鎖沖突的時候，后訪問的事務必須等前一個事務執(zhí)行完成，所以事務執(zhí)行是串行的?？杀苊馀K讀、不可重復讀、幻讀。

InnoDB 優(yōu)化建議

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

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

• 盡可能讓數(shù)據(jù)檢索通過索引完成，避免 InnoDB 因為無法通過索引加行鎖，而導致升級為表鎖的情況。換句話說就是，多用行鎖，少用表鎖。
• 加索引的時候盡量準確，避免造成不必要的鎖定影響其他查詢。
• 盡量減少給予范圍的數(shù)據(jù)檢索(間隙鎖)，避免因為間隙鎖帶來的影響，鎖定了不該鎖定的記錄。
• 盡量控制事務的大小，減少鎖定的資源量和鎖定時間。
• 盡量使用較低級別的事務隔離，減少 MySQL 因為事務隔離帶來的成本。

總結(jié)

 

MySQL 數(shù)據(jù)庫鎖的思維導圖

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

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

MySQL 有四個事務級別分別是，讀未提交，讀提交，可重復讀，串行化。他們的隔離級別依次升高。

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

作者：崔皓

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

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