深入分析MySQL更新死鎖問題

死鎖對於mysql來講是非常的不好的，如果我們的mysql出現死鎖問題那麼肯定會導致網站卡了，今天我們來看一篇關於MySQL更新死鎖問題，希望文章能夠讓各位理解到

作為一個社交類的 App ，我們有很多操作都會同時發生，為了確保數據的一致性，會采用數據庫的事物。

比如現在我們有一個點贊操作，點贊成功後，需要更改文章的熱度。以下是 SQL 語句:

INSERT INTO user_praise(uid,plan_id,stage_id) VALUES(123456,14456,10023);

UPDATE plan_hot SET hot = hot + 1 WHERE plan_id = 14456;

在這裡我們需要用到事物來確保它的原子性，也就是要麼這兩條語句全部執行成功，要麼就全部回滾。

問題出現

在本地這麼測試後，發現並沒有出現什麼問題，但是部署上線後，打印出了錯誤日志，如下

### Error updating database. 
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error occurred while setting parameters
SQL: UPDATE plan_hot SET hot = hot + 1 WHERE plan_id = ?;

錯誤中提示下面的更新語句出現了死鎖

UPDATE plan_hot SET hot = hot + 1 WHERE plan_id = ?;

該問題是第一次遇到，大概也知道是哪裡出現了死鎖，就是更新 hot = hot + 1 的時候，hot 可能會被一個鎖鎖住，而下一個操作就需要等待釋放鎖，然後才能獲取到 hot 的值，進行 hot = hot + 1。如果操作過快的話，可能就會造成死鎖。

但是還是不懂為什麼會這樣，根本原因是什麼，於是查資料，發現有一個小伙伴解釋得蠻清晰的，下面是他的博文。

分析解決

問題分析及解決方案參考

就是如何利用樂觀鎖來解決並發問題，但是項目推到線上後就報錯了，如下

-08-13 15:12:44 [ERROR] com.zhubajie.coupon.app.CouponReceiveAppServiceImpl {CouponReceiveAppServiceImpl.java:50} -
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE cpn_core_coupon     SET coup_num_usr = coup_num_usr + 1     WHERE coup_usr = ? AND spec_id = ? AND coup_num_usr     <     ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

錯誤中提示下面這條sql發生了死鎖

UPDATE coupon SET coup_num_usr = coup_num_usr + 1 WHERE coup_usr = ? AND spec_id = ? AND coup_num_usr < ?
首先我們來看下coupon的表結構

CREATE TABLE `coupon` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ' ',
  `spec_id` char(20) NOT NULL COMMENT '優惠券活動編號',
  `coup_usr` char(11) DEFAULT NULL COMMENT '優惠券用戶',
  `coup_num_usr` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '該用戶已領取該活動的券數量',
  PRIMARY KEY (`id`),
  KEY `coup_usr_idx` (`coup_usr`),
  KEY `spec_idx` (`spec_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8508 DEFAULT CHARSET=utf8 COMMENT='優惠券';

其中coup usr和spec id是索引，

mysql的事務支持與存儲引擎有關，MyISAM不支持事務，INNODB支持事務，更新時采用的是行級鎖。這裡采用的是INNODB做存儲引擎，意味著會將update語句做為一個事務來處理。前面提到行級鎖必須建立在索引的基礎，這條更新語句用到了索引，所以這裡肯定會加上行級鎖。 行級鎖並不是直接鎖記錄，而是鎖索引，如果一條SQL語句用到了主鍵索引，mysql會鎖住主鍵索引；如果一條語句操作了非主鍵索引，mysql會先鎖住非主鍵索引，再鎖定主鍵索引。 這個update語句會執行以下步驟： 1、由於用到了非主鍵索引，首先需要獲取普通索引上的行級鎖 2、緊接著根據主鍵進行更新，所以需要獲取主鍵上的行級鎖； 3、更新完畢後，提交，並釋放所有鎖。

如果在步驟1和2之間突然插入一條語句：UPDATE coupon SET coup num usr = coup num usr + 1 WHERE coup usr = ? AND spec id = ? AND coup num usr < ?

就會發生死鎖的情況，因為一條語句獲取了普通索引的鎖，等待主鍵鎖，另外一條語句獲取了主鍵鎖，等待非主鍵索引，這樣就出現了死鎖.

如何來解決update ... where ...語句的死鎖問題呢？我們可以對其進行分離，首先利用where條件找到主鍵，然後再利用這些主鍵去更新數據。

因為select * where ...語句是沒有鎖的，所以不存在會鎖上where條件裡面的字段，也就不會發生死鎖的情況，只有在update的時候回鎖上主鍵。

所以改成下面兩條語句

SELECT id WHERE coup_usr = ? AND spec_id = ?

UPDATE coupon SET coup_num_usr = coup_num_usr + 1 WHERE id = ? AND coup_num_usr < ?

第一條語句找出所有需要更新行的主鍵id，然後再一條一條更新。

在采用INNODB的MySQL中，更新操作默認會加行級鎖，行級鎖是基於索引的，在分析死鎖之前需要查詢一下mysql的執行計劃，看看是否用到了索引，用到了哪個索引，對於沒有用索引的操作會采用表級鎖。如果操作用到了主鍵索引會先在主鍵索引上加鎖，然後在其他索引上加鎖，否則加鎖順序相反。在並發度高的應用中，批量更新一定要帶上記錄的主鍵，優先獲取主鍵上的鎖，這樣可以減少死鎖的發生。