mysql insert into ...select 語句為什麼會造成死鎖？

01-12

這是一個避免tableA 表某欄位重複的語句，
insert into tableA(column1, column2, column3) select column1, column2, column3 from dual where not exists (select 1 from tableA where column2=XX )，在高並發情況下造成大量的死鎖，請問為什麼會這樣？假設column1 是主鍵id,column2 是為了不應該重複的欄位。
補充：不能在column2上添加唯一約束，因線上庫表已有大量重複值

之前我在實際開發中遇到過mysql死鎖問題，然後順便研究了下mysql的鎖機制以及產生死鎖的場景。分析下這個問題，如有不正確的地方，請指出！

確實，當數據表中不存在一條記錄時，並發insert兩條統一條記錄（包含的唯一鍵也相同）是可能會出現死鎖的。

假設有3個session都去插入同一條記錄（假設t1是唯一鍵）：

Session 1:


START TRANSACTION;

INSERT INTO t1 VALUES(1);
Session 2:
START TRANSACTION;

INSERT INTO t1 VALUES(1);
Session 3:

START TRANSACTION; INSERT INTO t1 VALUES(1);

我們知道,insert是會對對應的行加排它鎖的。假設session 1獲取到了排它鎖，那麼session 2和session 3均會報主鍵重複的錯誤。而發生主鍵重複的錯誤時，這行是會被加共享鎖的（參考mysql user manual：If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock）。這個時候Session 2和session 3均會嘗試獲得這一行的共享鎖。這個時候如果Session 1 回滾，那麼它會釋放之前加的排它鎖。這個時候Session 2和Session 3會完成獲得共享鎖。

這個時候Session 2和Session 3就發生死鎖了，因為各自持有共享鎖，而均欲獲得排它鎖。都不能達到各自的目的，死鎖了。

我實際測試過，確實是這樣。不過如果Session 1 commit的話不會造成死鎖，這個地方有點不明白，不知道哪位大俠能否給解釋下？

其實MySQL的鎖機制以及發生死鎖的場景是很複雜的一塊，很多時候看上去很簡單的SQL都會發生死鎖，初次遇到估計會很詫異。

關於鎖機制我之前有寫過一個ppt，總結了鎖機制以及常見的幾種死鎖場景：

技術分享-MySQL InnoDB locks and deadlocks

然後給出一些不錯的參考資料：

（1）首先必然是官方文檔：

MySQL :: MySQL 5.5 Reference Manual :: 14.8.3 Locks Set by Different SQL Statements in InnoDB

（2）阿里DBA大牛-何登成的博客

何登成的技術博客

（3）《高性能MYSQL》

不太清楚死鎖原因，不過建議使用 replace into 代替你的sql，這種insert select 明顯效率非常低，如果不行要從設計層面想辦法繞開這種低效率的插入方式。

這裡說一個概念，快照讀與當前讀

快照讀就是一般執行的select，這個是沒有鎖結構的，不會產生鎖，所以也不會產生死鎖的情況

重點是當前讀，顧名思義就是讀取當前塊的狀態。典型的例子就是select for update，與dml語句。dml中的select子句也是當前讀。當前讀是有鎖結構的，行級排他。

從這個角度去理解的話，就比較輕鬆理解鎖的問題

上下文不清楚，只看語句可能僅僅是因為你插入的行衝突太大。後面的子查詢讀的時候行不存在，要插入的時候鎖衝突了。另外，這就是一個unique key的需求啊。