MySQL效能調優與架構設計–第七章MySQL資料庫鎖定機制

為了保證資料的一直完整性，出現了各種鎖定機制。而鎖定機制決定了一個資料庫的並發處理能力和效能。

資料庫的鎖定機制簡單來說就是資料庫為了保證資料的一致性而使各種共用資源在被並發訪問時變得有序所設計的一種規則。

三種類型的鎖定機制：行級鎖定、頁級鎖定和表級鎖定。
行級鎖定：最小粒度鎖定，更大的並發處理能力，但是也最容易死結，且對資源消耗比較大。
表級鎖定：最大粒度鎖定，並發最艱難，消耗資源最少。
頁級鎖定：並發能力和效能開銷，位於行級鎖定和表級鎖定之間。

在MySQL資料庫中，使用表級鎖定的是MyISAM、Memory、CSV、等一些非事務性儲存引擎，行級鎖定的主要是InnoDB和NDB Cluster，頁級鎖定主要是BerkeleyDB。

各種鎖定機制分析：

表級鎖定：
MySQL的表級鎖定主要分為兩種類型，一種是讀鎖定，另一種是寫鎖定。在MySQL中，主要通過四個
隊列來維護這兩種鎖定：兩個存放當前正在鎖定中的讀和寫鎖定資訊，另外兩個存放等待中的讀寫鎖定
資訊，如下：
Currentread-lockqueue(lock->read)
Pendingread-lockqueue(lock->read_wait)
Currentwrite-lockqueue(lock->write)
Pendingwrite-lockqueue(lock->write_wait)

鎖定類型 說明
IGNORE 當發生鎖請求的時候內部互動使用，在鎖定結構和隊列
中並不會有任何資訊儲存
UNLOCK 釋放鎖定請求的互動用所類型
READ 普通讀鎖定
WRITE 普通寫鎖定
READ_WITH_SHARED_LOCKS 在Innodb中使用到，由如下方式產生
如：SELECT...LOCKINSHAREMODE
READ_HIGH_PRIORITY 高優先順序讀鎖定
READ_NO_INSERT 不允許ConcurentInsert的鎖定
WRITE_ALLOW_WRITE 這個類型實際上就是當由儲存引擎自行處理鎖定的時
候，mysqld允許其他的線程再擷取讀或者寫鎖定，因為
即使資源衝突，儲存引擎自己也會知道怎麼來處理
WRITE_ALLOW_READ 這種鎖定發生在對錶做DDL（ALTER TABLE ...）的時
候，MySQL可以允許其他線程擷取讀鎖定，因為MySQL是
通過重建整個表然後再RENAME而實現的該功能，所在整
個過程原表仍然可以提供讀服務
WRITE_CONCURRENT_INSERT 進行中ConcurentInsert時候所使用的鎖定方式，該
鎖定進行的時候，除了READ_NO_INSERT之外的其他任何
讀鎖定請求都不會被阻塞
WRITE_DELAYED 在使用INSERTDELAYED時候的鎖定類型
WRITE_LOW_PRIORITY 顯 示 聲 明 的 低 級 別 鎖 定 方 式 ， 通 過 設 置
LOW_PRIORITY_UPDAT=1而產生
WRITE_ONLY 當在操作過程中某個鎖定異常中斷之後系統內部需要進
行CLOSETABLE操作，在這個過程中出現的鎖定類型就
是WRITE_ONLY

讀鎖定：
一個新的用戶端請求在申請擷取讀鎖定資源的時候，需要滿足兩個條件：
1、請求鎖定資源當前沒有被寫鎖定；
2、寫鎖定等待隊列（Pendingwrite-lockqueue）中沒有更高優先順序的寫鎖定等待；

Innodb鎖定模式及實現機制：
InnoDB的鎖定機制分為共用鎖定和獨佔鎖定。為了實現表級鎖定，還存在意圖共用鎖和意向獨佔鎖定。
共用鎖定（S）獨佔鎖定（X）意圖共用鎖（IS）意向獨佔鎖定（IX）
共用鎖定（S） 相容 衝突 相容
衝突
獨佔鎖定（X） 衝突 衝突
衝突 衝突
意 向 共 享 鎖（IS）相容 衝突
相容 相容
意 向 排 他 鎖（IX）衝突 衝突
相容 相容

Oracle鎖定資料主要是通過在需要鎖定的某行記錄所在的物理Block上的事務槽上添加鎖定資訊。
InnoDB的鎖定機制是通過在指向資料記錄的第一個索引鍵之前和最後一個索引鍵之後的空域空間標記鎖定資訊實現，被成為“NEXT-KEY locking”。
間隙鎖有一個比較致命的弱點，就是當鎖定一個範圍索引值之後，即使某些不存在的索引值也會被無辜
的鎖定，而造成在鎖定的時候無法插入鎖定索引值範圍內的任何資料。在某些情境下這可能會對效能造成
很大的危害。而Innodb給出的解釋是為了組織幻讀的出現，所以他們選擇的間隙鎖來實現鎖定。

除了間隙鎖給Innodb帶來效能的負面影響之外，通過索引實現鎖定的方式還存在其他幾個較大的效能隱患：
●當Query無法利用索引的時候，Innodb會放棄使用行層級鎖定而改用表層級的鎖定，造成並發效能的降低；
●當Quuery使用的索引並不包含所有過濾條件的時候，資料檢索使用到的索引鍵所只想的資料可
能有部分並不屬於該Query的結果集的行列，但是也會被鎖定，因為間隙鎖鎖定的是一個範
圍，而不是具體的索引鍵；
●當Query在使用索引定位元據的時候，如果使用的索引鍵一樣但訪問的資料行不同的時候（索引只是過濾條件的一部分），一樣會被鎖定。

*********
上面的內容兩點需要說明：
1、MyISAM在5.0和5.5兩個版本中測試， 是測試不出來表級鎖定的，因為對於非事務的引擎是一直自動認可的（對於非事務的表，如MyISAM表或記憶體表（Memory Table），改變AUTOCOMMIT值沒有意義，這些表本質上一直操作在AUTOCOMMIT模式）。
2、InnoDB在5.0中，是遵循的索引鍵鎖定機制，也就是沒索引鍵會升級為表鎖定；但是5.5，沒有索引鍵，也是行級索引機制。
*********

Innodb實現的在ISO／ANSISQL92規範中所定義的ReadUnCommited，ReadCommited，Repeatable
Read和Serializable這四種交易隔離等級。同時，為了保證資料在事務中的一致性，實現了多版本資料
訪問。

合理利用鎖定機制 最佳化MySQL：

MyISAM表鎖定最佳化建議：
關鍵是如何提高並發度。
縮短鎖定時間：
·盡量減少打的複雜的Query，將複雜的Query分拆成幾個小的Query分步進行；
·儘可能的建立足夠高效的索引，讓資料檢索更迅速；
·盡量讓MyISAM儲存引擎表只存放必須要的資訊，控制欄位類型；
·利用合適的機會最佳化MyISAM表資料檔案。
分離能力並行操作：
MyISAM儲存引擎有一個控制是否開啟ConcurrentInsert功能的參數選項：concurrent_insert，可
以設定為0，1或者2。三個值的具體說明如下：
a) concurrent_insert=2，無論MyISAM儲存引擎的表資料檔案的中間部分是否存在因為刪除資料
而留下的空閑空間，都允許在資料檔案尾部進行ConcurrentInsert;
b) concurrent_insert=1，當MyISAM儲存引擎表資料檔案中間不存在空閑空間的時候，可以從文
件尾部進行ConcurrentInsert;
c) concurrent_insert=0，無論MyISAM儲存引擎的表資料檔案的中間部分是否存在因為刪除資料
而留下的空閑空間，都不允許ConcurrentInsert。
合理利用讀寫優先順序：
在本章各種鎖定分析一節中我們瞭解到了MySQL的表級鎖定對於讀和寫是有不同優先順序設定的，默
認情況下是寫優先順序要大於讀優先順序。所以，如果我們可以根據各自系統內容的差異決定讀與寫的優先
級。如果我們的系統是一個以讀為主，而且要優先保證查詢效能的話，我們可以通過設定系統參數選項
low_priority_updates=1，將寫的優先順序設定為比讀的優先順序低，即可讓告訴MySQL盡量先處理讀請
求。當然，如果我們的系統需要有限保證資料寫入的效能的話，則可以不用設定low_priority_updates
參數了。

Innodb行鎖最佳化建議：
要想合理利用Innodb的行級鎖定，做到揚長避短，我們必須做好以下工作：
a) 儘可能讓所有的資料檢索都通過索引來完成，從而避免Innodb因為無法通過索引鍵加鎖而升級
為表級鎖定；
b) 合理設計索引，讓Innodb在索引鍵上面加鎖的時候儘可能準確，儘可能的縮小鎖定範圍，避免
造成不必要的鎖定而影響其他Query的執行；
c) 儘可能減少基於範圍的資料檢索過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定
的記錄；
d) 盡量控制事務的大小，減少鎖定資源量和鎖定時間長度；
e) 在業務環境允許的情況下，盡量使用較低層級的事務隔離，以減少MySQL因為實現事務隔離級
別所帶來的附加成本；
由於Innodb的行級鎖定和事務性，所以肯定會產生死結，下面是一些比較常用的減少死結產生機率
的的小建議，讀者朋友可以根據各自的業務特點針對性的嘗試：
a) 類似業務模組中，儘可能按照相同的訪問順序來訪問，防止產生死結；
b) 在同一個事務中，儘可能做到一次鎖定所需要的所有資源，減少死結產生機率；
c) 對於非常容易產生死結的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死結
產生的機率；

系統鎖定爭用情況查詢：
對於兩種鎖定層級，MySQL內部有兩組專門的狀態變數記錄系統內部鎖資源爭用情況，我們先看看
MySQL實現的表級鎖定的爭用狀態變數：
mysql>showstatuslike'table%';
+-----------------------+-------+
|Variable_name |Value|
+-----------------------+-------+
|Table_locks_immediate|100 |
|Table_locks_waited |0 |
+-----------------------+-------+
這裡有兩個狀態變數記錄MySQL內部表級鎖定的情況，兩個變數說明如下：
●Table_locks_immediate：產生表級鎖定的次數；
●Table_locks_waited：出現表級鎖定爭用而發生等待的次數；

對於Innodb所使用的行級鎖定，系統中是通過另外一組更為詳細的狀態變數來記錄的，如下：
mysql>showstatuslike'innodb_row_lock%';
+-------------------------------+--------+
|Variable_name |Value |
+-------------------------------+--------+
|Innodb_row_lock_current_waits|0 |
|Innodb_row_lock_time |490578|
|Innodb_row_lock_time_avg |37736 |
|Innodb_row_lock_time_max |121411|
|Innodb_row_lock_waits |13 |
+-------------------------------+--------+
●Innodb_row_lock_current_waits：當前正在等待鎖定的數量；
●Innodb_row_lock_time：從系統啟動到現在鎖定總時間長度；
●Innodb_row_lock_time_avg：每次等待所花平均時間；
●Innodb_row_lock_time_max：從系統啟動到現在等待最常的一次所花的時間；
●Innodb_row_lock_waits：系統啟動後到現在總共等待的次數；