防止 INSERT/DELETE 造成資料庫死鎖：三種方案（移除 FK、預先轉向、CASCADE 刪除）

主表 A、子表 B（含 A 的 PK 做 FK）。插入先鎖 A 再鎖 B，刪除先鎖 B 再鎖 A → 交叉等待 → 死鎖。本文分析三種解決方案優缺點：移除 FK、預先轉向 FK、CASCADE 刪除。

死鎖形成過程

-- 插入交易 (Tx1)
LOCK A (X) → 需 LOCK B (X) → 等待

-- 刪除交易 (Tx2)
LOCK B (X) → 需 LOCK A (X) → 等待

-- 結果：Tx1 持有 A 等 B，Tx2 持有 B 等 A → 死鎖

方案一：移除外鍵關係

• 做法：ALTER TABLE B DROP FOREIGN KEY fk_b_a
• 刪除順序改為：先刪 A，再刜 B
• ✅ 訪問順序一致，避免死鎖
• ❌ **失去參考完整性保護**，應用層須自行維護
• ❌ 既有髒資料無法自動清理

方案二：預先轉向 FK 指向「虛擬保留行」

• 在 A 表建立特殊行：id=0, name='DELETED'，永不刪除
• 刪除前：UPDATE B SET a_id=0 WHERE a_id=要刪除的id
• 再執行刪除：先刪 A，再刪 B
• ✅ 保留 FK 約束，訪問順序一致
• ❌ 增加額外 UPDATE 操作與「虛擬行」邏輯複雜度
• ❌ 統計查詢需排除 id=0

方案三：FK 設定 ON DELETE CASCADE（推薦）

• 建表時：FOREIGN KEY (a_id) REFERENCES A(id) ON DELETE CASCADE
• 刪除時：DELETE FROM A WHERE id=... 即可，B 表關聯行自動刪除
• ✅ 單一刪除語句，**無需鎖 B 表**，自然避免死鎖
• ✅ 保持參考完整性，邏輯最簡潔
• ⚠️ 級聯刪除會遞歸觸發，大量資料需評估效能
• ⚠️ 若 B 表又被其他表參考，需全鏈路 CASCADE

方案比較總結

其他輔助手段

• 統一所有交易的**鎖表順序**（永遠先鎖 A 再鎖 B）
• 縮短交易持續時間，減少持鎖時間
• 設定 innodb_lock_wait_timeout 避免無限等待
• 啟用 innodb_deadlock_detect=ON（預設開啟）自動偵測犧牲者