另一种死锁分类

[pagezz-canway]

我在线上环境遇到另一种死锁，但是未在表格中找到，不知道你是否遇到过？

事务一语句	事务二语句	事务一等待锁	事务二等待锁	事务二持有锁	案例
insert	delete	lock mode S	lock_mode X locks rec but not gap	lock_mode X locks rec but not gap

具体日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
190723 18:54:38
*** (1) TRANSACTION:
TRANSACTION E04E0FAF, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 32 lock struct(s), heap size 6960, 431 row lock(s), undo log entries 82
MySQL thread id 364137278, OS thread handle 0x7fb4459f7700, query id 23683965704  operation update
INSERT INTO `account_user_groups` (`user_id`, `group_id`) VALUES (113, 704)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6655 page no 255 n bits 1000 index `account_user_groups_user_id_group_id_4d09af3e_uniq` of table `operation`.`account_user_groups` trx id E04E0FAF lock mode S waiting
Record lock, heap no 282 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000071; asc    q;;
 1: len 4; hex 800002c8; asc     ;;
 2: len 4; hex 81878884; asc     ;;

*** (2) TRANSACTION:
TRANSACTION E04E0FC5, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
42 lock struct(s), heap size 14776, 182 row lock(s), undo log entries 52
MySQL thread id 364137279, OS thread handle 0x7fb444b7e700, query id 23683965809 operation updating
DELETE FROM `account_user_groups` WHERE `account_user_groups`.`group_id` = 713
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6655 page no 255 n bits 1000 index `account_user_groups_user_id_group_id_4d09af3e_uniq` of table `operation`.`account_user_groups` trx id E04E0FC5 lock_mode X locks rec but not gap
Record lock, heap no 280 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000004a; asc    J;;
 1: len 4; hex 800002c8; asc     ;;
 2: len 4; hex 8187888a; asc     ;;

Record lock, heap no 282 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000071; asc    q;;
 1: len 4; hex 800002c8; asc     ;;
 2: len 4; hex 81878884; asc     ;;

Record lock, heap no 466 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000073; asc    s;;
 1: len 4; hex 800002c8; asc     ;;
 2: len 4; hex 81878886; asc     ;;

Record lock, heap no 711 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000072; asc    r;;
 1: len 4; hex 800002c8; asc     ;;
 2: len 4; hex 81878885; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6655 page no 206 n bits 1000 index `account_user_groups_user_id_group_id_4d09af3e_uniq` of table `operation`.`account_user_groups` trx id E04E0FC5 lock_mode X locks rec but not gap waiting
Record lock, heap no 23 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000042; asc    B;;
 1: len 4; hex 800002c9; asc     ;;
 2: len 4; hex 8187889e; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------

[aneasystone]

你好，谢谢你提供的死锁案例，从你的死锁日志可以抽出下面大概的信息：

事务一：

INSERT INTO `account_user_groups` (`user_id`, `group_id`) VALUES (113, 704)

WAITING space id 6655 page no 255
lock mode S waiting

事务二：

DELETE FROM `account_user_groups` WHERE `account_user_groups`.`group_id` = 713

HOLDS space id 6655 page no 255
lock_mode X locks rec but not gap

WAITING space id 6655 page no 206
lock_mode X locks rec but not gap waiting

事务一 INSERT 等待 S 锁，应该是唯一键重复导致，page no 255 正好和下面的事务二的 X 记录锁记录一致，但是事务二在等待 page no 206 的一个 X 记录锁，猜测应该是事务一或者其他事务加上的。

一种可能的死锁解释如下：

事务一	事务二	备注
INSERT INTO account_user_groups (user_id, group_id) VALUES (xxx, 713)
	DELETE FROM account_user_groups WHERE account_user_groups.group_id = 713	需要对所有 group_id = 713 的记录加 X 记录锁，但和上一条记录冲突，等待
INSERT INTO account_user_groups (user_id, group_id) VALUES (113, 704)		该记录存在，但被事务二标记为删除，所以加 S 锁等待，从而导致死锁

但是具体场景还得去看下事务一和事务二的相关代码，确认下每个事务具体的 SQL 语句，从你的日志 431 row lock(s), undo log entries 82 可以看出你这可能是个大事务，事务中的 SQL 比较多。

再次表示感谢，欢迎提个 PR 补充下这个死锁案例。

[pagezz-canway]

你好，我的事务确实较大，但是为了避免死锁，事务首句有
select .. for update 的语句，保证对 group_id 相同的记录，只有一个事务正在更新，所以这个死锁案例只有在线上环境偶现，半年也就出现过几次。
表结构如下：

| account_user_groups | CREATE TABLE `account_user_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_user_groups_user_id_group_id_4d09af3e_uniq` (`user_id`,`group_id`),
  KEY `account_user_groups_group_id_6c71f749_fk_auth_group_id` (`group_id`),
  CONSTRAINT `account_user_groups_group_id_6c71f749_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`),
  CONSTRAINT `account_user_groups_user_id_14345e7b_fk_account_user_id` FOREIGN KEY (`user_id`) REFERENCES `account_user` (`id`)
)

每次事务一定会执行以下语句

DELETE FROM account_user_groups WHERE account_user_groups.group_id = g1
DELETE FROM account_user_groups WHERE account_user_groups.group_id = g2
...
INSERT INTO account_user_groups (user_id, group_id) VALUES (u1, g1)
INSERT INTO account_user_groups (user_id, group_id) VALUES (u1, g2)
INSERT INTO account_user_groups (user_id, group_id) VALUES (u2, g2)

即批量删除group_id为某些值的数据，然后再插入新数据；但是由于开头的select .. for update 的语句，保证对 group_id 相同的记录，只有一个事务正在更新。所以怀疑是next-key 类型的锁导致，但是本地一致无法复现。不知道你是否有解决思路可以参考，谢谢你的回答。

[aneasystone]

为了避免死锁，事务首句有 select .. for update 的语句，保证对 group_id 相同的记录，只有一个事务正在更新

这个 select .. for update 语句是什么样的？针对所有的 g1 g2 ... 都加锁吗？

[pagezz-canway]

每次需要更新的group都一定是另一张表的外键，所以针对另一张表的某一条数据 select .. for update，就保证了2个事务更新的 group 集合不同

[aneasystone]

能否提供下两个事务具体的 SQL？