MYSQL 死锁探究（一）

主键自增，唯一索引

表结构：
CREATE TABLE account (
account_id bigint NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
user_id bigint NOT NULL DEFAULT ‘0’ COMMENT ‘用户Id’,
balance varchar(255) NOT NULL DEFAULT ‘0.00’ COMMENT ‘余额’,
status tinyint NOT NULL DEFAULT ‘1’ COMMENT ‘状态’,
created datetime NOT NULL DEFAULT ‘1970-01-01 08:00:00’ COMMENT ‘创建时间’,
modified datetime NOT NULL DEFAULT ‘1970-01-01 08:00:00’ COMMENT ‘修改时间’,
PRIMARY KEY (red_packet_account_id),
UNIQUE KEY uniq_userId (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=217 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT=‘余额表’;

T1:
begin;
insert into account (user_id, balance,status )values( ‘21222’, 12, 1);

 ROLLBACK;

T2:
begin;
insert into account (user_id, balance,status )values( ‘21222’, 12, 1);

COMMIT;
T3:
begin;
insert into account (user_id, balance,status )values( ‘21222’, 12, 1);

COMMIT;

执行顺序：T1 开启事务，执行insert；
T2开启事务，执行insert；
T3开启事务，执行insert；
T1 ROLLBACK 回滚，此时，T2插入成功，T3报死锁错误；
T2，T3执行COMMIT。
查看死锁日志：show engine innodb status;

LATEST DETECTED DEADLOCK

2020-07-31 14:31:52 0x4c14
*** (1) TRANSACTION:
TRANSACTION 4223, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 31, OS thread handle 27912, query id 492 localhost ::1 root update
insert into red_packet_account (user_id, balance,status )values( ‘21222’, 12, 1)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 5 n bits 72 index uniq_userId of table rcp-test.account trx id 4223 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 5 n bits 72 index uniq_userId of table rcp-test.account trx id 4223 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 4224, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 32, OS thread handle 9240, query id 498 localhost ::1 root update
insert into red_packet_account (user_id, balance,status )values( ‘21222’, 12, 1)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 5 n bits 72 index uniq_userId of table rcp-test.account trx id 4224 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 5 n bits 72 index uniq_userId of table rcp-test.account trx id 4224 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

TRANSACTIONS

总结：
T1先加入X锁，T2，T3等待。T1回滚了，T2，T3都加入意向插入锁S，判断这条数据库里没有，就准备升级S到X。因为S，X互斥。此时T2,T3各自持有的S锁都在等待对方释放升级成X锁。