求分析一个死锁问题以及解决方案

[lry]

表结构如下

CREATE TABLE `system_proxy` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(46) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `port` int unsigned NOT NULL DEFAULT '0',
  `status` tinyint unsigned NOT NULL DEFAULT '0',
  `holder` int unsigned NOT NULL DEFAULT '0',
  `update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `unique_ip_port` (`ip`,`port`) USING BTREE,
  KEY `index_holder_status` (`status`,`holder`) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

show engine innodb status 详细内容在这里 https://paste.ubuntu.com/p/7ZyjCpTDrX/

业务上，多线程会并发进行如下两个 sql 的更新操作，观察上面的日志，应该就是造成概率性发生死锁的原因。

（我不太会看这个日志。。求大腿讲解以及解决方案）

事务 1 update system_proxy set status = 3 where id = ?

事务 2 update system_proxy set status = 1, holder = 3 where status = 0 limit 1

[DoctorCat]

事务 2 应指定 id

[lry]

@DoctorCat 这个业务上没法指定 id，本质就是多线程循环运行，每次运行前先占有一个资源。
这个死锁看起来像是两个事务同时修改了 index_holder_status 这个索引，所以是不是让事务 1 不去更新这个索引就能解决？

[papa2]

事务 2 的 limit 要配合 order by id 才会精确锁定行，事务 2 每条更新单独开启一个事务，循环去开启事务是不科学的

[DoctorCat]

@lry 那样是无法解决的。可以先获取需要更新的记录的主键，然后再去分批 update

[by73]

看了下原因，可能的确跟索引有关，第一个线程找到了这一行 A，加上了行锁（索引锁），更新 status，然后获取 index_holder_status 的索引锁准备更新 status 的索引；但是第二个线程根据 index_holder_status 可能也选中了第一个线程里的行 A （猜测 A 原来的 status 为 0 ），然后准备更新这一行的数据，发现行 A 被加了锁，因此产生了死锁。

当然，我对 MySQL 也不太懂，以上信息都是百度来的（逃

Ref:
1. https://dba.stackexchange.com/a/117030
2. https://zhuanlan.zhihu.com/p/66676020

[geligaoli]

事务 2，既然 limit 1，不如读出这条记录，然后根据 ID 更新。

[beidounanxizi]

好好的 clear 代码不写，写个 concurrent code snippet 整活🐶

[beidounanxizi]

看这记录 大概率是没获取到 gap 锁

[xizismile]

update 更新使用了两个索引导致的死锁问题

可以看下面这个链接：
http://mysql.taobao.org/monthly/2016/03/10/

[Visitor233]

@xizismile 我很好奇这种文章链接是在那找到的，这非常的有用，如果还能找到其他大厂的那更好

[9LCRwvU14033RHJo]

我觉得楼主可以试试 @DoctorCat 说的。将事务 2 分成两步执行：
BEGIN;
select id into @row_id where .... for update SKIP LOCKED
update update system_proxy set status = 1, holder = 3 where id = @row_id;
COMMIT;

其中 select for update 用 skip locked 跳过被其他事务加锁的行。

[9LCRwvU14033RHJo]

@geligaoli 也是这么建议

[xizismile]

@Visitor233 有意思，大多数人想要鱼，你想要的确是渔

分享一哈，我查找这个问题的思路
1.看问题日志描述和下面的一些回答，基本上可以确定的是，mysql 在双索引下，会出现死锁的问题
2.然后拿着三个关键词去百度 /bing/google 搜索，“mysql 双索引 死锁”，这样你就能搜出一大堆博客来，遇到的问题和题主的差不多
3.我上面发的那个淘宝内部网站，来源是我在学 mysql 的时候，在其他资料里看到的，里面大多数是数据库源码原理的解析
因此想在这个网站里面查找，有木有上述的问题，就用到了高级搜索技巧(site 指令表示只在这一个网站内部进行搜索)
“mysql 双索引 死锁 site:mysql.taobao.org”

[Visitor233]

@xizismile 现如今的信息海洋，渔的熟练度不高，捞上来的死鱼解决不了温饱问题呀。
分享一个美团的 tech.meituan.com

[xizismile]

@Visitor233 美团的这个博客也是挺有深度的，赞一个~

[lry]

@user8341 这个应该也可以。第一见 select for update skip locked 的用法。
之前看过一篇文章说，select for update 可以用 先 update 后 select 代替来提高效率，所以没想到这里遇到了索引更新导致的死锁问题。今天我上午修改了一些表和查询设计，避免了同时锁 index_holder_status 索引 (其实就是调整了联合索引的顺序。。。)
```
CREATE TABLE `system_proxy` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`ip` varchar(46) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`port` int unsigned NOT NULL DEFAULT '0',
`holder` int unsigned NOT NULL DEFAULT '0',
`used` tinyint unsigned NOT NULL DEFAULT '0',
`update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`id`) USING BTREE,
KEY `index_holder_used` (`holder`,`used`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5585 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
```

```
// 设置代理资源 holder 为当前线程 id
update system_proxy set holder = #{holder} where holder = 0 limit 1
// 查询线程当前独占的代理资源
select id, ip, port from system_proxy where holder = #{holder} and used = 0
// 更新行记录 修改代理资源状态为已使用
update system_proxy set used = 1 where id = #{id}
```