1. MySQL 鎖定機制簡介

各儲存引擎使用三種型別鎖定機制

行級鎖定（row-level）
表級鎖定（table-level）
頁級鎖定（page-leve）：頁級鎖定介於行級鎖定與表級鎖定之間。

2. MySQL 資料庫

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

3. MyISAM 表級鎖定主要分為兩種型別

讀鎖定，一個新客戶端在申請獲取讀鎖定資源的時候，需要滿足兩個條件：

請求鎖定的資源當前沒有被寫鎖定
寫鎖定等待佇列（Pending write-lock queue）中沒有更高優先順序的寫鎖定在等待
（隻影響寫操作）

寫鎖定

（影響讀操作，同時也影響寫操作）

4. 佇列

Mysql種主要分 4 中佇列來維護這兩種鎖定：兩個存放當前正在鎖定的讀和寫鎖定資訊，另外兩個存放等待中的讀寫鎖定西資訊，如下：

Current read-lock queue (lock->read)
Pending read-lock queue (lock->read_wait)
Current write-lock queue (lock->write)
Pending write-lock queue (lock->write_wait)

5. InnoDB 的行級鎖定分為四種型別

共享鎖（有叫做：讀鎖）

允許一個事務去讀一行，阻止其他事務獲得相同資料的排它鎖。

排他鎖（有叫做：寫鎖）

允許獲得排它鎖的事務更新資料，阻止其他事務

意向共享鎖
意向排他鎖

6. InnoDB 間隙鎖

InnoDB 的鎖定是透過在指向資料記錄的第一個索引鍵之前和最後一個索引鍵之後的空域空間標記鎖定資訊實現的。這種鎖定方式被稱為 “NEXT-KEY locking”（間隙鎖）
間隙鎖弱點：鎖定一個範圍之後，即使某些不存在的鍵值也會被無辜鎖定，造成鎖定的時候無法插入鍵值鎖定內的任何資料。
透過索引實現鎖定的方式存在其他幾個較大的效能隱患：

當 Query 無法利用索引的時候，InnoDB 會放棄使用行級鎖定而改用表級鎖定，造成併發效能降低；

當 Query 使用的索引並不包含所有過濾條件時，資料檢索使用到的索引鍵中的資料可能有部分不屬於 Query 的結果集行列，但是也會被鎖定，因為間隙鎖鎖定的是一個範圍，而不是具體的索引鍵。
當 Query 在使用索引定位資料的時候，如果使用的索引鍵一樣但訪問的資料行不同（索引只是過濾條件的一部分），他們一樣會被鎖定。

7. MyISAM 表鎖最佳化建議

縮短鎖定時間

儘量減少大的複雜 Query，將複雜 Query 拆分成幾個小的 Query 執行。
儘可能地建立足夠高效的索引，讓資料檢索更迅速。
儘量讓 MyISAM 儲存引擎的表只存放必要的資訊，控制欄位型別。
利用合適的機會最佳化 MyISAM 表資料檔案。

分離能並行的操作

concurrent_insert = 2，無論 MyISAM 儲存引擎的表資料檔案的中間部分是否存在因為刪除資料而留下的空閒空間，都允許在資料檔案尾部進行 Concurrent Insert。
concurrent_insert = 1，MyISAM 儲存引擎表資料檔案中間不存在空閒空間的時候，可以從檔案尾部進行 Concurrent Insert。
concurrent_insert = 0，無論 MyISAM 儲存引擎的表資料檔案的中間部分是否存在因為刪除資料而留下的空閒空間，都不允許 Concurrent Insert。（讀鎖時，不允許插入）
MyISAM 並非只能完全的序列化，MyISAM 儲存引擎還有一個特性 Concurrent Insert（併發插入）的特性。
MyISAM 儲存引擎有一個控制是否開啟 Concurrent insert 功能的引數選項：concurrent_insert 可以設定為 0/1/2：具體如下：

合理利用讀寫優先順序

表級鎖定預設情況下寫優先順序大於讀，如果讀操作多的時候，可以設定讀優先順序高，可設定引數 low_priority_updates = 1。

8. InnoDB 行鎖最佳化建議

儘可能讓所有的資料檢索都透過索引來完成，從而避免 InnoDB 因為無法透過索引鍵加鎖而升級為表級鎖定
合理設計索引，讓 InnoDB 在索引鍵上加鎖的時候儘可能準確，儘可能地縮小鎖定範圍，避免造成不必要的鎖定而影響其他 Query 的執行。
儘可能減少基於範圍的資料檢索過濾條件，避免因間隙鎖帶來的負面影響而鎖定了不該鎖定的記錄。
儘量控制事務大小，減少鎖定的資源量和鎖定的時間長度。
在業務環境允許的情況下，儘量使用較低階別的事務隔離，減少 MySQL 因為實現事務隔離級別所帶來的附加成本。

9. 系統鎖定爭用情況查詢

MySQL 內部有兩組專用的狀態變數記錄系統內部資源爭用情況。

表級鎖定的爭用狀態變數

mysql> show status like ‘table%’;

Table_locks_immediate：產生表級鎖定的次數；
Table_locks_waited：出現表級鎖定爭用而發生等待的次數

Table_locks_immediate 值大於 Table_locks_waited 5000 是比較合適的，在大就需要分析問題所在。
兩個狀態值都是從系統啟動後開始記錄，每出現一次加 1，如果這裡 Table_locks_waited 狀態值比較高，說明表級鎖定爭用嚴重，需進一步分析。

InnoDB 行級鎖定狀態變數記錄

sql> show status like ‘innodb_row_lock%’;

Innodb_row_lock_current_waites：當前正在等待鎖定的數量；
Innodb_row_lock_time：從系統啟動到現在鎖定總時間長度；
Innodb_row_lock_time_avg：每次等待所花平均時間；
Innodb_row_lock_time_max：從系統啟動到現在等待最長的一次所花的時間；
Innodb_row_lock_waits：從系統啟動到現在總等待次數。

5 個狀態，比較重要的是 Innodb_row_lock_time_avg（等待平均時長），Innodb_row_lock_waits（等待總次數）及 Innodb_row_lock_time（等待總時長）

10. 除此之外

InnoDB除了提供以上 5 個系統狀態變數外，還提供了更為豐富的即時狀態資訊，實現方法如下：

建立 InnoDB Monitor 表來開啟 InnoDB 的 monitor 功能

mysql > create table innodb_monitor(a int) engine=innodb;

然後執行 ”show innodb status” 檢視詳細資訊

為什麼建立 innodb_monitor 表？

建立該表就是告訴 InnoDB 我們要開始監控他的詳細資訊，然後 InnoDB 就會將比較詳細的事務級鎖定資訊記錄到 MySQL 的 error log 中，以便後面做進一步分析。

MySQL 資料庫鎖定機制？這篇文章為你分析清楚~

相關文章