mysql - 使用 SQL 分配资源(行)

Question

我正在使用 mariadb。有一个resource表，其中包含id列、used标志和其他资源描述符。每一行代表一个资源分配单元。我在分配行时遇到问题。

在每笔交易中，我想要:

1. 选择并锁定未使用的行。
2. 将used设置为true。
3. 根据所选行操作数据库。
4. 然后提交。

要求:

• 可以有多个并发事务。
• 它可以选择任何未使用的行。
• 它不应该阻塞。
• 如果步骤 3 失败，事务将回滚并重新启动。最好可以选择不同的行。

首先，我尝试了SELECT * FROM resource WHEREused=0 LIMIT 1 FOR UPDATE，但在并发情况下，第二个事务将阻塞，直到第一个事务结束。

START TRANSACTION
SELECT * FROM resource WHERE used=0 LIMIT 1 FOR UPDATE    <-- second tx waits here until first commits
...
COMMIT

然后我尝试通过 ORDER BY rand() 添加随机性。但似乎这会首先对整个表进行排序，所以仍然会阻塞。

START TRANSACTION
SELECT * FROM resource WHERE used=0 ORDER BY rand() LIMIT 1 FOR UPDATE    <-- still blocks
...
COMMIT

有什么提示吗？

Answer 1

我假设您的处理将花费相当长的时间，或者您有大量并发事务 - 否则，接受阻塞可能会更容易。

我过去通过更精细的“已使用”状态解决了这个问题。

伪代码:

begin transaction
select the row to process, set status to "in progress", and mark with unique process identifier
end transaction

begin transaction
select the row with "in progress" and my unique process identifier
complete other processing logic
set row status to "used"
if error:
   rollback transaction
   set flag to "error"
else
  commit transaction

这仍然会阻塞表格，但只持续了不到一秒。

我们有一项单独的工作来查找“放弃”的交易(带有“错误”标志的记录，并将这些记录报告给管理控制台)。