MySQL大无语事件：一次生产环境的死锁事故，看看我怎么排查

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '名称',
  `age` int(3) NOT NULL COMMENT '年龄',
  `school` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '学校',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `name_age`(`name`, age) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error occurred while setting parameters
### SQL: update student SET school = "清华" WHERE ( name = '小明' )
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; 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

    开启事务

    .... 业务普通查询

    for ( ... 循环条件 ) {

        .... Student对象构建

        .... 将构建好的对象 Student 进行insert入库

        .... 将构建好的对象 Student 发送mq进行异步处理

        .... 业务剩余条件

    }

   .... 业务剩余条件

   提交事务

    .... 接受到消息Student

    .... 计算需要变更的参数

    .... 将计算好的参数 赋值给Student `school`字段

    .... 将Student 在表中的数据进行更新(这里产生了死锁)

show engine innodb status;

------------------------
LATEST DETECTED DEADLOCK
------------------------
191028 13:33:14
*** (1) TRANSACTION:
TRANSACTION 2656E7, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 879805, OS thread handle 0x7f8d26308700, query id 3761780 XXXXXXXXXXXXX root Updating
update student SET school = "清华" WHERE ( name = '小明' )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1362 n bits 376 index `name_age` of table `数据库1`.`student` trx id 2656E7 lock_mode X waiting
XXXXXX

*** (2) TRANSACTION:
TRANSACTION 2656E2, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 3
MySQL thread id 879796, OS thread handle 0x7f8d261c3700, query id 3761781 XXXXXXXXXXXXX root update
insert into student (XXXXXXXXXXXXXXXXXXXXXXXX)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1362 n bits 376 index `name_age` of table `数据库1`.`student` trx id 2656E2 lock_mode X locks rec but not gap
XXXXXX

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1362 n bits 376 index `name_age` of table `数据库1`.`student` trx id 2656E2 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 292 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
XXXXXX

*** WE ROLL BACK TRANSACTION (1)
------------

/* Basic lock modes */
enum lock_mode {
    LOCK_IS = 0, /* intention shared */
    LOCK_IX,    /* intention exclusive */
    LOCK_S,     /* shared */
    LOCK_X,     /* exclusive */
    LOCK_AUTO_INC,  /* locks the auto-inc counter of a table in an exclusive mode*/
    ...
};

insert student(name, age, school) values("test1", 10, "value1");
insert student(name, age, school) values("test10", 10, "value2");