【51CTO.com原創稿件】隨著 IT 技術的飛速發展，各種技術層出不窮，讓人眼花繚亂。儘管技術在不斷更新換代，但是有些技術依舊被一代代 IT 人使用至今。
圖片來自 Pexels
MySQL 就是其中之一，它經歷了多個版本迭代。數據庫鎖是 MySQL 數據引擎的一部分，今天我們就一起來學習 MySQL 的數據庫鎖和它的優化。
MySQL 鎖分類
當多個事務或者進程訪問同一個資源的時候，為了保證數據的一致性，就需要用到鎖機制。
從鎖定資源的角度來看，MySQL 中的鎖分為：
- 表級鎖
- 行級鎖
- 頁面鎖
表級鎖：對整張表加鎖。開銷小，加鎖快;不會出現死鎖;鎖定粒度大，發生鎖衝突的概率最高，併發度最低。
行級鎖：對某行記錄加鎖。開銷大，加鎖慢;會出現死鎖;鎖定粒度最小，發生鎖衝突的概率最低，併發度也最高。
頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間，併發度一般。
在實際開發過程中，主要會使用到表級鎖和行級鎖兩種。既然鎖是針對資源的，那麼這些資源就是數據，在 MySQL 提供插件式存儲引擎對數據進行存儲。
插件式存儲引擎的好處是，開發人員可以根據需要選擇適合的存儲引擎。
在眾多的存儲引擎中，有兩種引擎被比較多的使用，他們分別是：
- MyISAM 存儲引擎，它不支持事務、表鎖設計，支持全文索引，主要面向一些在線分析處理(OLAP)數據庫應用。說白了主要就是查詢數據，對數據的插入，更新操作比較少。
- InnoDB 存儲引擎，它支持事務，其設計目標主要面向在線事務處理(OLTP)的應用。
其特點是行鎖設計、支持外鍵，並支持類似於 Oracle 的非鎖定讀，即默認讀取操作不會產生鎖。
簡單來說，就是對數據的插入，更新操作比較多。從 MySQL 數據庫 5.5.8 版本開始，InnoDB 存儲引擎是默認的存儲引擎。
上面兩種存儲引擎在處理多進程數據操作的時候是如何表現的，就是我們接下來要討論的問題。
為了讓整個描述更加清晰，我們將表級鎖和行級鎖以及 MyISAM，InnoDB 存儲引擎，就形成了一個 2*2 的象限。
2*2 錶行鎖，MyISAM，InnoDB 示意圖
由於 MyISAM 存儲引擎不支持行級鎖，實際上後面討論的問題會圍繞三個象限的討論展開。
從內容上來看，InnoDB 作為使用最多的存儲引擎遇到的問題和值得注意的地方較多，也是本文的重點。
MyISAM 存儲引擎和表級鎖
首先，來看第一象限的內容：
2*2 錶行鎖，MyISAM，InnoDB 示意圖-第一象限
MyISAM 存儲引擎支持表級鎖，並且支持兩種鎖模式：
- 對 MyISAM 表的讀操作(共享鎖)，不會阻塞其他進程對同一表的讀請求，但會阻塞對其的寫請求。當讀鎖釋放後，才會執行其他進程的寫操作。
- 對 MyISAM 表的寫操作(排他鎖)，會阻塞其他進程對同一表的讀寫操作，當該鎖釋放後，才會執行其他進程的讀寫操作。
MyISAM 優化建議
在使用 MyISAM 存儲引擎時。執行 SQL 語句，會自動為 SELECT 語句加上共享鎖，為 UDI(更新，刪除，插入)操作加上排他鎖。
由於這個特性在多進程併發插入同一張表的時候，就會因為排他鎖而進行等待。
因此可以通過配置 concurrent_insert 系統變量，來控制其併發的插入行為。
①concurrent_insert=0 時，不允許併發插入。
②concurrent_insert=1 時，如果 MyISAM 表中沒有空洞(即表中沒有被刪除的行)，允許一個進程讀表時，另一個進程向表的尾部插入記錄(MySQL 默認設置)。
注：空洞是行記錄被刪除以後，只是被標記為“已刪除”其存儲空間沒有被回收，也就是說沒有被物理刪除。由另外一個進程，異步對這個數據進行刪除。
因為空間長度問題，刪除以後的物理空間不能被新的記錄所使用，從而形成了空洞。
③concurrent_insert=2 時，無論 MyISAM 表中有沒有空洞，都允許在表尾併發插入記錄。
如果在數據插入的時候，沒有併發刪除操作的話，可以嘗試把 concurrent_insert 設置為 1。
反之，在數據插入的時候有刪除操作且量較大時，也就是會產生“空洞”的時候，就需要把 concurrent_insert 設置為 2。
另外，當一個進程請求某個 MyISAM 表的讀鎖，另一個進程也請求同一表的寫鎖。
即使讀請求先到達，寫請求後到達，寫請求也會插到讀請求之前。因為 MySQL 的默認設置認為，寫請求比讀請求重要。
我們可以通過 low_priority_updates 來調節讀寫行為的優先級：
- 數據庫以讀為主時，要優先保證查詢性能時，可通過 low_priority_updates=1 設置讀優先級高於寫優先級。
- 數據庫以寫為主時，則不用設置 low_priority_updates 參數。
InnoDB 存儲引擎和表級鎖
再來看看第二象限的內容：
2*2 錶行鎖，MyISAM，InnoDB 示意圖-第二象限
InnoDB 存儲引擎表鎖。當沒有對數據表中的索引數據進行查詢時，會執行表鎖操作。
上面是 InnoDB 實現行鎖，同時它也可以實現表鎖。其方式就是意向鎖(Intention Locks)。
這裡介紹兩種意向鎖：
- 意向共享鎖(IS)：事務打算給數據行加行共享鎖，事務在給一個數據行加共享鎖前，必須先取得該表的 IS 鎖。
- 意向排他鎖(IX)：事務打算給數據行加行排他鎖，事務在給一個數據行加排他鎖前，必須先取得該表的 IX 鎖。
注：意向共享鎖和意向排他鎖是數據庫主動加的，不需要我們手動處理。對於 UPDATE、DELETE 和 INSERT 語句，InnoDB 會自動給數據集加排他鎖。
InnoDB表鎖的實現方式：假設有一個表 test2，有兩個字段分別是 id 和 name。
沒有設置主鍵同時也沒有設置任何索引(index)如下：
InnoDB 表鎖實現方式圖
InnoDB 存儲引擎和行級鎖
第四象限我們使用的比較多，討論的內容也相對多些：
2*2 錶行鎖，MyISAM，InnoDB 示意圖-第四象限
InnoDB 存儲引擎行鎖，當數據查詢時針對索引數據進行時，會使用行級鎖。
共享鎖(S)：當一個事務讀取一條記錄的時候，不會阻塞其他事務對同一記錄的讀請求，但會阻塞對其的寫請求。當讀鎖釋放後，才會執行其他事務的寫操作。
例如：select … lock in share mode
排他鎖(X)：當一個事務對一條記錄進行寫操作時，會阻塞其他事務對同一表的讀寫操作，當該鎖釋放後，才會執行其他事務的讀寫操作。
例如：select … for update
行鎖的實現方式：假設有一個表 test1，有兩個字段分別是 id 和 name。
id 作為主鍵同時也是 table 的索引(index)如下：
InnoDB 行鎖實現方式圖
在高併發的情況下，多個事務同時請求更新數據，由於資源被佔用等待事務增多。
如此，會造成性能問題，可以通過 innodb_lock_wait_timeout 來解決。innodb_lock_wait_timeout 是事務等待獲取資源的最長時間，單位為秒。如果超過時間還未分配到資源，則會返回應用失敗。
四種鎖的兼容情況：
共享鎖，排他鎖，意向共享鎖，意向排他鎖兼容圖例
如果一個事務請求的鎖模式與當前的鎖兼容， InnoDB 就將請求的鎖授予該事務;反之， 如果兩者不兼容，該事務就要等待鎖釋放。
間隙鎖
前面談到行鎖是針對一條記錄進行加鎖。當對一個範圍內的記錄加鎖的時候，我們稱之為間隙鎖。
當使用範圍條件索引數據時，InnoDB 會對符合條件的數據索引項加鎖。對於鍵值在條件範圍內但並不存在的記錄，叫做“間隙(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 的任何記錄，本事務再次執行 select 語句，就會發生幻讀。
- 另一方面，也是為了滿足恢復和複製的需要。
間隙鎖圖
死鎖
兩個事務都需要獲得對方持有的排他鎖才能繼續完成任務，這種互相等待對方釋放資源的情況就是死鎖。
死鎖圖
檢測死鎖：InnoDB 存儲引擎能檢測到死鎖的循環依賴並立即返回一個錯誤。
死鎖恢復：死鎖發生以後，只有部分或完全回滾其中一個事務，才能打破死鎖。
InnoDB 方法是，將持有最少行級排他鎖的事務回滾。在應用程序設計時必須考慮處理死鎖，多數情況下重新執行因死鎖回滾的事務即可。
避免死鎖：
- 在事務開始時，如果有記錄要修改，先使用 SELECT... FOR UPDATE 語句獲取鎖，即使這些修改語句是在後面執行。
- 在事務中，如果要更新記錄，直接申請排他鎖。而不是查詢時申請共享鎖、更新時再申請排他鎖。
這樣做會導致，當申請排他鎖時，其他事務可能已經獲得了相同記錄的共享鎖，從而造成鎖衝突，甚至死鎖。
簡單來說，如果你要更新記錄要做兩步操作，第一步查詢，第二步更新。就不要第一步上共享鎖，第二部上排他鎖了，直接在第一步就上排他鎖，搶佔先機。
- 如果事務需要鎖定多個表，那麼儘量按照相同的順序使用加鎖語句，可以降低產生死鎖的機會。
- 通過 SELECT ... LOCK INSHARE MODE(共享鎖)獲取行的讀鎖後，如果當前事務再需要對該記錄進行更新操作，則很有可能造成死鎖。所以，如果要對行記錄進行修改，直接上排他鎖。
- 改變事務隔離級別(事務隔離級別在後面詳細說明)。
MySQL 鎖定情況的查詢
在實際開發中無法避免數據被鎖的問題，那麼我們可以通過哪些手段來查詢鎖呢?
表級鎖可以通過兩個變量的查詢：
- Table_locks_immediate，產生表級鎖的次數。
- Table_locks_waited，數顯表級鎖而等待的次數。
行級鎖可以通過下面幾個變量查詢：
- Innodb_row_lock_current_waits，當前正在等待鎖定的數量。
- Innodb_row_lock_time(重要)，從系統啟動到現在鎖定總時長。
- Innodb_row_lock_time_avg(重要)，每次等待所花平均時間。
- Innodb_row_lock_time_max，從系統啟動到現在等待最長的一次花費時間。
- Innodb_row_lock_waits(重要)，從系統啟動到現在總共等待的次數。
MySQL 事務隔離級別
前面講的死鎖是因為併發訪問數據庫造成。當多個事務同時訪問數據庫，做併發操作的時候會發生以下問題。
髒讀(dirty read)，一個事務在處理過程中，讀取了另外一個事務未提交的數據。未提交的數據稱之為髒數據。
髒讀例子
不可重複讀(non-repeatable read)，在事務範圍內，多次查詢某條記錄，每次得到不同的結果。
第一個事務中的兩次讀取數據之間，由於第二個事務的修改，第一個事務兩次讀到的數據可能不一樣。
不可重複讀例子
幻讀(phantom read)，是事務非獨立執行時發生的一種現象。
幻讀的例子
在同一時間點，數據庫允許多個併發事務，同時對數據進行讀寫操作，會造成數據不一致性。
四種隔離級別，解決事務併發問題對照圖
隔離性就是用來防止這種數據不一致的。事務隔離根據級別不同，從低到高包括：
- 讀未提交(read uncommitted)：它是最低的事務隔離級別，一個事務還沒提交時，它做的變更就能被別的事務看到。有髒讀的可能性。
- 讀提交(read committed)：保證一個事物提交後才能被另外一個事務讀取。另外一個事務不能讀取該事物未提交的數據。可避免髒讀的發生，但是可能會造成不可重複讀。
- 可重複讀(repeatable read MySQL 默認方式)：多次讀取同一範圍的數據會返回第一次查詢的快照，即使其他事務對該數據做了更新修改。事務在執行期間看到的數據前後必須是一致的。
- 串行化(serializable)：是最可靠的事務隔離級別。“寫”會加“排他鎖”，“讀”會加“共享鎖”。
當出現讀寫鎖衝突的時候，後訪問的事務必須等前一個事務執行完成，所以事務執行是串行的。可避免髒讀、不可重複讀、幻讀。
InnoDB 優化建議
從鎖機制的實現方面來說，InnoDB 的行級鎖帶來的性能損耗可能比表級鎖要高一點，但在併發方面的處理能力遠遠優於 MyISAM 的表級鎖。這也是大多數公司的 MySQL 都是使用 InnoDB 模式的原因。
但是，InnoDB 也有脆弱的一面，下面提出幾個優化建議供大家參考：
- 儘可能讓數據檢索通過索引完成，避免 InnoDB 因為無法通過索引加行鎖，而導致升級為表鎖的情況。換句話說就是，多用行鎖，少用表鎖。
- 加索引的時候儘量準確，避免造成不必要的鎖定影響其他查詢。
- 儘量減少給予範圍的數據檢索(間隙鎖)，避免因為間隙鎖帶來的影響，鎖定了不該鎖定的記錄。
- 儘量控制事務的大小，減少鎖定的資源量和鎖定時間。
- 儘量使用較低級別的事務隔離，減少 MySQL 因為事務隔離帶來的成本。
總結
MySQL 數據庫鎖的思維導圖
MySQL 的鎖主要分為表級鎖和行級鎖。MyISAM 引擎使用的是表級鎖，針對表級的共享鎖和排他鎖，可以通過 concurrent_insert 和 low_priority_updates 參數來優化。
InnoDB 支持表鎖和行鎖，根據索引來判斷如何選擇。行鎖有，行共享鎖和行排他鎖;表鎖有，意向共享鎖，意向排他鎖，表鎖是系統自己加上的;鎖範圍的是間隙鎖。遇到死鎖，我們如何檢測，恢復以及如何避免。
MySQL 有四個事務級別分別是，讀未提交，讀提交，可重複讀，串行化。他們的隔離級別依次升高。
通過隔離級別的設置，可以避免，髒讀，不可重複讀和幻讀的情況。最後，對於使用比較多的 InnoDB 引擎，提出了一些優化建議。
簡介：十六年開發和架構經驗，曾擔任過惠普武漢交付中心技術專家，需求分析師，項目經理，後在創業公司擔任技術/產品經理。善於學習，樂於分享。目前專注於技術架構與研發管理。
閱讀更多 51CTO 的文章
