一個MySQL死鎖問題的反思

dbasdk發表於2017-12-06

原文網址 : http://blog.itpub.net/29734436/viewspace-2148332/

很早之前我寫過幾篇關於MySQL死鎖的分析，比如

但是感覺不過癮，而且分析的都是一些特定的場景，好像還缺少一些舉一反三的感覺，所以今天就補上這一波。

MySQL裡的鎖相容列表大體是這樣的關係，如果第一次看會有些暈，感覺抓不住重點，其實有一點小技巧。

首先InnoDB實現了兩種類似的行鎖，即S(共享鎖）和X(排他鎖），而InnoDB層面的表級意向鎖有IS(意向共享鎖）和IX9意向排他鎖），意向鎖之間是互相相容的，這句話很重要，按照這個思路里面一半的內容就明確了。而另外一部分則是S和X的相容性。帶著S鎖和X鎖的組合都是互相排斥，只有一類場景例外，那就是都是S鎖，是相容的。所以這個圖按照這個思路幾乎不用記就能基本理解了。

一個MySQL死鎖問題的反思

看起來S鎖的組合是很柔和的，從這種場景來看保持相容，那麼出死鎖的概率應該很低吧，其實在RR,RC隔離級別下我們可以逐步擴充套件然後舉一反三。

如果S鎖的組合在兩個會話中是互相相容，那麼接下來的X鎖的組合就是互相排斥的。

一個MySQL死鎖問題的反思

那麼在兩個會話併發的場景下，死鎖的步驟如下：

mysql> create table dt1 (id int unique

Query OK, 0 rows affected (0.03 sec)

會話1：

begin;

select *from dt1 lock in share mode; --顯式共享鎖

會話2：

begin;

select *from dt1 lock in share mode; --顯式共享鎖

會話1：

insert into dt1 values(1); --阻塞

會話2：

insert into dt1 values(2); --觸發死鎖

所以上面的語句特點很明顯，插入的資料分別是1和2，看起來互補衝突也不行。

我們進度稍快一些，我們可能很少看到直接宣告share mode的方式，但是有很多時候由其他的場景會觸發，其中的一個主要原因就在於對於duplicate資料的檢查會開啟S鎖。這是比較特別的一點，需要注意。

按照這一點來擴充套件，很容易就可以擴充套件到3個會話中。

會話1只是負責插入一條資料，會話2,3也緊接著插入一條記錄（會話2,3自動提交），但是因為唯一性索引的檢查，會導致會話2和會話3都開啟了S鎖，因為相容，所以暫時還沒影響。如果會話1正常提交，會話2,3的檢查會生效，導致資料插入不了，違反唯一性約束，但是我們反其道而行，就可以用一個rollback來釋放鎖，緊接著會話2和會話3都會獲得S鎖成功，緊接著獲得X鎖，細節演算法就不說了。這個時候互相阻塞，導致會話3產生死鎖，會話2的資料插入依然會成功。

會話1：

begin;

insert into dt1 values(1);

會話2：

insert into dt1 values(1);

會話3：

insert into dt1 values(1);

會話1：

rollback;

看起來很精巧的小測試，但是裡面蘊含這大道理，比如按照這個思路，如果後面的兩個語句都是delete，也會觸發死鎖。有的時候我們可以正面來圖例，或者通過死鎖日誌來推理。給我的一個啟發是太極。

一個MySQL死鎖問題的反思