MySQL數據庫鎖定機制？這篇文章告訴你
1. MySQL 鎖定機制簡介
各存儲引擎使用三種類型鎖定機制
- 行級鎖定（row-level）
- 表級鎖定（table-level）
- 頁級鎖定（page-leve） ： 頁級鎖定介于行級鎖定與表級鎖定之間。
2. 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 中，以便后面做進一步分析。
