涉及死锁的 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 '状态，0:已注册，1：已激活，3：已锁定，4：已注销',
`phone_no` VARCHAR(15) NOT NULL DEFAULT ''
COMMENT '手机号',
`email` VARCHAR(75) NOT NULL DEFAULT ''
COMMENT '电子邮件',
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
COMMENT '创建时间',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
COMMENT '最近修改时间',
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)
根据 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 引擎，更新操作默认会加行级锁，行级锁会对索引加锁。如果更新语句使用多个索引，行锁会先锁非聚簇索引，再锁聚簇索引。如果两个事务中的 SQL 用到了不同的非聚簇索引或者一个用了一个没有使用（即使用索引的情况不同），这样的话就会导致这两个事务加行锁的顺序不一致，形成了多个事务之间资源（行锁）的循环等待，构成了死锁的四个必要条件之一，而其他3个条件（互斥、请求与保持、不剥夺）已经满足，所以最终导致了死锁。
具体分析（以死锁日志1为例）
由日志分析可得：
事务1用到的 SQL 语句：
UPDATE authorized_user SET status = 1 WHERE username = 'wcy'
事务1获取的锁：X lock on (669,13359,1176)index_username
事务1等待的锁：X lock on (669,937,136)PRIMARY
事务2用到的 SQL 语句：
UPDATE authorized_user SET username = 'wcy100' WHERE id = 1
事务2获取的锁：X lock on (669,937,136)PRIMARY
事务2等待的锁：X lock on (669,13359,1176)index_username
导致这种情况的 SQL 执行流程说明：
事务1的 update 语句正常的执行步骤如下：
1.由于用到了非聚簇索引，首先需要获取 index_username 上的行级锁。
2.紧接着根据主键进行更新，所以需要获取 PRIMAEY 上的行级锁。
3.更新完毕后，提交，并释放所有锁。
但是，如果在步骤1和2之间突然插入事务2的这条 SQL 语句：
UPDATE authorized_user SET username = 'wcy100' WHERE id = 1, 这条语句会先锁住聚簇索引，然后由于其更新的字段 username 有非聚簇索引，所以这条语句需要锁住 index_username。
这时候的情况变成了：事务1获取了 index_username 上的锁，等待 PRIMARY 上的锁；事务2获取了 PRIMARY 上的锁，等待 index_username 上的锁，这样就出现了死锁。
解决方案
可以将原先的单条批量 update 语句分拆成如下步骤（此处以事务1的 SQL 语句为例）：
1.先获取需要更新的记录的主键
select id from authorized_user WHERE username = 'wcy'
2.开启一个事务，逐条更新
for (Integer id : ids) {
updateStatusById(id,1);
}
这样每一次更新操作都是针对单条记录先获取 PRIMARY 上的锁，再获取 index_username 上的锁，避免了由于在获取行锁的时候，锁索引的顺序不一致造成的死锁。
