MySQL - 避免在 repeatable_read 上选择和更新相同的记录 [英] MySQL - Avoid to select, and update, the same record on repeatable_read

问题描述

我正在开发一个需要从 MySQL 表中获取唯一令牌的 PHP 服务.表是这样的:

i'm developping a PHP service that need to get a unique TOKEN from a MySQL table. The table is like:

ID    TOKEN   ID_PROCESS
1     AAAAA   0
2     BBBBB   0
3     CCCCC   0

该表有数百万条已生成 TOKEN 的记录.

The table has millions of records with already generated TOKEN.

我需要特别确定，例如，ID = 1 的记录被选择然后更新(UPDATE table set ID_PROCESS = 123 WHERE ID = 1)来自一个用户，并且没有其他人可以选择它然后更新它覆盖了以前的更新.

I need to be extra sure that, for example, the record with ID = 1 is selected and then updated ( UPDATE table set ID_PROCESS = 123 WHERE ID = 1) from one user and that nobody else could select it and then update it overwriting the former update.

为了选择一个可用的令牌，我只需要:

For selecting an available TOKEN i just do:

SELECT FROM table WHERE ID_PROCESS = 0 ORDER BY RAND() LIMIT 1

如果两个并发会话选择了同一个记录，我就有麻烦了，如果我使用事务，隔离级别为 REPEATABLE_READ，我将看不到第一个更新.

If two concurrent sessions select the same record i'm in trouble, if i use transaction, with isolation level REPEATABLE_READ, i'm not going to see the first update.

如何在并发场景中实现安全选择可用的 TOKEN?

How can i achieve the security to pick an available TOKEN in a concurrent scenario?

感谢您的帮助！

推荐答案

最简单的解决方案是从那些没有分配进程的行中更新一行.

The easiest solution is to UPDATE a single row out of those that have no process assigned.

UPDATE mytable SET ID_PROCESS = 123 WHERE ID_PROCESS = 0 LIMIT 1;

然后快速提交以释放行锁，因为所有 ID_PROCESS = 0 的行将被锁定，直到您结束事务.

Then commit quickly to release the row locks, because all rows with ID_PROCESS = 0 will be locked until you end your transaction.

完成后，假设每个并发客户端使用不同的进程 ID，您可以选择刚刚更新的行.

Once that's done, assuming each concurrent client uses a distinct process id, you can select the row you just updated.

SELECT ... FROM table WHERE ID_PROCESS = 123;

注意这与事务隔离级别无关.这种锁定在 REPEATABLE READ、READ COMMITTED 和其他隔离级别中的行为方式相同.

Note this has nothing to do with transaction isolation level. This locking behaves the same way in REPEATABLE READ, READ COMMITTED, and other isolation levels.

这篇关于MySQL - 避免在 repeatable_read 上选择和更新相同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！