表結(jié)構(gòu)如下：
CREATE TABLE `user_item` (
`id` BIGINT(20) NOT NULL,
`user_id` BIGINT(20) NOT NULL,
`item_id` BIGINT(20) NOT NULL,
`status` TINYINT(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_1` (`user_id`,`item_id`,`status`)
) ENGINE=INNODB DEFAULT CHARSET=utf-8
SQL 語句如下：
update user_item set status=1 where user_id=? and item_id=?
原因分析
MySQL 的事務(wù)支持與存儲(chǔ)引擎有關(guān)，MyISAM 不支持事務(wù)，INNODB 支持事務(wù)，更新時(shí)采用的是行級(jí)鎖。這里采用的是 INNODB 做存儲(chǔ)引擎，意味著會(huì)將 update 語句做為一個(gè)事務(wù)來處理。前面提到行級(jí)鎖必須建立在索引的基礎(chǔ)，這條更新語句用到了索引 idx_1，所以這里肯定會(huì)加上行級(jí)鎖
行級(jí)鎖并不是直接鎖記錄，而是鎖索引，如果一條 SQL 語句用到了主鍵索引，mysql 會(huì)鎖住主鍵索引；如果一條語句操作了非主鍵索引，mysql 會(huì)先鎖住非主鍵索引，再鎖定主鍵索引
這個(gè) update 語句會(huì)執(zhí)行以下步驟：
1、由于用到了非主鍵索引，首先需要獲取 idx_1 上的行級(jí)鎖
2、緊接著根據(jù)主鍵進(jìn)行更新，所以需要獲取主鍵上的行級(jí)鎖
3、更新完畢后，提交，并釋放所有鎖。
如果在步驟 1 和 2 之間突然插入一條語句：update user_item .....where id=? and user_id=?，這條語句會(huì)先鎖住主鍵索引，然后鎖住 idx_1
蛋疼的情況出現(xiàn)了，一條語句獲取了 idx_1 上的鎖，等待主鍵索引上的鎖；另一條語句獲取了主鍵索引上的鎖，等待 idx_1 上的鎖，這樣就出現(xiàn)了死鎖
解決方法
只要讓更新操作中帶有主鍵即可，也就是讓獲取鎖的順序一致即可：
- 先獲取需要更新的記錄的主鍵
select id from user_item where user_id=? and item_id=?
- 逐條更新
select id from user_item where user_id=? and item_id=?
for (Long id : idList) {
userItemDAO.updateStatus(id, userId, 1);
}
update user_item set status=? where id=? and user_id=?
- 這樣貌似解決了，都是對(duì)單條進(jìn)行操作，都是先獲取主鍵上的鎖，再獲取 idx_1 上的鎖
不過這個(gè)解決方案與先前的更新語句不一樣，先前的更新語句（update user_item set status=1 where user_id=? and item_id=?）對(duì)所有記錄的更新在一個(gè)事務(wù)中，采用循環(huán)更新后并不在同一個(gè)事務(wù)中，所以在 for 循環(huán)外面還得開一個(gè)事務(wù)。偽代碼：
public Object doInTransaction(Transaction t) {
try {
for (Long id : idList) {
userItemDAO.updateStatus(id, userId, 1);
}
return null;
} catch (DAOException e) {
t.rollback();
} catch (Exception e) {
r.rollback();
}
}
總結(jié)
在采用 INNODB 的 MySQL 中，更新操作默認(rèn)會(huì)加行級(jí)鎖，行級(jí)鎖是基于索引的，在分析死鎖之前需要查詢一下 mysql 的執(zhí)行計(jì)劃，看看是否用到了索引，用到了哪個(gè)索引，對(duì)于沒有用索引的操作會(huì)采用表級(jí)鎖。如果操作用到了主鍵索引會(huì)先在主鍵索引上加鎖，然后在其他索引上加鎖，否則加鎖順序相反。在并發(fā)度高的應(yīng)用中，批量更新一定要帶上記錄的主鍵，優(yōu)先獲取主鍵上的鎖，這樣可以減少死鎖的發(fā)生
