涉及死鎖的 authorized_user 表的 DDL
CREATE TABLE `authorized_user` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT
COMMENT '自增id',
`username` VARCHAR(50) NOT NULL
COMMENT '用戶名',
`password` VARCHAR(200) NOT NULL
COMMENT '加密后的密碼',
`description` VARCHAR(500) NOT NULL DEFAULT ''
COMMENT '描述',
`status` TINYINT(4) NOT NULL DEFAULT 0
COMMENT '狀態(tài)，0:已注冊(cè)令野，1：已激活焰枢，3：已鎖定丢郊，4：已注銷',
`phone_no` VARCHAR(15) NOT NULL DEFAULT ''
COMMENT '手機(jī)號(hào)',
`email` VARCHAR(75) NOT NULL DEFAULT ''
COMMENT '電子郵件',
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
COMMENT '創(chuàng)建時(shí)間',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
COMMENT '最近修改時(shí)間',
PRIMARY KEY (`id`),
KEY `index_username` (`username`)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8
COMMENT = '用戶表';
死鎖日志
2016-11-27 15:57:13 7fe166a92700InnoDB: transactions deadlock detected, dumping detailed information.
2016-11-27 15:57:13 7fe166a92700
*** (1) TRANSACTION:
TRANSACTION 80660631141, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 2936, 8 row lock(s)
MySQL thread id 126366554, OS thread handle 0x7fe18143e700, query id 2011631849 172.18.3.148 acc Searching rows for update
UPDATE authorized_user SET status = 1 WHERE username = 'wcy'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 669 page no 937 n bits 136 index `PRIMARY` of table `test`.`authorized_user` trx table locks 1 total table locks 2 trx id 80660631141 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
*** (2) TRANSACTION:
TRANSACTION 80660631143, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 126366547, OS thread handle 0x7fe166a92700, query id 2011631851 172.18.3.154 acc updating
UPDATE authorized_user SET username = 'wcy100' WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 669 page no 937 n bits 136 index `PRIMARY` of table `test`.`authorized_user` trx table locks 1 total table locks 2 trx id 80660631143 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 669 page no 13359 n bits 1176 index `index_username` of table `test`.`authorized_user` trx table locks 1 total table locks 2 trx id 80660631143 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
*** WE ROLL BACK TRANSACTION (2)
根據(jù) MySQL 日志分析出來的涉及死鎖的 SQL 語句分析
mysql> explain UPDATE authorized_user SET status = 1 WHERE username = 'wcy' \G
*************************** 1. row ***************************
id: 1
select_type: UPDATE
table: authorized_user
partitions: NULL
type: range
possible_keys: index_username
key: index_username
key_len: 152
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 row in set (0.00 sec)
mysql> explain UPDATE authorized_user SET username = 'wcy100' WHERE id = 1 \G
*************************** 1. row ***************************
id: 1
select_type: UPDATE
table: authorized_user
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 row in set (0.00 sec)
原因分析
InnoDB 引擎，更新操作默認(rèn)會(huì)加行級(jí)鎖医咨，行級(jí)鎖會(huì)對(duì)索引加鎖枫匾。如果更新語句使用多個(gè)索引，行鎖會(huì)先鎖非聚簇索引拟淮，再鎖聚簇索引干茉。如果兩個(gè)事務(wù)中的 SQL 用到了不同的非聚簇索引或者一個(gè)用了一個(gè)沒有使用（即使用索引的情況不同），這樣的話就會(huì)導(dǎo)致這兩個(gè)事務(wù)加行鎖的順序不一致很泊，形成了多個(gè)事務(wù)之間資源（行鎖）的循環(huán)等待角虫，構(gòu)成了死鎖的四個(gè)必要條件之一，而其他3個(gè)條件（互斥委造、請(qǐng)求與保持戳鹅、不剝奪）已經(jīng)滿足，所以最終導(dǎo)致了死鎖昏兆。
具體分析（以死鎖日志1為例）
由日志分析可得：
事務(wù)1用到的 SQL 語句：
UPDATE authorized_user SET status = 1 WHERE username = 'wcy'
事務(wù)1獲取的鎖：X lock on (669,13359,1176)index_username
事務(wù)1等待的鎖：X lock on (669,937,136)PRIMARY
事務(wù)2用到的 SQL 語句：
UPDATE authorized_user SET username = 'wcy100' WHERE id = 1
事務(wù)2獲取的鎖：X lock on (669,937,136)PRIMARY
事務(wù)2等待的鎖：X lock on (669,13359,1176)index_username
導(dǎo)致這種情況的 SQL 執(zhí)行流程說明：
事務(wù)1的 update 語句正常的執(zhí)行步驟如下：
1.由于用到了非聚簇索引枫虏，首先需要獲取 index_username 上的行級(jí)鎖。
2.緊接著根據(jù)主鍵進(jìn)行更新爬虱，所以需要獲取 PRIMAEY 上的行級(jí)鎖隶债。
3.更新完畢后，提交跑筝，并釋放所有鎖死讹。
但是，如果在步驟1和2之間突然插入事務(wù)2的這條 SQL 語句：
UPDATE authorized_user SET username = 'wcy100' WHERE id = 1, 這條語句會(huì)先鎖住聚簇索引曲梗，然后由于其更新的字段 username 有非聚簇索引赞警，所以這條語句需要鎖住 index_username。
這時(shí)候的情況變成了：事務(wù)1獲取了 index_username 上的鎖虏两，等待 PRIMARY 上的鎖愧旦；事務(wù)2獲取了 PRIMARY 上的鎖，等待 index_username 上的鎖碘举，這樣就出現(xiàn)了死鎖忘瓦。
解決方案
可以將原先的單條批量 update 語句分拆成如下步驟（此處以事務(wù)1的 SQL 語句為例）：
1.先獲取需要更新的記錄的主鍵
select id from authorized_user WHERE username = 'wcy'
2.開啟一個(gè)事務(wù)，逐條更新
for (Integer id : ids) {
updateStatusById(id,1);
}
這樣每一次更新操作都是針對(duì)單條記錄先獲取 PRIMARY 上的鎖，再獲取 index_username 上的鎖耕皮，避免了由于在獲取行鎖的時(shí)候境蜕，鎖索引的順序不一致造成的死鎖。
