01. MySQL 事務(wù)死鎖現(xiàn)象及原因初步判斷

做IT的幾乎每天都接觸 MySql，但是 Mysql 事務(wù)死鎖卻并不常見(jiàn)，前段時(shí)間就讓我遇到了。異常日志如下

從日志看是發(fā)生了 Lock wait timeout exceeded 異常。

Lock wait timeout exceeded：后提交的事務(wù)等待前面處理的事務(wù)釋放鎖，但是在等待的時(shí)候超過(guò)了mysql的鎖等待時(shí)間，就會(huì)引發(fā)這個(gè)異常。

PreparedStatementCallback; SQL [
UPDATE sf_wx_keyword_rule
SET status = ?,
last_update_time = last_update_time
WHERE id = ?];
Lock wait timeout exceeded;
try restarting transaction;

發(fā)生異常的代碼主要邏輯如下

分析后其實(shí)是因?yàn)橐粋€(gè)處理流程里開(kāi)了兩個(gè)事務(wù)，并更新的同一條數(shù)據(jù)，導(dǎo)致的事務(wù)間死鎖。

外層方法通過(guò)@Transactional 開(kāi)啟了事務(wù)1（@t1），對(duì) sf_wx_keyword_rule 一條數(shù)據(jù)做更新，內(nèi)層方法通過(guò) REQUIRES_NEW 又開(kāi)啟了一個(gè)新事務(wù)2（@t2），并對(duì)sf_wx_keyword_rule 的同一條數(shù)據(jù)做更新。

begin @t1;
UPDATE table SET status = ? WHERE id = 1
begin @t2;
UPDATE table SET status = ? WHERE id = 1
commit @t2;
commit @t1;

結(jié)論：由于 @t1 和 @t2 更新的是同一條數(shù)據(jù)，所以 @t2 的執(zhí)行需要依賴(lài) @t1 的提交，而@t1 的提交又需要 @t2 執(zhí)行完。所以?xún)蓚€(gè)事務(wù)互相等待對(duì)方提交導(dǎo)致死鎖。

02. 復(fù)現(xiàn)及深層原因追蹤

2.1 復(fù)現(xiàn)

為了搞清楚事務(wù)死鎖，及死鎖期間 MySql 的數(shù)據(jù)狀態(tài)，新建 test1 表重復(fù)上述操作

過(guò)了大概 30s @t2 返回鎖超時(shí)，與異常日志一致。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2.2 原因追蹤

2.2.1 事務(wù)狀態(tài)

Mysql 事務(wù)操作會(huì)涉及到三張表

//當(dāng)前正在執(zhí)行的每個(gè)事務(wù)的信息
information_schema.innodb_trx
//當(dāng)前事務(wù)持有的鎖記錄
information_schema.innodb_locks
// 當(dāng)前被阻塞的事務(wù)鎖記錄
information_schema.innodb_lock_waits 

查詢(xún) innodb_trx 表

主要字段的含義

當(dāng)前有兩個(gè)未提交的事務(wù)，trx_id=21245712 狀態(tài)為 LOCK WAIT，這條事務(wù)產(chǎn)生了一個(gè) id為 21245712:565:3:2 (innodb_locks 表的id) 的鎖，也就是該事務(wù)的 LOCK因?yàn)楸蛔枞鴮?dǎo)致事務(wù)超時(shí)。

trx_id = 21245684 是執(zhí)行完 SQL 還未提交的事務(wù)。

2.2.2 MySql 鎖

• innodb_locks InnoDB 鎖記錄

主要字段含義

鎖在 MySql 事務(wù)里是非常主要的，上面的事務(wù)就是通過(guò) Primary (主鍵) 在 Record (行) 上加的X (寫(xiě)) 鎖，先加的 X 鎖會(huì)成功，后加的 X 鎖就會(huì)被阻塞。下面詳細(xì)了解一下幾個(gè)主要的鎖。

基本鎖

InnoDB 行級(jí)鎖，分為共享鎖（S）和獨(dú)占鎖（X）

• 共享鎖（Sharaed Locks: S鎖），或叫讀鎖
• mysql允許拿到S鎖的事務(wù)讀一行
• 加了S鎖記錄，允許其他事務(wù)再加S鎖，不允許其他事務(wù)再加X(jué)鎖
• 語(yǔ)法：select ... lock in share mode;
• 獨(dú)占鎖（Exclusive Locks:X鎖）或叫寫(xiě)鎖
• mysql允許拿到X鎖的事務(wù)更新或刪除一行
• 加了X鎖的記錄，不允許其他事務(wù)再加X(jué)鎖或S鎖
• 語(yǔ)法：select … for update;

所以出現(xiàn)上述事務(wù)死鎖超時(shí)的原因是 UPDATE 會(huì)在記錄上加 X 鎖，阻塞了另一個(gè)事務(wù)對(duì)同一數(shù)據(jù)加的 X 鎖。

延伸一下，有 X 鎖之后，我們還能正常的讀數(shù)據(jù)嗎？答案是可以的。

select * from test1;

普通的 SELECT 語(yǔ)句上沒(méi)有加鎖，只有 select ... lock in share mode; 才會(huì)加 S 鎖。

下面是 MySql 的其他鎖

意向鎖

InnoDB為了支持多粒度（表鎖和行鎖）的鎖并存，引入意向鎖。意向鎖是表級(jí)鎖，分為IS鎖和IX鎖。

• 意向共享鎖（IS）事務(wù)在請(qǐng)求S鎖前，需要先獲得對(duì)應(yīng)的IS鎖
• 意向排他鎖 （IX）事務(wù)在請(qǐng)求X鎖前，需要先獲得對(duì)應(yīng)的IX鎖

鎖兼容矩陣

自增鎖 auto-inc lock

AUTO-INC鎖是事務(wù)中的一種特殊的表級(jí)鎖，通過(guò)AUTO_INCREMENT的列來(lái)實(shí)現(xiàn)，這種鎖是作用于語(yǔ)句的而不是事務(wù)。

記錄鎖 record Lock

即行鎖。單條索引記錄上加鎖，record lock鎖住的永遠(yuǎn)是索引，而非記錄本身。

間隙鎖 gap lock

區(qū)間鎖, 僅僅鎖住一個(gè)索引區(qū)間(開(kāi)區(qū)間)。在索引記錄之間的間隙中加鎖，或者是在某一條索引記錄之前或者之后加鎖，并不包括該索引記錄本身。GAP鎖的目的是為了防止同一事務(wù)的兩次當(dāng)前讀，出現(xiàn)幻讀的情況。

臨鍵鎖 next key lock

行鎖和間隙鎖組合起來(lái)就叫Next-Key-Lock，左開(kāi)右閉區(qū)間。默認(rèn)情況下，innodb使用next-key locks來(lái)鎖定記錄。但當(dāng)查詢(xún)的索引含有唯一屬性的時(shí)候，Next-Key Lock 會(huì)進(jìn)行優(yōu)化，將其降級(jí)為Record Lock，即僅鎖住索引本身，不是范圍。

插入意向鎖 insert intention lock

Gap Lock中存在一種插入意向鎖（Insert Intention Lock），在insert操作時(shí)產(chǎn)生。在多事務(wù)同時(shí)寫(xiě)入不同數(shù)據(jù)至同一索引間隙的時(shí)候，并不需要等待其他事務(wù)完成，不會(huì)發(fā)生鎖等待。 假設(shè)有一個(gè)記錄索引包含鍵值4和7，不同的事務(wù)分別插入5和6，每個(gè)事務(wù)都會(huì)產(chǎn)生一個(gè)加在4-7之間的插入意向鎖，獲取在插入行上的排它鎖，但是不會(huì)被互相鎖住，因?yàn)閿?shù)據(jù)行并不沖突。

注：插入意向鎖并非意向鎖，而是一種特殊的間隙鎖。

如果插入前，該間隙已經(jīng)有g(shù)ap鎖，那么insert 會(huì)申請(qǐng)插入意向鎖。因?yàn)榱吮苊饣米x，當(dāng)其他事務(wù)持有該間隙的間隔鎖，插入意向鎖就會(huì)被阻塞（不用直接用gap鎖，是因?yàn)間ap鎖不互斥）。

• innodb_lock_waits 被阻塞的鎖記錄

這張表里有記錄就說(shuō)明有事務(wù)被阻塞里。

主要字段含義

03. 解決方案及總結(jié)

線上遇到死鎖怎么解決？最快的方式當(dāng)然是 kill 事務(wù)，重啟服務(wù)，根本原因還是需要看這三張表，以后再遇到數(shù)據(jù)庫(kù)死鎖、事務(wù)死鎖，查這三張表就差不多知道原因了。

我們?cè)撊绾伪苊馑梨i呢？常規(guī)的回答都是以固定的順序訪問(wèn)數(shù)據(jù)。但本案例是因?yàn)槭褂昧?nbsp;REQUIRES_NEW 導(dǎo)致。

使用 REQUIRES_NEW 的原因以下場(chǎng)景，內(nèi)層事務(wù)是一個(gè)批量更新，但是又不希望因?yàn)槟骋粭l失敗而影響其他的更新。

begin @t1
aMApper.update()
for pojo in pojos:
  begin @t2
  bMapper.update(pojo)
  rpc.update()
  commit
commit

所以一定要避免內(nèi)外雙層事務(wù)修改同一條數(shù)據(jù)的情況，對(duì)于 Spring 事務(wù)傳播機(jī)制也要熟知其作用。

要保證數(shù)據(jù)的最終一致性，應(yīng)該寫(xiě)成一個(gè)Job，更新失敗后不斷的去補(bǔ)償。

公眾號(hào)：看起來(lái)很美（kanqilaihenmei_）