MySQL 鎖機制原理全解析：表鎖、行鎖、頁鎖、死鎖與優化策略

MySQL 鎖機制原理全解析：表鎖、行鎖、頁鎖、死鎖與優化策略

鎖是數據庫併發控制核心。MySQL 不同存儲引擎鎖粒度不同：MyISAM/MEMORY 表鎖，BDB 頁鎖，InnoDB 行鎖。本文系統梳理原理、衝突矩陣、死鎖排查與優化參數。

鎖類型對比

表鎖機制

• 讀鎖 (READ)：共享，兼容讀鎖，阻塞寫鎖。
• 寫鎖 (WRITE)：排他，阻塞讀/寫鎖。
• 隊列：寫鎖隊列優先於讀鎖隊列（寫優先策略）。
• 顯式鎖表：LOCK TABLES t1 READ, t2 WRITE; / UNLOCK TABLES;。

行鎖機制

• InnoDB 基於索引加鎖：**無索引條件會退化為表鎖**（鎖全表聚簇索引）。
• 三類行鎖：
  Record Lock：單行記錄鎖。
  Gap Lock：間隙鎖，防止幻讀（RR 隔離級別）。
  Next-Key Lock：Record + Gap，鎖定區間。
• 意向鎖（表級）：IS、IX，標識事務意圖，便於表鎖兼容性檢查。

死鎖產生與排查

• 條件：互斥、請求與保持、不剝佔、循環等待。
• 典型：事務 A 鎖行 1 再鎖行 2，事務 B 鎖行 2 再鎖行 1。
• 排查：SHOW ENGINE INNODB STATUS\G 看 LATEST DETECTED DEADLOCK 區塊。
• InnoDB 自動檢測並回滾代價較小的事務（權重：已修改行數、鎖數）。

優化策略

1. 固定加鎖順序：應用層按主鍵排序更新多行。
2. 縮短事務：少查詢、少計算、提前提交。
3. 合理索引：讓行鎖精準定位，避免全表掃描升級表鎖。
4. 低隔離級別：READ COMMITTED 減少 Gap Lock（需評估業務可接受性）。
5. 參數調優：
   innodb_lock_wait_timeout=50（默認 50s）
   innodb_deadlock_detect=ON（高併發可關閉，靠 timeout）
   innodb_spin_wait_delay、innodb_sync_spin_loops（自旋優化）
6. 應用層鎖：GET_LOCK('key', 10) / RELEASE_LOCK('key') 實現分布式邏輯鎖。

監控指標

• Innodb_row_lock_waits、Innodb_row_lock_time_avg、Innodb_row_lock_time_max。
• Table_locks_waited、Table_locks_immediate（MyISAM）。