@n0099 github.com/n0099/TiebaMonitor/issues/32#issuecomment-1401223094

github.com/n0099/TiebaMonitor/issues/32#issuecomment-1401116944

> 我的理解是这里需要的行为实际上是一个原子的 [RMW 操作]( en.wikipedia.org/wiki/Read%E2%80%93modify%E2%80%93write) ——如何插入这一行是依赖于 相同的行是否已被插入 这个要读取到的条件的。这个读操作+插入操作整体上必须是原子的 /事务的。

是，所以阁下也看到了我后来需要给`SELECT`加上`FOR UPDATE`从而实现每个线程通过`SELECT`就能锁住他们准备`INSERT`的`行 a 或 b`，mysql 称其为`IX 锁`：dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks

> 那我觉得您的这个场景也是类似的，您需要的是一个原子的 仅当不会冲突才写入 的操作，不可能只用低共识数的 总是读取 总是写入 操作实现。

而仅靠一个与 mysqld 进程毫无关系的进程范围全局锁是不可能保证 `SELECT/INSERT`和对`进程锁`的写 会被包裹进同一个原子操作的

> CAS 、LL/SC 具有更高的 共识数 ，不可能只用 原子读 、原子写 之类的具有更低 共识数 的操作实现，无论做多少个这样的操作。

所以我们的`四叶头子 CS 硕士 PLT 理论中级高手仏皇 irol 阁下` @kokoro-aya 再一次从计算机科学理论研究的角度为我们提前证明了这一点，而无需亲自下凡接触 mysqld

> * 它不会发生死锁。不论 CAS 是否成功发生，它都立即返回而不作等待。它根本不阻塞，所以也不存在死锁。

而对于 SQL 的`TRANSACTION`而言不可能要求用户一次性就能把他想封装成原子操作的所有语句都发送过来让数据库处理
也就是说用户发送的语句是一条条分开的（每条语句之间可以间隔无限久时间），而不是在单次通信中就发送（实际上如果用户提前就知道我到底要发送多少条语句，那`TRANSACTION`所带来的原子性的意义就被削弱了，只剩下事务中任意语句失败时整个事务都会`ROLLBACK`以保证数据一致性这个用途）
```sql
START TRANSACTION;
SELECT ...;
INSERT ...;
COMMIT;
```
那么此时如果某个语句显式声明了锁，如`SELECT ... FOR UPDATE`产生了`IX 锁（有意排他锁）`
或由于当前事务隔离级别（如`SERIALIZED`）导致语句隐式声明了锁
那么有上锁就必定会有并行事务一直阻塞等待解锁，直到 serverfault.com/questions/241823/setting-a-time-limit-for-a-transaction-in-mysql-innodb

> * 它也不会发生[活锁]( en.wikipedia.org/wiki/Deadlock#Livelock)。如果某个线程的 CAS 失败，那是因为其它线程在它读取和 CAS 的间隙当中做了写入。虽然此线程需要在循环中重新 CAS 一遍，但导致其失败的线程一定成功完成了写入。所以总是有进展发生，不存在活锁。

而对于这种传统的阻塞锁设计而言只要复数个线程请求同一资源的频率足够高就很容易导致`livelock`，结果哪个线程都没有真的`INSERT`，enwiki 条目也进一步指出`livelock`是[starvation]( en.wikipedia.org/wiki/Starvation_(computer_science))的特例：`Livelock is a special case of resource starvation; the general definition only states that a specific process is not progressing.`

> * CPU 提供的这种指令通常宽度有限——只能对一定宽度的数据做这个原子操作，不能将这种原子性扩展到更大的数据结构当中。这种宽度限制不能被简单地克服，无法只用较窄的原子 CAS 实现更宽的原子 CAS 。x86 最长提供到机器字长两倍宽度的 CAS ，即 32 位下的 CMPXCHG8B 指令和 64 位下的 CMPXCHG16B 指令。

`无法只用较窄的原子 CAS 实现更宽的原子 CAS` 我的理解是同样是因为阁下此前提及的`共识数`，比如用两个`32 位 CAS 操作`来试图封装一个`64 位资源`成原子性的会导致其`共识数`不同，如 en.wikipedia.org/wiki/Consensus_(computer_science)#Consensus_number 的表格中指出：
Consensus number|Objects
-|-
$2n-2$|n-register assignment

> 如何用宽度受限的原子 CAS 操作实现更复杂的无锁数据结构是一个被广泛研究的问题。但对数据库这类更重量级的软件实现（而且可以接受高得多的操作延迟）而言，理应不受如此紧凑的限制。

所以传统数据库厂商无法把 CPU 指令集层面提供的`同步原语`进一步封装嵌入 RDBMS 设计中通过抽象隔离暴露给用户来用

> * 上面用伪代码表示的这个方案只涉及了`总是要写入只是写入多少需要根据读取值决定`的简单情况。对这个做一定的调整也不难类似地实现例如`只对当前全局值的一部分情况要做写入，否则需要等待其它线程对全局值做更多修改`之类的情形。或者说这个`while`可以兼具自旋锁的功能。同时根据需要还可以在循环体内加入 sleep 或者阻塞的语句。这些情况下，上述关于`它不会死锁也不会活锁`的表述不再适用。

只要引入了对`desync`（当要写入的值已经被改了就说明发送了同步失效，也就是违反了 en.wikipedia.org/wiki/Linearizability ）的资源进行等待（不论上锁还是解锁）都会重新回到阻塞锁模型中所以`dead/livelock`的幽灵又回来了

> 总之，这是一个很通用的原理。我猜对于数据库的分布式访问来说应该也是有原理相当的构造的。只是发生在更长的延时、更大的数据、更复杂的数据结构。

传统老牌 RDBMS 中基本没有什么无锁数据结构，毕竟其内部结构实现过于复杂恶俗使得他们无从下手改造成无锁的，这也是为什么 V2EX 的 @daxiguaya 于 www.v2ex.com/t/908047#r_12595620 指出
> 还要考虑间隔锁、表锁之类的问题,除非死锁的时候我会去 DEBUG 数据库的锁状态,其它情况我想操这个心 :)

@n0099 github.com/n0099/TiebaMonitor/issues/32#issuecomment-1401199725

github.com/n0099/TiebaMonitor/issues/32#issuecomment-1401081050

> 既然`线程 2`在`START TRANSACTION`之后的读`SELECT * FROM t`，由于`线程 1`的写的成功`COMMIT`，其读取值已不再正确，那么`线程 2`的`COMMIT`不该失败吗？否则它岂不是`TRANSACTION`了个寂寞？

在图
user-images.githubusercontent.com/13030387/214179293-c04a07a2-41cd-4b01-bb01-3852c4a426e3.png
中`线程 2`是在`线程 1`执行`INSERT`和`COMMIT`之前就`SELECT`了的，因此`线程 2`认为行 a 不存在，而`线程 2`如果不`INSERT`行 a 而是直接`COMMIT`那么什么错误都不会发生
因为`COMMIT`本就极少会产生错误（ stackoverflow.com/questions/3960189/can-a-commit-statement-in-sql-ever-fail-how ），所以所有图中的`DUPLICATE`错误都是在执行`INSERT`时就发生的而不是`COMMIT`（图中画在一起了所以容易误导）

SQL 中的`TRANSACTION`只是用来封装多个 SQL 使其成为原子操作的，就像 CAS

> 如果`TRANSACTION`的存在并没有让事务中的这两个语句产生什么关系，换言之`COMMIT`只是让`INSERT`生效的话。

`COMMIT`让`INSERT`生效只不过是二次确认了`写入行 a 或 b`这个操作所以数据库会把这个行真的写入硬盘

实际上对于 mysql innodb 而言`COMMIT`写入后还有一大堆内存缓冲区如[redolog]( dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-logging.html) [doublewrite]( dev.mysql.com/doc/refman/8.0/en/innodb-doublewrite-buffer.html)，只有等到这些内存缓存的 buffer page 也被 fsync 到硬盘上了才是真的数据落地
然而有些硬盘驱动会欺骗系统以让[fsync syscall]( dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_method)立即返回但实际上数据还在硬盘的缓存里并没有实际写入持久存储：
www.percona.com/blog/2018/02/08/fsync-performance-storage-devices/
dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
> 许多操作系统和一些磁盘硬件会欺骗刷新到磁盘的操作。他们可能会告诉 [mysqld]( dev.mysql.com/doc/refman/8.0/en/mysqld.html)刷新已经发生，即使它没有发生。在这种情况下，即使使用推荐的设置也无法保证事务的持久性，在最坏的情况下，断电可能会损坏 InnoDB 数据。在 SCSI 磁盘控制器或磁盘本身中使用电池供电的磁盘缓存可以加快文件刷新速度，并使操作更安全。您还可以尝试禁用硬件缓存中的磁盘写入缓存。

但这一切对数据库用户而言都是无关的（抽象隔离），用户只需要知道`COMMIT`了就是不可撤销地写入数据库了
请注意即便`COMMIT`了也不代表其他并行事务就能知道您已经`COMMIT`了`INSERT`了`行 a`（也就是`dirty read`），因为在防止`dirty read`的事务隔离级别（`REPEATABLE READ`及以上，如`SERIALIZED`）中其他事务有可能不知道您`INSERT`了的（因为其他事务先前`SELECT`过所以产生了`SNAPSHOT`，从而保证`REPEATABLE READ`）

> 那么既然您的两个（或更多个）线程，在`INSERT`和`COMMIT`**之间**并没有与全局锁或者其它线程有任何交互

实际运行时环境的线程比这的 2 个更多，并且在`COMMIT`之前会有许多个`INSERT`被执行，所以会有更多交互，我画的图是试图简化模型

> 那么从开始`INSERT`到`COMMIT`起作用的这段时间对于该线程以外是没有看得见的效果的。`INSERT`立即生效，跟`INSERT`然后立即`COMMIT`然后生效，从外面看起来是一样的。所以，这和您不用`TRANSACTION`应当没有任何差别。

如果阁下的`INSERT 生效`是指让其他事务看得见所`INSERT`的行（`dirty read`），那么必须等到`COMMIT`之后才有可能发生`dirty read`，因此此处的所有`SESSION`的事务隔离级别都是`READ COMMITTED`而不是最弱的`READ UNCOMMITTED`
对于 mysql ，如果`线程 2INSERT 行 a`时数据库层发现这违反了`UNIQUE 约束`（因为`线程 1`已经这么做了），那么在此时就会返回错误并静默地`ROLLBACK`事务而不是等到`COMMIT`时再这么做
使用事务包裹`SELECT`和`INSERT`的目的是为了让他们进入同一个原子操作中（就像 CAS ），因为实际上`在`COMMIT`之前会有许多个`INSERT`被执行`所以需要避免在`INSERT`重复行时由于 mysql 返回错误而只`INSERT`了一半的行

> 这应该也是不对的吧？允许 dirty read 的发生，应该并不是说保证它会发生？

我没有说把事务隔离级别从`REPEATABLE READ`降低到`READ COMMITTED`就一定会发生`dirty read`，如果您只有一个线程在这对着一个`SESSION`执行 SQL （也就是`并行度=1`），那么什么`race condition`都不会发生

@yangbowen https://github.com/n0099/TiebaMonitor/issues/32#issuecomment-1401116944

> `DUPLICATED`表示`线程 2`试图插入已经被`线程 1`插入了的行，因此违反了数据库层的`UNIQUE`约束

我的理解是这里需要的行为实际上是一个原子的 [RMW 操作]( https://en.wikipedia.org/wiki/Read%E2%80%93modify%E2%80%93write) ——如何插入这一行是依赖于 相同的行是否已被插入 这个要读取到的条件的。这个读操作+插入操作整体上必须是原子的 /事务的。
比较常见的一种做法是提供 [CAS]( https://en.wikipedia.org/wiki/Compare-and-swap) 原子操作：如果满足[条件]那么[写入]否则[失败] 。另一种做法是提供 [LL/SC]( https://en.wikipedia.org/wiki/Load-link/store-conditional) ：如果先前读取的[几个位置]没有被写入那么[写入]否则[失败]。x86 提供前者，而 ARM 提供后者，而不只是提供原子的读、写。以 CAS 为例，使用者可以采取如下方案：
```
临时值 = 读(全局值);
临时值 2 = 计算(临时值);
while (!CAS(全局值, 临时值, 临时值 2)) {
临时值 = 读(全局值);
临时值 2 = 计算(临时值);
}
```
其中`CAS`是原子的
```
CAS(data, expected, desired) {
if (data == expected) {
data = desired;
return true;
} else {
return false;
}
}
```
实际上维基百科关于上面说的 RMW 操作的 [词条]( https://en.wikipedia.org/wiki/Read%E2%80%93modify%E2%80%93write) 也已指出，CAS 、LL/SC 具有更高的 共识数 ，不可能只用 原子读 、原子写 之类的具有更低 共识数 的操作实现，无论做多少个这样的操作。
那我觉得您的这个场景也是类似的，您需要的是一个原子的 仅当不会冲突才写入 的操作，不可能只用低共识数的 总是读取 总是写入 操作实现。

---

关于这个方案再多说几点。

- 它不会发生死锁。不论 CAS 是否成功发生，它都立即返回而不作等待。它根本不阻塞，所以也不存在死锁。
- 它也不会发生[活锁]( https://en.wikipedia.org/wiki/Deadlock#Livelock)。如果某个线程的 CAS 失败，那是因为其它线程在它读取和 CAS 的间隙当中做了写入。虽然此线程需要在循环中重新 CAS 一遍，但导致其失败的线程一定成功完成了写入。所以总是有进展发生，不存在活锁。
- CPU 提供的这种指令通常宽度有限——只能对一定宽度的数据做这个原子操作，不能将这种原子性扩展到更大的数据结构当中。这种宽度限制不能被简单地克服，无法只用较窄的原子 CAS 实现更宽的原子 CAS 。x86 最长提供到机器字长两倍宽度的 CAS ，即 32 位下的 CMPXCHG8B 指令和 64 位下的 CMPXCHG16B 指令。如何用宽度受限的原子 CAS 操作实现更复杂的无锁数据结构是一个被广泛研究的问题。但对数据库这类更重量级的软件实现（而且可以接受高得多的操作延迟）而言，理应不受如此紧凑的限制。
- 上面用伪代码表示的这个方案只涉及了`总是要写入只是写入多少需要根据读取值决定`的简单情况。对这个做一定的调整也不难类似地实现例如`只对当前全局值的一部分情况要做写入，否则需要等待其它线程对全局值做更多修改`之类的情形。或者说这个`while`可以兼具自旋锁的功能。同时根据需要还可以在循环体内加入 sleep 或者阻塞的语句。这些情况下，上述关于`它不会死锁也不会活锁`的表述不再适用。

总之，这是一个很通用的原理。我猜对于数据库的分布式访问来说应该也是有原理相当的构造的。只是发生在更长的延时、更大的数据、更复杂的数据结构。

https://github.com/n0099/TiebaMonitor/issues/32#issuecomment-1401081050 @yangbowen

> 可以看出在符合这个时序图的流程中进程锁和数据库层事务都无法阻止这种冲突，因为
> 1. `线程 2`访问数据库表中已有行的时机早于`` 线程 1``COMMIT ``他的`INSERT`，所以`线程 2`无法预见`线程 1`将在未来插入行`a`（由于`READ COMMITED`事务隔离级别）
> 2. `线程 2`访问进程锁的时机又晚于`线程 1`完成`COMMIT`和释放进程锁中的行`a`，所以`线程 2`也不知道此前`线程 1`已经插入了行`a`

蛮怪的。我不懂数据库但是，既然`线程 2`在`START TRANSACTION`之后的读`SELECT * FROM t`，由于`线程 1`的写的成功`COMMIT`，其读取值已不再正确，那么`线程 2`的`COMMIT`不该失败吗？否则它岂不是`TRANSACTION`了个寂寞？
如果`TRANSACTION`的存在并没有让事务中的这两个语句产生什么关系，换言之`COMMIT`只是让`INSERT`生效的话。那么既然您的两个（或更多个）线程，在`INSERT`和`COMMIT`**之间**并没有与全局锁或者其它线程有任何交互，那么从开始`INSERT`到`COMMIT`起作用的这段时间对于该线程以外是没有看得见的效果的。`INSERT`立即生效，跟`INSERT`然后立即`COMMIT`然后生效，从外面看起来是一样的。所以，这和您不用`TRANSACTION`应当没有任何差别。

> 可见降至`READ UNCOMMITED`后允许`dirty read`的发生，也就是对于如下时序：

这应该也是不对的吧？允许`dirty read`的发生，应该并不是说保证它会发生？

1. `@before` 和 `@after` 是指 JUnit 中的一个 attribute? https://stackoverflow.com/questions/20295578/difference-between-before-beforeclass-beforeeach-and-beforeall 结合阁下最近的回复 https://www.v2ex.com/t/910378#reply8 ，我暂且蒙古

> Kotlin 能做到的 Scala 都能做到

2. 经典类型系统的表达力决定了整个语言能做到什么

> lombok 那套，感觉预处理的写法让代码变得不透明了

3. 不用 lombok 难道您很喜欢手动复制粘贴满屏幕的 g/setter 方法和字段访问吗（而很明显 java 没有 c#的 auto prop 语法糖）？要是复制粘贴过程中扣错字段名了呢？
4. 如果阁下不喜欢静态语言中用于实现 ruby 那样的元编程的 source generator 轮子那我建议您也少碰 c/cpp 的宏和`php: hypertext preprocessor`和 css 预处理器（ sass scss less ），因为他们的目的都是像 c#人滥用语法糖那样让代码变得不透明（然而语法糖远没有元编程自由，所以不透明的程度不如后者）
5. 与此同时截止 2023 年 1 月，奥利金德数理理论学家 dc 神的旗舰开源项目免 fq 上 p 站的 pixeval 第三方 c#客户端中仍在使用 source generator 来自动复制粘贴 i18n 文本： https://github.com/Pixeval/Pixeval/commit/ee13443205f8ed68dcc6dce87687f4cb341dde27 https://github.com/Pixeval/Pixeval/pull/278 在我看来这同样的`灯谜大会`
6. 但我在 https://github.com/n0099/TiebaMonitor/commit/8874e423b5345e66f81fc59e1ffe83f64a7d6d89 之后也希望能用 source generator 来自动生成我来回复制粘贴了十分钟的这些类型不安全（如果符号名不同也没有错误，也就是 3.中的`复制粘贴过程中扣错字段名`）的狗屎样板

7. 请问如何在这 https://github.com/n0099/TiebaMonitor/commit/4f098c8ef7f2fdd089a43ca99746a666c4bd10fc 之中避免使用 attribute 同时又不需要在每次使用 jsonserializer.serialize 时手动判断参数类型并决定是否传入这个 converter? https://learn.microsoft.com/en-us/dotnet/standard/serialization/system-text-json/converters-how-to

8. 阁下不应该只将 attribute 视作运行时反射的唯一用途，尽管这是其常见用途，对于刚从动态语言转静态语言的人（如我）而言滥用反射主要是为了实现动态语言中常用的表达（所以我以前说动态语言天天反射）

9. 而在 https://github.com/n0099/TiebaMonitor/commit/34f9c32dd346a22878ea8dcf2ac82fe46169c8bc 中我将类型确定的反射都改成了朴素的所谓`member selector`（也就是通过函数参数将 project 出某个类成员的任务委托给 caller 而不是硬编码在内部）

10. 但在 https://github.com/n0099/TiebaMonitor/blob/c414ca3429ceb1cd4a7607c10fb79cb608b7cd2d/crawler/src/Tieba/Crawl/Saver/CommonInSavers.cs#L79 中我并没有将这的反射给彻底削除，因为 https://github.com/n0099/TiebaMonitor/blob/c414ca3429ceb1cd4a7607c10fb79cb608b7cd2d/crawler/src/Tieba/Crawl/Saver/StaticCommonInSavers.cs#L5 的目的只是把类 https://github.com/n0099/TiebaMonitor/blob/v2/crawler/src/Db/Post/ThreadPost.cs 和类 https://github.com/n0099/TiebaMonitor/blob/v2/crawler/src/Db/Revision/ThreadRevision.cs 之间名字相同的字段给合并赋值一遍（也就是 js 人最爱的`Object.assign({}, {})`），我当然可以用同样的手法来把这个反射 setvalue 给换成调用 revivison 类中的某种 merge 方法并在其中逐一复制所有已知的硬编码的类实例中同名的 prop 值，就像是阁下最痛恨的对`两个结构相似的类`却要写高度相似的重复代码来处理不同的类型： https://github.com/n0099/TiebaMonitor/blob/c414ca3429ceb1cd4a7607c10fb79cb608b7cd2d/crawler/src/Tieba/Crawl/Parser/ThreadParser.cs#L17 ，也就是我此前于 https://sora.ink/archives/1574?replytocom=800#respond 中所说的：
> @dylech30th ts 那种 ducktype 也算传统 oo 的严格 subtype?
> 就像四叶 CS 硕士 PLT 中级高手 irol 阁下此前锐评 java 魔怔人为了让两个结构十分相似的类能够兼容而写出一个逐类 prop 去复制粘贴的 converter （常见于 bean 中，而我也被迫在 c#中写出了这样的恶俗玩意： https://github.com/n0099/TiebaMonitor/blob/e84a230fa0eb1c1095f6b6aa74b34a29f1f6a69d/crawler/src/Tieba/Crawl/Parser/ThreadParser.cs#L45 ）的刷代码函数和运行时开销的罪恶行径，而在 ducktyping 中只要结构相似那他们就是互相兼容同一个类型（如果不考虑逆变协变不变）

11. 然而我也只多举了一个反射的常见用例，事实上由于 8.中的`动态语言天天反射`使得您总能找到新的奇妙深刻反射（远比您所见的基于字符串的 DI/IoCcontainer/AOP 谔谔）

12. 如果阁下想完全不用反射建议去写 AOT 编译的 c/cpp/rust ，并且也别使用 RAII 在编译时给类结构附赠的元数据

#9 @netabare
> 保证数据类型的一致性和准确性

楼上#5 早已道明
> 多个 view ，然后 view 通过一些叫做 trigger 的 hook 进行前置、后置处理吐给客户端

> 客户端前端进行数据检验和匹配

建议立即开始写阁下最痛恨的几百上千行的 PL/SQL T-SQL 存储结构

> rdbms 就是一个 serverless 的低代码平台。你只关心把一些 lambda 在一个界面提交给系统，不用关心他在哪里执行；
> sql 语言就是低代码语言；
> JOIN 就是 graphql

这下现代中台娱乐圈壬上壬们又梦回他们最痛恨的 80/90s COBOL 了

> 您可以先尝试 innodb 的两种压缩方式：tablespace 层面（`ALTER TABLE ... ROW_FORMAT=COMPRESSED`）和 page 层面的压缩（`ALTER TABLE ... COMPRESSION="zlib"`）

我做了一丶丶测试： https://github.com/n0099/TiebaMonitor/issues/33

> 昨天我在本站使用指南节点看到站长说骂人也属于言论自由

您是说 https://www.v2ex.com/t/36991 吗，这都 12 年的主题帖了

> 怎么记住那么多页面的？感觉某些好像不是搜索就行的，有什么方法吗

建议立即安装浏览器扩展之 https://chrome.google.com/webstore/detail/better-history/egehpkpgpgooebopjihjmnpejnjafefi https://chrome.google.com/webstore/detail/session-buddy/edacconmaakjimmfgnblocblbcdcpbko

> 你说的那个 bit array 的位数是 tag 的数量吧

是，比如您现在总共有 10 个 tags 那么 bitmask 的长度就是 10 位（但注意大多数环境下处理 bitmask 时都会被视作 unsigned int 处理并对齐到最近的 int32/64 上因此即便您只需要 10 位他内部也还是 padding 了一堆 0 的 32/64 位）
如果使用 mysql 对 bitmask 的封装类型之 https://dev.mysql.com/doc/refman/8.0/en/set.html 那就是`SET(a,b,c,d,e,f,g,h,i,j)`
请注意由于 mysql 的 set 类型只不过是对不同长度的 int 类型的封装所以他也受到最长的 bigint 也只有 64 位的限制，所以当您的 tags 超过 64 个时您应该增加第二个 tags 列来存储，这样也会稍微增加查询时的复杂度（多复制粘贴约束一个字段），如果以后 mysql 自带了 bignumber 那样的可以进行数学运算的长度“无限”（实际上一般是 2^32-1 ）的 int 类型那您就不需要按 64 个来拆分
不要使用字符串类型（定长 varchar/变长 text ）来存储二进制位的字符串表达（比如 0101010 ），因为他们实际上是 ASCII 的 0x30 和 0x31 字节，也就是说阁下花了 8 倍的空间（ 1byte=8bits ）来存储他们，而且还成功实现了无法直接进行位运算（除非先把二进制字符串转二进制 int ）以实现 CRUD tags 集合或 xor 计算 hamming 距离

> 为了防止你有什么更高明的方法问一下

v2 人们会直接推荐您上各种现成的开源或商业的推荐算法封装实现产品，这样您就只需要做调包侠把 mysql 已有的数据灌进去就能获得“实时”计算出的相关推荐流，但这同样也带来了您去学习如何调包的成本（当然总比折腾 mysql 简单）

> 存储方面我只能想到数据库压缩存储

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html 进一步指出
> SET('value1','value2',...) 1 、2 、3 、4 或 8 个字节，具体取决于集合成员的数量（最多 64 个成员）

实际上阁下并不需要担心存储这一堆纯 int 有多大，您之前也看到了阁下生成的 10m 行的 content_tag_rel 表才几百 m 大
您可以先尝试 innodb 的两种压缩方式：tablespace 层面（`ALTER TABLE ... ROW_FORMAT=COMPRESSED`）和 page 层面的压缩（`ALTER TABLE ... COMPRESSION="zlib"`）： https://blog.koehntopp.info/2021/09/09/mysql-two-kinds-of-compression.html https://dev.mysql.com/doc/refman/8.0/en/innodb-compression.html
如果阁下真要十亿级别的行并空间尽可能小建议直接换列存储（如 clickhouse tidb ）或时序（如 influxdb ）数据库，但这也同样陷入了上述`v2 人们会直接推荐您`

> 计算方面怎么处理我就想不出来了

建议复习 https://en.wikipedia.org/wiki/Mask_(computing)
假设您现在每个 content 行中的 tags 字段类型是`SET(a,b,c,d)`，因此您总共有着 4 个可能的 tags ，按经典的 CRUD 来看：
C：给某个 content 增加`a`tag：`tag = tag | 0b0001`（注意是小端序，下同）
R：tags 中是否有`a`：`tag & 0b0001 = 0b0001`
U：反转 tag （如果已经有`a`就删除，反之亦然）：`tag ^ 0b0001`，其中^是 XOR 异或运算符 https://en.wikipedia.org/wiki/Exclusive_or https://en.wikipedia.org/wiki/XOR_gate
D：删除`a`：`tag & 0b1110`，注意这的操作数跟 R 是完全相反的，但运算符都是 AND ，也就是说~0b0001=0b1110 ，其中~是 NOT 运算符 https://en.wikipedia.org/wiki/Negation
以上所有运算都可以同时对多个 tag 执行并在单个位运算之内完成
也就是说如果您想查询同时有着 tag`a`和`c`，那您只需要写`WHERE tags & 0b0101 = 0b0101`，而不需要分开写两个 AND 约束
这被称作 https://en.wikipedia.org/wiki/Bit-level_parallelism 或者说 https://en.wikipedia.org/wiki/Single_instruction,_multiple_data

最后回到阁下的计算 tags 集合相似度上：
如果使用对所有 content 两两配对计算之间的 hamming 编辑距离法找出距离<=5 的 tags 集合（ 5 是指两个 tags bitmask 之间最多相差 5 个位 /tags ）
最朴素的方式是`SELECT * FROM table WHERE BIT_COUNT(tags ^ 当前 content 的 tags) <= 5`，然后对所有 content 的 tags 循环执行这个 sql ，也就是`O(2n)`的时间复杂度
但由于 hamming 距离运算是符合交换律的也就是说 a 与 b 之间（`BIT_COUNT(a 的 tags ^ b 的 tags)`）跟 b 与 a 之间（`BIT_COUNT(b 的 tags ^ a 的 tags)`）是相同的距离，因此您就可以像乘法表裁剪掉了斜向的一半重复值那样只计算一半配对的结果，也就是`O((n*n+1)/2)`的时间复杂度
进一步的阁下还可以利用 https://github.com/Starry-OvO/aiotieba/pull/63#issuecomment-1364693204 中的思路将 32/64 位长的 bitmask 给分组拆成更小的多个 int8/16 字段，然后通过多个 WHERE 约束来 pre-filter 以进一步减少最终需要计算 hamming 距离的行数

> 我认为"进程锁"应当能解决你提出的"幻读"的问题

我遇到的不是幻读而是“滞后”读，您可以从图 1 中看出来线程 2 读取进程锁时线程 1 已经释放了线程 1 此前插入并锁的行 a ，而线程 2 此前从数据库中读时还不存在行 a ，所以导致线程 2 从两处取得（时机上一个太早一个太晚）的事实都是不存在行 a

> 它存的是真正写入的数据

准确地说进程锁缓存着的行在数据库中有 3 种状态：即将 INSERT （还没 COMMIT ），已经 INSERT （ COMMIT 了，只有这个状态下是`真正写入的数据`），INSERT 失败（ ROLLBACK 了）

> 只能说目前只能保证不会重复插入导致整个事务回滚,这个应该更重要?

是

> 提早去识别"垃圾数据": 非当前所有活跃"写事务"需要的数据都是"垃圾数据"

的确可以提前不去生成已经被进程锁占据着的行的数据

> 在 `"线程 1"向"进程锁"插入数据->处理失败"进程锁"数据` 的中间会有`"线程 2"读取"进程锁"`

所以阁下的意思还是建议使用您最初提出的`"进程锁"清理数据的时机可以调整下,等到没有任何活跃事务的情况下再把它清掉.`
但问题并不在线程 2 在线程 1 还占据着进程锁中的行 a 时去读取进程锁中的行 a ，因为进程锁的目的不就是让线程 2 （或更多后续任务）知道现在行 a 被线程 1 占据着吗？

> 还要考虑间隔锁、表锁之类的问题

根据 https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks
> 对于使用唯一索引锁定行以搜索唯一行的语句，不需要间隙锁定。（这不包括搜索条件只包括多列唯一索引的某些列的情况；在这种情况下，确实会发生间隙锁定。）例如，如果该 id 列具有唯一索引，则以下语句仅使用一个索引记录锁定 id 值为 100 的行，其他会话是否在前面的间隙中插入行并不重要：
> SELECT * FROM child WHERE id = 100;
> 如果 id 未索引或具有非唯一索引，则该语句会锁定前面的间隙。

而这里的 a 所在的字段是具有 UNIQUE 约束的并且 SELECT 的 WHERE 子句使用=而不是< > BETWEEN 等 range operator ，所以只有 row lock 而没有 gap lock

> 这里还值得注意的是，不同事务可以在间隙上持有冲突锁。例如，事务 A 可以在一个间隙上持有一个共享间隙锁（间隙 S 锁），而事务 B 在同一间隙上持有一个独占间隙锁（间隙 X 锁）。允许冲突间隙锁的原因是，如果从索引中清除记录，则必须合并不同事务在记录上持有的间隙锁。
> 间隙锁 InnoDB 是“纯粹抑制性的”，这意味着它们的唯一目的是防止其他事务插入间隙。间隙锁可以共存。一个事务获取的间隙锁不会阻止另一个事务在同一间隙上获取间隙锁。共享和排他间隙锁之间没有区别。它们彼此不冲突，并且它们执行相同的功能。

这应该暗示了`SELECT ... WHERE uniq > 1 FOR SHARE`（ gap IS ）可以与`SELECT ... WHERE uniq > 1 FOR UPDATE`（ gap IX ）同时执行

> 可以明确禁用间隙锁定。如果您将事务隔离级别更改为 READ COMMITTED ，则会发生这种情况。在这种情况下，间隙锁定在搜索和索引扫描中被禁用，并且仅用于外键约束检查和重复键检查。
> 使用隔离级别 READ COMMITTED 还有其他影响 。在 MySQL 评估 WHERE 条件后，释放不匹配行的记录锁。对于 UPDATE 语句，InnoDB 进行“半一致”读取，这样它将最新提交的版本返回给 MySQL ，以便 MySQL 可以确定该行是否匹配 UPDATE 的 WHERE 条件

这可能也就是我在生产中只要使用 mysql 默认的`REPEATABLE READ`隔离级别就会疯狂死锁的原因

> 除非死锁的时候我会去 DEBUG 数据库的锁状态,其它情况我想操这个心 :)

建议无脑降低事务隔离级别至`READ COMMITTED`

乐观并发控制是要求每个客户端的后续读写都得依赖于此前查询所获得的的`ROW_VERSION`
比如事务 1`SELECT yi, ver FROM t`获得`(a,0)`
那么事务 1 后续的所有对该行的读写（ SELECT/UPDATE ）都得依赖于`ver=0`这个此前获得的事实
也就是对于读：事务 1 期望重新`SELECT yi, ver FROM t`获得的还是`(a,0)`，这叫做`REPEATABLE READ`（避免了幻读`phantom read`），注意 mysql 默认的事务隔离级别就是`REPEATABLE READ`所以默认是已经保证了在同一事务内不断重新执行`SELECT yi, ver FROM t`返回的永远都是`(a,0)`
对于写：事务 1 期望`UPDATE t SET 某个其他字段 = 某值 WHERE yi = a AND ver = 0`所返回的`affected rows`是 1 行，而如果不是 1 行而是 0 行就意味着表 t 中已经不存在符合约束`WHERE yi = a AND ver = 0`的行，也就是说行`yi=a`已经被其他事务修改了

建议参考以某企业级 orm EFCore 为背景的 MSDN 微软谜语： https://learn.microsoft.com/en-us/ef/core/saving/concurrency
以及我之前对于类似的场景（但是是 INSERT-only 而不是 UPDATE ）使用了数据库层提供的悲观并发控制，毕竟在 SQL 末尾加`FOR UPDATE`可比在 mysql 里用 TRIGGER 模拟单调递增的自增 sql server 的 ROW_VERSION 类型然后在程序业务逻辑里写乐观控制所带来的一大堆 if 简单多了： https://www.v2ex.com/t/908047

1. 那会在有许多线程并行执行事务时造成“饥饿”（ starvation ），因为靠后创建的线程获取不到任何没被锁的行导致其无事可做
2. 如果事务执行失败或者发生其他异常了也会释放锁（ try finally ）。而且为了保险我还加了锁超时，其他线程获取锁时会忽略已经连续锁了 5 分钟的行（数据库不会真卡到 INSERT 一些行都卡上几分钟吧）

> 这些是基于单体服务的设计

哪怕再怎么分布式（实际上同一个进程里的多线程也已经是分布式了所以才会有这些并行竞争问题），不论是无主还是有主的分布式网络，最终都得往一个单点（接受 INSERT 写操作的单主数据库网络）上竞争资源（插入什么行），那这就需要协调来避免竟态（同时插入导致表里存了重复行，但这会被数据库本身的 UNIQUE 约束给拦截所以导致各个线程收到 duplicate entry 错误，但我又不希望某个线程只是因为插入的一大批行中有一些已经存在了就导致整个事务回滚），除非能让分布式数据库也变成多主网络（ apache hadoop 那样），那每个任务节点就可以只向固定的数据库节点 INSERT （但仍然需要在接受 INSERT 写操作的所有数据库节点之间不断同步数据从而保证不会有相同 key 但 value 却不同的记录，也就是保证数据一致性）

> 我不喜欢用`SELECT ... FOR UPDATE`,它通常会让我其它功能查询也被无辜的阻塞

根据 https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks 的那张表格
事务 A 中的`SELECT ... FOR UPDATE`所产生的 IX 锁（有意排他锁）的确会导致事务 B 的普通的`SELECT`所产生的 S 锁（非有意共享锁）被挂起并一直等待 IX 锁释放（直到`innodb_lock_wait_timeout`）
但这表格也暗示了事务 A 的 IX 锁不会导致另一个事务 C 的`SELECT ... FOR SHARE`所产生的 IS 锁被挂起（`Compatible`），尽管我也不知道这会在哪个事务隔离级别下成立（可能是`READ COMMITTED`），但阁下的确可以亲自试试看

#12 @shade 用 bitmask https://dev.mysql.com/doc/refman/8.0/en/set.html
正好 /t/909074#r_12585697 里讨论过了

typo：最小复习->最小复现 https://en.wikipedia.org/wiki/Minimal_reproducible_example

让我们回到阁下的问题本身：

> 可能是我脑子出问题了。不知道为什么会说错。也可能是我写的时候脑子里想的是另外一种计算所有内容两两之间的相似度的方法吧。其实用我上面的 SQL 执行一次就能获得某个 content 的按某种相似度排序的所有的 content ，所以用不着 content 两两之间计算相似度，对所有 content 执行一遍那个带 IN 的语句就行了。感觉这个错误实在是太神奇了。想不到什么避免的方法。

这的相似度是指某两个 content 所属的 tags 集合之间的相似度吗？也就是朴素的推荐算法实现中使用的聚类推荐？
那么阁下可以违反一下 1NF （此前 https://www.v2ex.com/t/908231#r_12571811 提到的）将每 content 的 tags 集合变成一个 https://en.wikipedia.org/wiki/Bit_array 然后两两配对对所有 content 的这个转变为了 bitmask 的 tags 集合计算编辑距离（如 hamming/levenshtein 距离） https://github.com/Starry-OvO/aiotieba/pull/63 ，
那么对于每 content 的 tags bitmask ，存在其他 content 的 tags bitmask ，如果他们之间的编辑距离足够小（如<10 ），就认定这两个 content 具有相似的 tags ，就可以放进推荐 feed 流中

> 我感觉你这个说法很像嘲讽了，应该不只是我过于敏感。

https://www.v2ex.com/t/908083#r_12566585 对此早有预言：
建议深入学习贯彻泛银河系格雷科技分部邪恶组织四叶重工炼铜本部叶独头子叶独群组联合体陈意志第三帝国元首炼铜傻狗橙猫领导下的四叶 TG 本部（ https://t.me/n0099_tg https://t.me/n0099official ）话语体系文风：
https://sora.ink/archives/1574
https://github.com/n0099/TiebaMonitor/issues/24
https://github.com/Starry-OvO/aiotieba/issues/64

> 很有启发性，刷新我的认知了

一个 现实生活中十分常见的 xyproblem 场景 就能再一次刷新阁下的`认知`了？
那如果阁下去复习哲学基础之形式逻辑呢： https://sora.ink/archives/1502
https://n0099.net/v/d/1509
https://lasm.dev/2022/11/15/2022-11-15/
https://lasm.dev/2022/10/18/2022-10-18/
再来丶左右互搏：
https://n0099.net/v/d/3236
https://lasm.dev/2021/11/22/self/

而 v2 最近的某热门主题帖中也有人对此提出概括 https://www.v2ex.com/t/908787#r_12577024：
> 看到 v 友们的回复，我竟然一时不知道，是 v 友们的世界观太容易被改变了，还是 v 友们根本不知道世界观是个什么东西。。。 （ v 友们个个人中龙凤，怎么可能不知道呢。。。）

> 因为我问问题的时候偶尔会带上自己的目的。另外我也是对自己的目的有点不想公开

阁下并不需要透露阁下到底在做什么项目：
> 网站的具体细节我就不说了。由于网站还没开始做，我想尽量保密。你要是感兴趣的话我可以把原型和计划书单独发给你。其实我这个东西我给很多人都看过了，好像很多人都说从商业角度看不靠谱。所以不出意外我只能用业余时间自己做这个网站了。我对我设想的网站实在是太痴迷了，不做出来难受。

因为您最初的目的就是优化这两个 sql 而已，那您提问时为什么不最小化问题？
去给大型开源项目提 issue 或 stackoverflow 提问如果问题复杂也是直接附带一个最小复习
而不是把您的项目业务逻辑细节事无巨细的告诉纯路人（而且您也不想说），然后纯路人反而被您说的一大堆给整晕了就又陷入了 xyproblem

> 还有有时候 @他他才会管。希望别被站长看到。不过尽量还是别 @他

回顾经典之某位信安带手子纯路人指出了一个 xss 漏洞然后 livid 直接彻底解决了造成问题的功能（而不是尝试修复他）： https://www.v2ex.com/t/908287#r_12567114
我注册 v2 也有 4 年半了（ 2018-06-19 ），但此前都是从 google 检索结果里点击 v2 看看从来没发过贴所以我的确不知道 v2 啥环境，现在看来跟简中互联网平均水平如隔壁 hostloc 主机佬们差不多

> filtered 一直都是 100 。

https://dba.stackexchange.com/questions/164251/what-is-the-meaning-of-filtered-in-mysql-explain

> 狂暴轰入大量垃圾数据 > 执行带 in 查询 10 毫秒左右 > 重启 MariaDB > 执行带 in 查询 1 秒多 > 执行 ANALYZE TABLE tag_content_rel; > 执行带 in 查询 10 毫秒左右 > 之后不管重启多少次执行那条带 in 的都是 10 毫秒左右。

您应该先 OPTIMIZE 再 ANALYZE ，对于 innodb table engine 的表 OPTIMIZE 就是完全重建表，这样可以压缩掉您之前轰入 INSERT 时产生的 page 空洞从而稍微减低 io 这些 page 的耗时

> 比如说每隔一个月所有 content 都两两执行一次那个带 IN 的语句，前提是用户允许

您为啥要两两配对执行？为了缓存某个 content 的所有 tag ？但现查也只要 10ms 那您为什么要去套个缓存？说不定您的缓存比 10ms 还慢
而且为了获知所有 content-tag 关系（而不是需要哪个 content/tag 就去现查）您也只需要去掉 WHERE 子句直接`SELECT * FROM content_tag_rel`，所以哪来的两两配对？

> “内容”关联的“标签”是可能一直变的

content_tag_rel 表当然可以变，没人规定他必须是 immutable 的

> 我对我设想的网站实在是太痴迷了，不做出来难受。

我此前于 https://www.v2ex.com/t/908231#r_12566573 还以为阁下的程序是单纯的直接使用现成的开源 /闭源项目，所以阁下难以 /无法去修改其内部结构，所以才要想到在程序外部从数据库甚至服务器层面隔离 sql 来缓解问题
我错怪您了

> 在知乎上问问题问多了习惯被回答者嘲讽了

您可以先把问题说清楚，最开始您发的 /t/908231 里您不也没说您具体要优化的 sql 和表结构是什么样吗，后来我追问您才发
结果这几天看下来就是 对于 WHERE tag_id 少加了一个包含 tag_id 的索引
以及忘了 OPTIMIZE/ANALYZE TABLE 这两个小问题而已

其实阁下就是陷入了 https://xyproblem.info 之中：
> Q：如何使用 X 来做 Y ？
> A：如果你想做 Y ，你应该问这个问题，不要预先假设使用可能不合适的方法。这种形式的问题通常表明一个人不仅不了解 X ，而且对他们正在解决的问题 Y 感到困惑，并且过于关注他们特定情况的细节。

> 不过 V2EX 的站长好像对礼貌问题的容忍度挺低的，如果不想被处罚还是小心点吧

与此同时隔壁某主题帖 /t/909154 中一大堆阴阳怪气的杠精和低级红高级黑反串都没人管，却要封我一个纯路人？

同样是云厂商扫描硬盘 /静态存储（下称存储）上的文件，建议根据方式做区分：程序化和非程序化的
- 程序化：云厂商写了一些程序来定期 /按事件扫描某些 /全部存储中的文件，当程序遇到某些特征（如文件名 文件内容 hash fs 元数据）时将他的存在记录下来，然后可能会有打点上报 给安全部门作为汇总统计 和 /或 给 end user 发送通知告诉他发现存储了符合特征的文件
- 非程序化：云厂商的人员受到某些命令或是他们闲的打开了存储空间上的某些 /全部文件，人肉操作在其中达成他需要的目的（比如找到什么 log 作为证据提交）

当然直接这样一刀切同样是二极管行径，非程序化的人工法务调查也可以掩盖在 agent 那样的程序化流程之下，反之亦然

所以从（云厂商的）合规性的角度讲应该是尽可能避免非程序化的扫描，而是将这些需求都写成固定程序来执行
还要给用户提供选择性，比如在创建实例时加个默认选择的复选框允许他 optout 不安装 agent
当然即便 不安装 agent 启用硬盘加密 也无法对厂商掩盖什么，只是对厂商而已扫描时更麻烦一些（那他就可能只出于法律目的而扫描了）

据我所知国外云厂商的客服您主动给他 ssh 登录方式请他来解决疑似厂商造成的诡异问题他们都不敢来登录并要求您撤回，因为处理工单的客服的也没那个权限去操作用户托管数据
但即便您订阅了厂商的`高级`支持服务那群工程师同样不敢亲自登录您服进行调查，工程师们也只能在他们熟悉的厂商那一套技术栈上协助调查您遇到的疑似厂商造成的问题