Mysql死鎖排除實例分析

　　問題初現

# 　　在某天下午，突然系統報警，拋出個異常：

　　仔細一看好像是事務回滾異常，寫著的是因為死鎖回滾，原來是個死鎖問題，由於我對Mysql鎖還是有一定 了解的，於是開始主動排查這個問題。

　　先在資料庫中尋找Innodb Status，在Innodb Status中會記錄上一次死鎖的訊息，輸入下面指令：

　　SHOW ENGINE INNODB STATUS

# 　　死鎖資訊如下，sql資訊進行了簡單處理：

# 　　------------------------

# 　　LATEST DETECTED DEADLOCK

# 　　------------------------

# 　　2019-02-22 15:10:56 0x7eec2f468700

　　*** （1） TRANSACTION:

# 　　TRANSACTION 2660206487, ACTIVE 0 sec starting index read

　　mysql tables in use 1, locked 1

　　LOCK WAIT 2 lock struct（s）， heap size 1136, 1 row lock（s）

# 　　MySQL thread id 31261312, OS thread handle 139554322093824, query id 11624975750 10.23.134.92 erp_crm__6f73 10.23.134.92 erp_crm__6f73 upating# 　　/*id:3637ba36*/UPDATE tenant_config SET

　　open_card_point =  0

# 　　where tenant_id = 123

　　*** （1） WAITING FOR THIS LOCK TO BE GRANTED:

　　RECORD LOCKS space id 1322 page no 534 n bits 960 index uidx_tenant of table ——erp_crm_member_plan——。 ——tenant_config—— trx id 2660206487 lock_mode X locks rec but not gap waiting

　　*** （2） TRANSACTION:

# 　　TRANSACTION 2660206486, ACTIVE 0 sec starting index read

　　mysql tables in use 1, locked 1

　　3 lock struct（s）， heap size 1136, 2 row lock（s）

　　MySQL thread id 31261311, OS thread handle 139552870532864, query id 11624975758 10.23.134.92 erp_crm__6f73 10.23.134.92 erp_crm__6f73 upating# 　　/*id:3637ba36*/UPDATE tenant_config SET

　　open_card_point =  0

# 　　where tenant_id = 123

　　*** （2） HOLDS THE LOCK（S）：

　　RECORD LOCKS space id 1322 page no 534 n bits 960 index uidx_tenant of table ——erp_crm_member_plan——。 ——tenant_config—— trx id 2660206486 lock mode S

　　*** （2） WAITING FOR THIS LOCK TO BE GRANTED:

　　RECORD LOCKS space id 1322 page no 534 n bits 960 index uidx_tenant of table ——erp_crm_member_plan——。 ——tenant_config—— trx id 2660206486 lock_mode X locks rec but not gap waiting

　　*** WE ROLL BACK TRANSACTION （1）

　　------------

# 給大家簡單的分析解釋一下這段死鎖日誌，事務1執行Update語句的時候需要取得uidx_tenant這個索引再where條件上的X鎖（行鎖），事務2執行同樣的Update語句，也在uidx_tenant上面想要取得X鎖（行鎖），然後就出現了死鎖，回滾了事務1。當時我很懵逼，回想了一下死鎖產生的必要條件：

# 　　互斥。

　　請求與保持條件。

　　不剝奪條件。

　　循環等待。從日誌來看事務1和事務2都是取爭奪同一行的行鎖，和以往的互相循環爭奪鎖有點不同，怎麼看都無法滿足循環等待條件。經過同事提醒，既然從死鎖日誌中無法進行排查，那麼就只能從業務代碼和業務日誌從排查。這段程式碼的邏輯如下：

　　public int saveTenantConfig（PoiContext poiContext, TenantConfigDO tenantConfig） {

# 　　try {

# 　　return tenantConfigMapper.saveTenantConfig（poiContext.getTenantId（）， poiContext.getPoiId（）， tenantConfig）;

# 　　} catch （DuplicateKeyException e） {

　　LOGGER.warn（"[saveTenantConfig] 主鍵衝突，更新該記錄。context:{}, config:{}", poiContext, tenantConfig）;

　　return tenantConfigMapper.updateTenantConfig（poiContext.getTenantId（）， tenantConfig）;

　　}

# 　　}

# 　　這段程式碼的意思是保存一個設定文件，如果發生了唯一索引衝突那麼就會進行更新，當然這裡可能寫得不是很規範，其實可以用

　　insert into …

　　on duplicate key update

# 　　也可以達到同樣的效果，但是就算用這個其實也會發生死鎖。看了程式碼之後同事又給我發了當時業務日誌，

　　可以看見這裡有三條同時發生的日誌，說明都發生了唯一索引衝突進入了更新的語句，然後發生的死鎖。到這裡答案終於稍微有點眉了。

　　這個時候再看我們的表格結構如下（做了簡化處理）：

　　CREATE TABLE ——tenant_config—— （

　　——id—— bigint（21） NOT NULL AUTO_INCREMENT,

# 　　——tenant_id—— int（11） NOT NULL,

　　——open_card_point—— int（11） DEFAULT NULL,

　　PRIMARY KEY （——id——），

# 　　UNIQUE KEY ——uidx_tenant—— （——tenant_id——）

# 　　） ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT

　　我們的tenant_id是用來做唯一索引，我們的插入和更新的where條件都是基於唯一索引來操作的。

　　UPDATE tenant_config SET

# 　　open_card_point =  0

# 　　where tenant_id = 123

　　到了這裡感覺插入的時候對唯一索引加鎖有關係，接下來我們進行下一步的深入剖析。

　　深入剖析

# 　　上面我們說有三個事務進入update語句，為了簡化說明這裡我們只需要兩個事務同時進入update語句即可，下面的表格展示了我們整個的發生過程：

　　小提示：S鎖是共享鎖，X鎖是互斥鎖。一般來說X鎖和S，X鎖都互斥，S鎖和S鎖不互斥。

　　我們從上面的流程中看見發生這個死鎖的關鍵需要取得S鎖，為什麼我們再插入的時候需要取得S鎖呢？因為我們需要檢測唯一索引？在RR隔離等級下如果要讀取那麼就是當前讀，那麼其實就需要加上S鎖。這裡發現唯一鍵已經存在，這個時候執行update就會被兩個事務的S鎖互相阻塞，從而形成上面的循環等待條件。

小提示： 在MVCC中，當前讀和快照讀的區別：當前讀每次需要加鎖（可以使共享鎖或互斥鎖）獲取到最新的數據，而快照讀是讀取的是這個事務開始的時候那個快照，這個是透過undo log去進行實現的。

這個就是整個死鎖的原因，能出現這種死鎖的還有一個情況，就是同一時間來三個插入操作，其中先插入的那個事務如果最後回滾了，其餘兩個事務也會出現這種死鎖。

　　解法

# 　　這裡的核心問題是需要把S鎖給幹掉，這裡有三個可供參考的解決方案：

　　將RR隔離級別，降低成RC隔離級別。這裡RC隔離等級會用快照讀，從而不會加S鎖定。

　　再插入的時候使用select * for update,加X鎖，從而不會加S鎖。

　　可以提前加上分散式鎖，可以利用Redis,或者ZK等等，分散式鎖可以參考我的這篇文章。聊聊分散式鎖定

　　第一種方法不太現實，畢竟隔離等級不能輕易的修改。第三種方法又比較麻煩。所以第二種方法是我們最後確定的。

以上是Mysql死鎖排除實例分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！