表結(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ù)支持與存儲引擎有關(guān)弹沽，MyISAM 不支持事務(wù)牛欢，INNODB 支持事務(wù)风题，更新時采用的是行級鎖像棘。這里采用的是 INNODB 做存儲引擎，意味著會將 update 語句做為一個事務(wù)來處理疙筹。前面提到行級鎖必須建立在索引的基礎(chǔ)桨昙，這條更新語句用到了索引 idx_1，所以這里肯定會加上行級鎖
行級鎖并不是直接鎖記錄腌歉，而是鎖索引，如果一條 SQL 語句用到了主鍵索引齐苛，mysql 會鎖住主鍵索引翘盖；如果一條語句操作了非主鍵索引，mysql 會先鎖住非主鍵索引凹蜂，再鎖定主鍵索引
這個 update 語句會執(zhí)行以下步驟：
1馍驯、由于用到了非主鍵索引阁危，首先需要獲取 idx_1 上的行級鎖
2、緊接著根據(jù)主鍵進(jìn)行更新汰瘫，所以需要獲取主鍵上的行級鎖
3狂打、更新完畢后，提交混弥，并釋放所有鎖趴乡。
如果在步驟 1 和 2 之間突然插入一條語句：update user_item .....where id=? and user_id=?，這條語句會先鎖住主鍵索引蝗拿，然后鎖住 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=?
- 這樣貌似解決了胖齐，都是對單條進(jìn)行操作，都是先獲取主鍵上的鎖嗽冒，再獲取 idx_1 上的鎖
不過這個解決方案與先前的更新語句不一樣呀伙，先前的更新語句（update user_item set status=1 where user_id=? and item_id=?）對所有記錄的更新在一個事務(wù)中，采用循環(huán)更新后并不在同一個事務(wù)中辛慰，所以在 for 循環(huán)外面還得開一個事務(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)會加行級鎖帅腌，行級鎖是基于索引的驰弄，在分析死鎖之前需要查詢一下 mysql 的執(zhí)行計劃，看看是否用到了索引速客，用到了哪個索引戚篙，對于沒有用索引的操作會采用表級鎖。如果操作用到了主鍵索引會先在主鍵索引上加鎖溺职，然后在其他索引上加鎖岔擂，否則加鎖順序相反。在并發(fā)度高的應(yīng)用中浪耘，批量更新一定要帶上記錄的主鍵乱灵，優(yōu)先獲取主鍵上的鎖，這樣可以減少死鎖的發(fā)生
