萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 行鎖與表鎖深度解析:SELECT ... FOR UPDATE 實戰

前提條件

  • 儲存引擎:**InnoDB** (MyISAM 不支援行鎖)
  • 在**事務中**執行:BEGIN; ... COMMIT;
  • 隔離級別:READ COMMITTED / REPEATABLE READ (預設)

鎖類型判定規則

查詢條件是否命中主鍵/唯一索引鎖類型說明
WHERE id = 3 (主鍵,存在)✅ 唯一索引精確匹配**Record Lock (行鎖)**僅鎖定該行
WHERE id = -1 (主鍵,不存在)✅ 唯一索引精確匹配**無鎖**無記錄,無需鎖 (Gap Lock 視隔離級別)
WHERE name = 'Mouse' (非索引列)❌ 全表掃描**Table Lock (表鎖)**InnoDB 退化為表鎖
WHERE id <> 3 (主鍵範圍)❌ 範圍非精確**Table Lock / Next-Key Lock**掃描大量行,升級表鎖
WHERE id LIKE '3' (隱式轉換)❌ 函數導致索引失效**Table Lock**字串比較不走索引

實戰測試雙會話法

-- 會話 1
BEGIN;
SELECT * FROM products WHERE id = 3 FOR UPDATE;
-- 保持不提交

-- 會話 2 (新連接)
BEGIN;
SELECT * FROM products WHERE id = 3 FOR UPDATE;  -- 會阻塞
SELECT * FROM products WHERE id = 4 FOR UPDATE;  -- 秒過 (不同行)
SELECT * FROM products WHERE name = 'Mouse' FOR UPDATE;  -- 會阻塞 (表鎖)
COMMIT;  -- 會話 1 提交後,會話 2 依序獲得鎖

間隙鎖 與 Next-Key Lock (RR 隔離級別)

-- 表有 id = 10, 20, 30
BEGIN;
SELECT * FROM t WHERE id = 15 FOR UPDATE;  -- 不存在的值
-- 在 RR 下:鎖定 (10, 20) 間隙,防止幻讀
-- 在 RC 下:無間隙鎖,僅嘗試鎖定不存在的記錄 (無鎖)

避坑指南

  • FOR UPDATE 必須在事務中,自動提交模式下鎖即釋放,無意義
  • WHERE 條件必須走索引:EXPLAIN 確認 type=ref/eq_ref,否則升級表鎖
  • 避免長事務持鎖:查詢 → 業務邏輯 → 更新 → 提交,中間無 RPC/睡眠
  • 死鎖預防:多表操作**固定順序** (如按表名字母序)、單表按主鍵序更新

鎖監控

-- 當前鎖等待
SELECT * FROM sys.innodb_lock_waits;  -- 5.7
SELECT * FROM performance_schema.data_locks;  -- 8.0+

-- 事務資訊
SELECT * FROM information_schema.innodb_trx\G

**核心:行鎖靠索引,表鎖靠掃描;事務短、索引全、順序固定,並發無憂。**

copyright © 萬盛學電腦網 all rights reserved