MySQL更新死結問題

標籤：use   pre   updating   app   ret   主鍵索引   執行計畫   nod   處理   

之前寫了一篇部落格 http://leihuang.org/2015/07/22/concurrence-lock/

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

-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 specid = ? 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的執行計畫，看看是否用到了索引，用到了哪個索引，對於沒有用索引的操作會採用表級鎖。如果操作用到了主鍵索引會先在主鍵索引上加鎖，然後在其他索引上加鎖，否則加鎖順序相反。在並發度高的應用中，批次更新一定要帶上記錄的主鍵，優先擷取主鍵上的鎖，這樣可以減少死結的發生。

MySQL更新死結問題