1. 介紹
MySQL 被廣泛用作資料庫管理系統，但當多個查詢同時存取同一資料時，會觸發鎖定機制。鎖定對維護資料一致性至關重要，若管理不當則可能導致死結與效能下降。
本文說明 MySQL 鎖定的基本概念，並詳細說明 如何檢查鎖定狀態、釋放鎖定以及預防死結。
你將學到
- MySQL 鎖定類型及其影響
- 版本專屬的檢查鎖定方法
- 安全的釋放鎖定程序
- 實用的預防死結策略
讓我們從 MySQL 鎖定的基本概念 開始。
2. MySQL 鎖定的基本概念
在資料庫中，「鎖」是一種限制存取的機制，當多個交易同時嘗試修改資料時用以維持資料完整性。不當的鎖管理可能導致 效能問題或死結。
2.1 鎖定的主要類型
MySQL 提供 多種鎖類型，取決於所需的資料保護等級。
行鎖
- 僅鎖定特定行，將對其他交易的影響降至最低。
- 只被 InnoDB 引擎支援。
- 在使用
SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE時發生。
表鎖
- 鎖定整個表，阻止多個查詢同時執行。
- 常見於 MyISAM 引擎。
- 由
LOCK TABLES語句觸發。
意圖鎖
- 協調行鎖與表鎖以避免衝突。
- 只在 InnoDB 中使用，並由系統自動管理。
死結
- 發生在多筆交易彼此等待對方的鎖定，導致無限等待。
- 不當的交易設計可能導致進程停止。
2.2 鎖定發生示例
以下示例說明鎖定如何在實際 SQL 查詢中發生。
行鎖示例
執行下列 SQL 將鎖定特定行。
BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 100;
-- Other sessions cannot update this row until COMMIT or ROLLBACK is executed
若另一個會話嘗試更新同一行，將進入等待狀態（鎖競爭）。
表鎖示例
要鎖定整個表，請使用以下命令：
LOCK TABLES products WRITE;
-- Prevents other sessions from modifying the products table until the lock is released
直到此鎖釋放，其他用戶都無法修改 products 表中的資料。
死結示例
一個典型的死結情境如下：
-- Session 1
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
-- Waiting for Session 2...
-- Session 2
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
-- Waiting for Session 1...
-- Session 1 (executes next)
UPDATE customers SET last_order = NOW() WHERE customer_id = 10; -- Deadlock occurs here
在此情況下，每個交易都在等待對方釋放鎖定，導致死結。
3. 按版本檢查 MySQL 鎖定狀態
要判斷鎖是否啟用，請使用適合您 MySQL 版本的命令。
3.1 MySQL 5.6 及以前
在 MySQL 5.6 及以往版本中，使用 SHOW ENGINE INNODB STATUS; 來檢查鎖定細節。
SHOW ENGINE INNODB STATUSG;
此命令顯示關於目前鎖定的詳細資訊。
3.2 MySQL 5.7
從 MySQL 5.7 開始，更容易使用 sys.innodb_lock_waits 表。
SELECT * FROM sys.innodb_lock_waits;
此查詢顯示目前哪些交易正在等待鎖定。
3.3 MySQL 8.0 及之後
在 MySQL 8.0 及更新版本中，您可以使用 performance_schema.data_locks 取得更詳細資訊。
SELECT * FROM performance_schema.data_locks;
要識別是哪個會話持有鎖：
SELECT * FROM performance_schema.threads WHERE PROCESSLIST_ID = <process_id>;
這有助於定位負責鎖定的進程。
4. 如何在 MySQL 中釋放鎖定（附風險說明）
若 MySQL 中發生鎖定且未正確釋放，可能會阻塞進程並降低資料庫效能。
本節說明如何安全地釋放鎖定以及相關潛在風險。
4.1 識別持有鎖定的會話
在釋放鎖定前，先識別哪個會話持有它。使用下列 SQL 檢查等待鎖定的會話：
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE='Waiting for table metadata lock';
此查詢列出目前正在等待表元資料鎖定的會話。
在 MySQL 8.0 及之後的版本，您可以使用以下方式獲取詳細鎖定資料：
SELECT * FROM performance_schema.data_locks;
4.2 使用 KILL 命令釋放鎖定
識別出持有鎖定的會話後，您可以終止該進程以釋放鎖定。
1. 檢查持有鎖定的進程
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST;
2. 使用 KILL 命令結束會話
KILL <process_id>;
範例：終止 ID=12345 的進程，執行：
KILL 12345;
⚠️ 使用 KILL 的風險
- 被終止的交易將自動回滾
- 例如未完成的
UPDATE操作可能會放棄待處理的更改。 - 可能導致應用程式錯誤
- 若頻繁需要使用
KILL，請檢討您的應用程式交易設計。
4.3 使用 ROLLBACK 安全釋放鎖定
在使用 KILL 之前，盡量在可能的情況下手動 結束交易。
1. 檢查目前會話
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
2. 識別問題交易並執行 ROLLBACK
ROLLBACK;
此方法安全地釋放鎖定，同時保持資料一致性。
4.4 使用 SET innodb_lock_wait_timeout 自動釋放鎖定
不必手動釋放鎖定，您可以設定超時，讓鎖定在指定時間後自動過期。
SET innodb_lock_wait_timeout = 10;
此設定使得如果鎖定未在 10 秒內釋放，交易將自動終止，避免長時間停滯。
5. MySQL 鎖定的關鍵點與最佳實踐
適當的鎖定管理可以降低 死鎖和效能下降 的風險。以下是高效鎖定處理的最佳實踐。
5.1 防止死鎖
為了防止死鎖，請遵循以下原則：
1. 保持一致的交易順序
- 始終以 相同的順序 更新多張表。
- 範例：
-- OK: Always update orders → customers
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;
× 錯誤：不一致的順序會導致死鎖
-- Session 1
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
COMMIT;
-- Session 2 (executed in reverse order → possible deadlock)
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;
2. 保持交易短暫
- 始終及時 COMMIT 或 ROLLBACK
- 長時間交易會阻塞其他會話並增加死鎖風險。
3. 使用適當的索引
- 索引可降低被鎖定行的範圍，減少不必要的鎖定。
- 範例：在
orders表中為customer_id加上索引，可確保僅鎖定相關行。
CREATE INDEX idx_customer_id ON orders (customer_id);
6. 總結
- MySQL 鎖定包含 行、表以及意圖鎖。管理不當會導致死鎖與性能下降。
- 鎖檢查方式在 MySQL 版本 之間有所不同。
- 釋放鎖時務必小心！
- 在使用
KILL前先嘗試ROLLBACK。 - 使用
SET innodb_lock_wait_timeout來自動釋放鎖。 - 透過 保持一致的交易順序 與 短交易時間 來預防死鎖。
7. 常見問題
Q1. 檢查 MySQL 鎖狀態最簡單的方法是什麼？
- A1. 在 MySQL 8.0+ 中，使用
SELECT * FROM performance_schema.data_locks;便可輕鬆查看鎖狀態。
Q2. 如何處理死鎖？
- A2. 執行
SHOW ENGINE INNODB STATUSG;以找出原因，然後調整交易順序以防止重現。
Q3. KILL 指令會破壞資料嗎？
- A3. 強制終止會觸發未完成交易的
ROLLBACK，可能影響一致性，請謹慎使用。
Q4. 如何預防死鎖？
- A4. 遵循以下規則：
- 保持一致的交易順序
- 使用短交易
- 設定適當的索引
Q5. 如何透過減少鎖來提升 MySQL 性能？
- A5.
- 設計高效索引 以減少鎖定
- 使用短交易 以降低鎖定時間
- 避免全表鎖定 (LOCK TABLES)
- 利用讀取複本 以分散讀取負載
