MySQL - 两个并发进程的一致选择和更新

Question

我有一个程序任务来执行表的一致更新。为简单起见，我将讨论虚拟表只是为了展示这个想法。所以我有两个表:user 和 note。

note有一个id(主键自增)和user_id(外键)，number和 next_number。
number 是用户备注的编号。 next_number 可以等于 0 如果这是最新的用户笔记或者可以等于下一个用户笔记的编号(就像 C++ 链表中指向下一个节点的指针) .
所以每个用户都有从 1 到 n 编号的笔记。

现在有两个进程(Proc1 和Proc2)需要为同一用户将行插入表note。

所以每个进程都开始一个事务，检索最新用户笔记的 number，然后执行一些其他操作并插入新行，number 递增到 1。

这两个进程(Proc1 和 Proc2)是否有可能在它们的事务中检索相同的编号(假设第一个进程将在第二个进程执行其 INSERT 和 UPDATE 语句之前执行 SELECT 语句)？

例如:

1. Proc1:SELECT number FROM note WHERE user_id = 1 AND next_number = 0 - 检索到的数字 5
2. Proc2:SELECT number FROM note WHERE user_id = 1 AND next_number = 0 - 检索到的数字 5
3. Proc1:INSERT INTO note SET number = 6，next_number = 0，user_id = 1，text = 'Proc1 note'
4. Proc2:INSERT INTO note SET number = 6，next_number = 0，user_id = 1，text = 'Proc2 note'
5. Proc1:UPDATE note SET next_number = 6 WHERE number = 5
6. Proc2:UPDATE note SET next_number = 6 WHERE number = 5
7. Proc1:提交Proc1的事务
8. Proc2:提交Proc2的事务

如果可能，如何避免这种情况？请问SELECT ... FOR UPDATE解决问题？

更新 - 简化我的问题

所以一般的问题是如果进程启动了一个事务，如何禁止其他 MySQL session 与当前事务正在处理的行进行交互？

Answer 1

您应该对此进行测试以确保它在做正确的事情，但是第二个 SELECT ... LOCK IN SHARE MODE 语句应该阻塞直到第一个语句完成。您还应该查看隐式设置的 SERIALIZABLE 事务隔离级别。