鎖定不存在的InnoDB 行以防止並發插入

在資料庫管理系統中，確保資料完整性並防止並發存取衝突至關重要。在 InnoDB 中，當嘗試不間斷地搜尋和插入不存在的行時，就會出現挑戰。

傳統方法

最初，有些人可能會建議使用 LOCK IN現有行上的 SHARE MODE 和 FOR UPDATE 關鍵字。但是，當嘗試鎖定不存在的行時，此方法會出現不足。

SELECT ... FOR UPDATE 的限制

而SELECT ... FOR UPDATE 會阻止並發在現有記錄上插入，當嘗試鎖定不存在的行時會失敗。並發會話仍然可以使用 SELECT ... FOR UPDATE 鎖定相同不存在的行，從而導致插入操作期間出現競爭條件和潛在的死鎖或關鍵錯誤。

更好的解決方案

由於 MySQL 缺乏針對不存在行的本機鎖定機制，因此需要替代方法。兩個可行的選項包括：

1. 訊號量表：訊號量表提供了一種追蹤和控制對資料庫資源的並發存取（包括行插入）的方法。透過為每個潛在不存在的行建立一個信號量行，並發交易可以在嘗試插入新行之前取得信號量行上的鎖定。
2. 表級鎖定： 在場景中如果行級鎖定不可行，則可能需要鎖定整個表。這確保沒有並發事務可以修改表，防止競爭條件並確保可靠的資料插入。

結論

防止競爭條件並確保完整性在不存在的行上插入資料庫、利用訊號量表或鎖定整個表提供了有效的解決方法，儘管存在潛在的性能影響。