h0099 最近的时间轴更新
h0099's repos on GitHub
Java · 18 人关注
tbclient.protobuf
从 244 个历史版本百度贴吧客户端中提取出的所有 Protocol Buffers 定义文件 `.proto`
HTML · 15 人关注
httpErrorPage
自定义HTTP错误页
C# · 9 人关注
open-tbm
PHP · 2 人关注
bilibiliVote
bilibili吧2019年吧主公投原始数据
Java · 2 人关注
GTCE-5.10.41
GregTech rewrite for modern versions of Minecraft
2 人关注
Network-Addon-Mod-Install-Options
SC4 NAM33 自定义安装选项 录入/翻译/截图 http://git.oschina.net/n0099/Network-Addon-Mod-Install-Options
Shell · 1 人关注
iobench.sh
Just another simple wrapper over `sysbench fileio` to output a table of common metrics
1 人关注
n0099
1 人关注
open-tbclient
293 个历史版本百度贴吧客户端中的所有 `.java` 文件
C++ · 1 人关注
PaddleOCRSharp
This project is modified and encapsulated by C++ code based on Baidu PaddlePaddle OCR. Net class library. It includes the table recognition function of text recognition, text detection and statistical analysis based on text detection results. At the same time, it is optimized to improve the recognition accuracy in the case of inaccurate small image
1 人关注
Tieba-SimCity
模拟城市吧吧规及导航
1 人关注
TouhouCannonBall-Live2d-Models
https://drive.google.com/drive/folders/1tqZ2aJ9dU_ZolDV4xWsV_rqiCRYJKS4-
0 人关注
archive-TiebaLite
https://web.archive.org/web/20240217185947/https://github.com/HuanCheng65/TiebaLite
Shell · 0 人关注
azcopy_sanoid_zfs_snapshot.sh
Shell · 0 人关注
azure_root-on-zfs_migration.sh
0 人关注
bbcode
[READ ONLY] Subtree split of Flarum bbcode extension.
TypeScript · 0 人关注
berry
📦🐈 Active development trunk for Yarn ⚒
TypeScript · 0 人关注
core
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Shell · 0 人关注
docker-zulip
Container configurations, images, and examples for Zulip.
PHP · 0 人关注
flarum-ext-auth-steam
Flarum Steam Login extension
0 人关注
flarum-redis
Redis cache and queue for @flarum
JavaScript · 0 人关注
level-ranks
Add a level/expierence bar to your flarum Forum.
C# · 0 人关注
nsonic
NSonic is an open-source .NET client implementation for the Sonic search backend.
C# · 0 人关注
opencvsharp
OpenCV wrapper for .NET
0 人关注
pdiff
TypeScript · 0 人关注
res
TypeScript · 0 人关注
resharper_inspectcode
C# static analysis on GitHub Actions.
Visual Basic · 0 人关注
SC4AutoInstallerAndLauncher
模拟城市4 豪华版 自动安装程序及启动器 源代码
0 人关注
signature
✍️ Add signature support to your Flarum forum.
h0099

h0099

V2EX 第 323482 号会员,加入于 2018-06-19 02:26:22 +08:00
今日活跃度排名 3110
h0099 最近回复了
如果单独只看您发的那坨`SHOW ENGINE INNODB STATUS`deadlock detection log 的话那当时场景就是
session2 先执行因而持有着`(bid, sid, oid)`
```
RECORD LOCKS space id 270 page no 6338 n bits 352 index idx_bid_sid_oid of table db.tb trx id 7367656999 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 13 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
```
上的末端`(某值, +∞)`[`nextkey`]( https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-next-key-locks)X 锁因为有[`hex 73757072656d756d; asc supremum`]( https://en.wikipedia.org/wiki/Infimum_and_supremum) https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-5-locks-deeper-dive/

而后执行的 session1 也在尝试 acquire`(bid, sid, oid)=('444444', '555555', '666666')`上的 recordlockX 锁但由于其位于 session2 的`nextkey`X 锁范围中而等待
```
RECORD LOCKS space id 270 page no 6338 n bits 352 index idx_bid_sid_oid of table db.tb trx id 7367657071 lock_mode X waiting Record lock, heap no 13 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
```

当 session1 在等待时 session2 也开始等待 PK (但下面的约束不是在`PRIMARY KEY (id)`上而是`INSERT`中的其他字段可能因为其是`AUTO_INCREMENT`AI 而您也没显式指定其值所以无法`WHERE`)`(bid, sid, oid, emark, amark, bmark, cmark)=('444444', '555555', '666666', '00', '00', '00', '00')`上的[recordlock(有`but not gap`)]( https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-record-locks)X
```
RECORD LOCKS space id 270 page no 7273 n bits 88 index PRIMARY of table db.tb trx id 7367656999 lock_mode X locks rec but not gap waiting
[...]
3: len 25; hex 4f4243574830315a3032363631323032333132323241303536; asc 444444;;
4: len 10; hex 30313939373333303331; asc 555555;;
5: len 6; hex 363733353839; asc 666666;;
[...]
```
所以是互相等待对方而被 deadlock detection 并决定 rollback 后来的 session1 让 session2 先过
```
2023-12-28T19:00:05.140247+08:00 3180165 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)
```
如果您能够修改逻辑避免在 sql 中用到`ON DUPLICATE KEY UPDATE`aka 您不是只能加索引调参优化的 dba 而是透过 orm 层层抽象隔离写 java 业务的 dev 那您也可以像您 at 我过来的那帖中那样换成`RC 事务隔离级别`+`SELECT ... WHERE unique 约束或 PK FOR UPDATE`产生`表级 IX 锁` https://github.com/n0099/open-tbm/issues/32#issuecomment-1401223094 对`SELECT`出既有的 record 来`UPDATE ... SET a|emark = ... WHERE unique 约束或 PK`再对不存在的 record 进行`INSERT`此时可以保证`INSERT`不会返回`ERROR 1062 (23000): Duplicate entry`因为此前的`SELECT ... FOR UPDATE`已经上了`表级 IX 锁` https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-intention-locks 而此时其他`INSERT`需要`行级 X 锁`就会阻塞等待`表级 IX 锁`释放
> Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.
首先您 tb2 删漏了
```diff
CREATE TABLE `tb2` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`bid` varchar(38) NOT NULL,
`expire_time` datetime DEFAULT NULL,
`status` char(2) NOT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
- UNIQUE KEY `idx_name_list_batch_id` (`batch_id`),
+ UNIQUE KEY `idx_name_bid` (`bid`),
KEY `idx_name_list_expire_time` (`expire_time`),
- KEY `index_name_list_market_no` (`market_case_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
```
在 mysql8.0.35 下创建表后
```sql
INSERT INTO tb1(sid, bid, oid, status) VALUES(0, 1, 2, 3);
INSERT INTO tb2(bid, expire_time, status) VALUES(1, ADDDATE(NOW(), 2), 3);
```
默认 RR 事务隔离级别下
```sql
-- session1
START TRANSACTION;
insert into tb(bid, sid, oid, emark, amark, bmark, cmark, update_time) values ('444444', '555555', '666666', '00', '00', '00', '00', now()) ON DUPLICATE KEY UPDATE amark = values(amark), update_time = now();
-- 重复执行
ROLLBACK;
```
```sql
-- session2 您的`'2023-12-31 19:00:03.963'`我改成了`ADDDATE(NOW(), 3)`以便 reprod
START TRANSACTION;
insert into tb(bid, sid, oid, emark, amark, bmark, update_time) select tb1.bid, tb1.sid, tb1.oid, '10', '00', '00', now() from tb1 left join tb2 on tb1.bid = tb2.bid where tb2.expire_time < ADDDATE(NOW(), 3) and tb2.expire_time > now() and tb1.status IN ('2', '3') ON DUPLICATE KEY UPDATE emark = values(emark), update_time = now();
-- 重复执行
ROLLBACK;
```
不论两个谁先执行执行几次它们都不会等待 acquire 另一事务占有的锁
而在 RC 下
|session1|session2|
|-|-|
|`SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;`|Ibid.|
|`START TRANSACTION;`|Ibid.|
|`SHOW SESSION VARIABLES LIKE '%iso%'\G`|Ibid.|
|`insert into tb(bid, sid, oid, emark, amark, bmark, cmark, update_time) values ('444444', '555555', '666666', '00', '00', '00', '00', now()) ON DUPLICATE KEY UPDATE amark = values(amark), update_time = now();`||
||`insert into tb(bid, sid, oid, emark, amark, bmark, update_time) select tb1.bid, tb1.sid, tb1.oid, '10', '00', '00', now() from tb1 left join tb2 on tb1.bid = tb2.bid where tb2.expire_time < ADDDATE(NOW(), 3) and tb2.expire_time > now() and tb1.status IN ('2', '3') ON DUPLICATE KEY UPDATE emark = values(emark), update_time = now();`|
||等待 session1 释放某个锁|
|`ROLLBACK;`||
||继续执行并返回|

而如果 session2 先执行那后续它们以任意顺序执行都不会有等待
这可能是因为`tb`此时作为空表(`CREATE`后没有`INSERT`过) session1`INSERT`后需要表级锁以保证其他事务能够 RC (看到其刚`INSERT`的这行,但由于不是`READ UNCOMMITTED`RU 级别所以需要等待 session1`ROLLBACK`/`COMMIT`)
https://stackoverflow.com/questions/56434878/mysql-deadlock-using-an-index-with-a-new-value/56435470#56435470
> In an empty table, the place that the 1 would have been is "anywhere in the table" (or anywhere from the start to the "supremum" mentioned in the deadlock) - which is consequently gaplocked by the delete. The same is true for the 2. And those locks do not conflict each other by definition.

而在 session2 先`INSERT`后`tb`不是空表所以 session1 无需表级锁而可以在索引`idx_bid_sid`上锁`('444444', '555555')`,而 session2`INSERT SELECT`subquery 所返回行`(bid, sid)=(1, 0)`中也没有跟`('444444', '555555')`冲突因而无需`ON DUPLICATE KEY UPDATE`从而避免等待 session1 释放对那行的锁
所以您的`tb`表在 session1 执行`INSERT`时是空的吗?
事务 2`INSERT INTO tb SELECT ...`的 subquery
```sql
select tb1.bid, tb1.sid, tb1.oid, '10', '00', '00', now()
from tb1 left join tb2
on tb1.bid = tb2.bid
where tb2.expire_time < '2023-12-31 19:00:03.963'
and tb2.expire_time > now()
and tb1.status IN ('2', '3')
```
返回中有没有`(bid, sid, oid)`=`('444444', '555555', '666666')`的行?
https://www.v2ex.com/t/908047#r_14219204
https://www.v2ex.com/t/1005536#r_14219181

我的评价是:建议先大脑升级`最新最热最凶最恶`mysql8.2
#13 @netabare 建议直接使用四叶信安底层壬上壬上海贵族 FSF EFF 精神会员杨博文阁下 @yangbowen 最爱的 GPG PGP 包装信息
不适合首页上的登录墙内信息茧房:

这就是 v2 机场
什么讨论
关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1028 人在线   最高记录 6543   ·     Select Language
创意工作者们的社区
World is powered by solitude
VERSION: 3.9.8.5 · 16ms · UTC 22:21 · PVG 06:21 · LAX 15:21 · JFK 18:21
Developed with CodeLauncher
♥ Do have faith in what you're doing.