MySQL 的鎖機制雖然複雜,但若能掌握其精髓,將能為資料庫效能帶來質的飛躍。本文將透過大量實戰範例,深入淺出地剖析 MySQL 鎖的類型、特性、常見問題及解決策略,助你構建高併發、高效能的資料庫應用。
鎖是資料庫協調多進程或線程併發訪問共享資源的核心機制。與其他資料庫相比,MySQL 鎖機制的最大特點在於不同儲存引擎支援不同的鎖機制:
| 鎖類型 | 開銷/加鎖速度 | 死鎖 | 鎖定粒度 | 併發效能 |
|---|---|---|---|---|
| 表級鎖 | 開銷小,加鎖快 | 不會出現 | 粒度大 | 衝突概率最高,併發度最低 |
| 行級鎖 | 開銷大,加鎖慢 | 會出現 | 粒度最小 | 衝突概率最低,併發度最高 |
| 頁面鎖 | 介於兩者之間 | 會出現 | 介於兩者之間 | 併發度一般 |
無法一概而論哪種鎖更好,需視應用場景而定:表級鎖適合以查詢為主、少量索引條件更新的應用(如 Web 應用);行級鎖適合大量索引條件併發更新少量不同資料、同時有併發查詢的 OLTP 系統。
透過檢查狀態變量分析鎖爭用:
mysql> SHOW STATUS LIKE 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 2979 |
| Table_locks_waited | 0 |
+-----------------------+-------+
若 Table_locks_waited 值較高,說明存在嚴重的表級鎖爭用。
MySQL 表級鎖有兩種模式:
兼容性矩陣:
| 請求鎖模式 | 無鎖 | 讀鎖 | 寫鎖 |
|---|---|---|---|
| 讀鎖 | 兼容 | 兼容 | 衝突 |
| 寫鎖 | 兼容 | 衝突 | 衝突 |
結論:讀操作不阻塞其他讀,但阻塞寫;寫操作阻塞其他讀和寫;讀寫操作串行執行。
MyISAM 執行查詢(SELECT)前自動加讀鎖,執行更新(UPDATE、DELETE、INSERT)前自動加寫鎖,無需用戶干預。顯式加鎖(LOCK TABLES)主要用於模擬事務操作,實現多表一致性讀取。例如:
LOCK TABLES orders READ LOCAL, order_detail READ LOCAL;
SELECT SUM(total) FROM orders;
SELECT SUM(subtotal) FROM order_detail;
UNLOCK TABLES;
關鍵注意點:
LOCAL 選項允許滿足併發插入條件時,其他用戶在表尾併發插入記錄LOCK TABLES 後,只能訪問顯式加鎖的表;加讀鎖則只能查詢,不能更新MyISAM 透過 concurrent_insert 系統變量控制併發插入行為:
善用此特性可解決同一表查詢與插入的鎖爭用。建議設為 2,並定期用 OPTIMIZE TABLE 整理碎片。
MyISAM 預設寫優先:即使讀請求先到,寫請求也會插到讀鎖請求之前。這導致大量更新時查詢難以獲取讀鎖,甚至永久阻塞。可透過以下方式調節:
low-priority-updates 使讀請求優先SET LOW_PRIORITY_UPDATES=1 降低該連線更新優先級LOW_PRIORITY 屬性max_write_lock_count,讀鎖達到閾值時暫時降低寫優先級此外,應避免長時間運行的查詢操作,必要時拆解為多步驟或安排在離峰時段執行。
InnoDB 支援事務(ACID 特性),引入行級鎖同時帶來新問題。並發事務處理可能導致:更新丟失、髒讀、不可重複讀、幻讀。ISO/ANSI SQL92 定義四大隔離級別:
| 隔離級別 | 讀一致性 | 髒讀 | 不可重複讀 | 幻讀 |
|---|---|---|---|---|
| Read Uncommitted | 最低 | 是 | 是 | 是 |
| Read Committed | 語句級 | 否 | 是 | 是 |
| Repeatable Read | 事務級 | 否 | 否 | 是 |
| Serializable | 最高 | 否 | 否 | 否 |
MySQL 支援全部四級,但實現細節有特殊之處(如某些級別下採用 MVCC 一致性讀)。
mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0 |
| InnoDB_row_lock_time | 0 |
| InnoDB_row_lock_time_avg | 0 |
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |
+-------------------------------+-------+
若 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 較高,說明鎖爭用嚴重。可建立 innodb_monitor 表並用 SHOW INNODB STATUS 進一步分析。
SELECT ... LOCK IN SHARE MODESELECT ... FOR UPDATE為支援多粒度鎖,InnoDB 還有兩種意向鎖(表鎖):意向共享鎖(IS)、意向排他鎖(IX)。意向鎖由 InnoDB 自動管理。
兼容性矩陣:
| 請求\當前 | X | IX | S | IS |
|---|---|---|---|---|
| X | 衝突 | 衝突 | 衝突 | 衝突 |
| IX | 衝突 | 兼容 | 衝突 | 兼容 |
| S | 衝突 | 衝突 | 兼容 | 兼容 |
| IS | 衝突 | 兼容 | 兼容 | 兼容 |
最佳實踐:需更新資料時,直接用 SELECT ... FOR UPDATE 獲取排他鎖,避免先獲取共享鎖再升級導致死鎖。
InnoDB 行鎖是透過給索引項加鎖實現的(Oracle 則鎖定資料塊)。意味著:
實戰案例:無索引時 FOR UPDATE 鎖全表;加索引後僅鎖定符合條件的行。
使用範圍條件而非相等條件檢索並請求鎖時,InnoDB 不僅鎖定符合條件的現有記錄索引項,也會鎖定鍵值在條件範圍內但不存在的記錄(即「間隙 GAP」),這就是間隙鎖。目的:
影響:範圍條件鎖定會阻塞符合範圍內鍵值的併發插入,造成嚴重鎖等待。實際開發中應盡量用相等條件訪問更新資料,避免範圍條件。
特別注意:相等條件請求給不存在的記錄加鎖,也會使用間隙鎖。
MySQL 的 Binlog 是 SQL 語句級、按事務提交順序記錄。為保證恢復/復制正確性,在事務未提交前,其他並發事務不能插入滿足其鎖定條件的任何記錄(即不允許幻讀),這超過了「可重複讀」級別,實際要求事務串行化。因此 InnoDB 在範圍條件更新時,無論 Read Committed 或 Repeatable Read,都要使用間隙鎖。
對於 INSERT ... SELECT 和 CREATE TABLE ... SELECT,MySQL 會給源表加共享鎖(甚至間隙鎖),而非使用 MVCC 一致性讀,以防 Binlog 記錄順序導致恢復/復制錯誤。若業務允許犧牲復制準確性換取併發,可設 innodb_locks_unsafe_for_binlog=on 或改用 SELECT ... INTO OUTFILE + LOAD DATA INFILE 繞過鎖。
隔離級別越高,InnoDB 加鎖越嚴格(尤其範圍條件),鎖衝突概率越大,併發效能影響越大。建議應用盡量使用較低隔離級別(如 Read Committed),透過優化事務邏輯滿足大部分需求;確需高隔離時,動態調整 Session 隔離級別。
絕大多數情況應用行級鎖,但特殊事務可考慮表鎖:
使用表鎖注意事項:
autocommit=0、innodb_table_locks=1(預設),InnoDB 才能自動識別涉及表鎖的死鎖LOCK TABLES 前設 AUTOCOMMIT=0;事務結束前勿用 UNLOCK TABLES(會隱含提交);COMMIT/ROLLBACK 不釋放表鎖,必須用 UNLOCK TABLESMyISAM 無死鎖(一次性獲取所有鎖);InnoDB 鎖逐步獲取,死鎖難免。InnoDB 通常能自動檢測並回滾一個事務,但涉及外部鎖或表鎖時可能無法檢測,需靠 innodb_lock_wait_timeout 解決。
良好習慣:程式碼中捕獲並處理死鎖異常;用 SHOW INNODB STATUS 分析最後死鎖原因。
concurrent_insert掌握 MySQL 鎖機制精髓,結合業務場景靈活調優,方能構建高效穩定的資料庫系統。