MySQL：如何实现行级事务锁定而不是表锁定

Question

这是一个使用案例：

我有一张表格，其中包含一堆状态为“可用”或“不可用”的唯一代码。作为交易的一部分，我想从表格中选择一个可用的代码，然后稍后在交易中更新该行。由于这可能会同时发生在许多会话中，我希望最好随机选择一条记录，并在表格上使用行级锁定，以便其他事务不会被查询阻止。

我正在使用InnoDB作为存储引擎，我的查询大致如下：

select * from tbl_codes where available = 1 order by rand() limit 1 for update

然而，与其仅锁定表中的一行，它最终锁定整个表。有没有人能给我一些指导，如何使此查询不锁定整个表而仅锁定行？ 更新 补充说明：通过在我的选择中指定显式键而不是执行rand()，我能够实现行级锁定。当我的查询看起来像这样时：

   select * from tbl_codes where available = 1 and id=5 limit 1 for update

查询2：

   select * from tbl_codes where available = 1 and id=10 limit 1 for update

然而，这并不能真正帮助解决问题。

附录2：我采用的最终解决方案

考虑到MySQL中rand()存在一些问题，我选择的策略是：

1. 我选择50个可用状态码id，然后在应用程序层对数组进行洗牌，以增加顺序的随机性。

   select id from tbl_codes where available = 1 limit 50

2. 我开始在循环中从我的洗牌数组中弹出代码，直到我能够选择一个带锁定的代码为止。

   select * from tbl_codes where available = 1 and id = :id

- Eugene

---

你确定它最终会锁定整个表吗？你如何检测到这一点？ - gd1

简单来说，我有两个客户端会话打开，在每个会话中都开始了一个事务，在第一个会话中运行这个查询，然后在第二个会话中运行它，但它会锁定第二个会话，而不是继续进行。 - Eugene

附加说明：我成功地实现了行级锁定，通过在我的 select 中指定一个显式 key 而不是使用 rand()。当我的查询看起来像这样时：

1. select * from tbl_codes where available = 1 and id=5 limit 1 for update
2. select * from tbl_codes where available = 1 and id=10 limit 1 for update

然而，这并不能真正解决问题。 - Eugene

另一种在选择时获取随机性的方法是向查询添加随机偏移量，其中随机偏移量在应用程序层生成。例如：select id from tbl_codes where available = 1 LIMIT :offset, 50 当您有许多代码可供选择时，这种方法效果很好。 - Jay

Answer 1

可能有益于查看MySQL实际执行此查询的方式：

select * from tbl_codes where available = 1 order by rand() limit 1 for update

这将读取和排序所有符合WHERE条件的行，使用rand()为每行生成一个虚拟列中的随机数，基于该虚拟列对所有行（在临时表中）进行排序，然后从排序集合中向客户端返回行，直到达到LIMIT（在这种情况下只有一个）。FOR UPDATE影响整个语句执行时所做的锁定，因此该子句适用于在InnoDB中读取行时，而不是在将它们返回给客户端时应用。

暂且不论上述方法的性能影响（它很糟糕），你永远无法从中获得合理的锁定行为。

简短回答：

1. 选择要查询的行，使用RAND()或任何其他策略，以找到该行的PRIMARY KEY值。例如：SELECT id FROM tbl_codes WHERE available = 1 ORDER BY rand() LIMIT 1
2. 仅使用其PRIMARY KEY锁定要查询的行。例如：SELECT * FROM tbl_codes WHERE id = N

希望这有所帮助。

- jeremycole

---

问题在于，在您的点1和点2之间，行可能变得不可用。也许他可以在1和2之间循环，直到他可以锁定一个可用的行，而2应该变成“SELECT * FROM tbl_codes WHERE id = N and available = 1”，即使看起来是不必要的。 - gd1

gd1 - 这正是我想要做的，谢谢 jeremycole/gd1。 - Eugene

Answer 2

即使与您的问题不完全匹配，该问题在此处得到了一些讨论：http://akinas.com/pages/en/blog/mysql_random_row/

这种方法的问题在于它非常慢。它之所以如此缓慢，是因为MySQL创建一个临时表，其中包含所有结果行，并为每个结果行分配一个随机排序索引。然后对结果进行排序并返回。

该文章没有涉及锁定。但是，也许MySQL会锁定所有具有 available = 1 的行，并在事务结束之前不释放它们！

该文章提出了一些解决方案，但除了这个非常hacky的解决方案外，其他解决方案似乎都不适合您，而我也没有验证其正确性。

SELECT * FROM table WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM table ) ORDER BY id LIMIT 1;

这是我能为您做的最好的事情，因为我不熟悉MySQL内部。此外，该文章相当古老。

- gd1

---

嗯，是的，我怀疑这可能与rand()或“available”范围有关。你解决方案中查询的问题是有时会命中一个不可用的记录，导致根本没有返回任何行。 - Eugene

我明白了，你是对的。我能想到的唯一解决方案（但代价很高！）是将表分区（例如，将可用和不可用的分开），以便黑客攻击可以起作用。但我认为这将意味着太多的更改。 - gd1

阅读Jeremycole的解决方案以及我的评论。 - gd1

是的，我正在考虑类似的东西...也许设置范围之类的东西，例如添加类似于“where id> 100且<200”的条件，然后将它们错开，并跟踪哪些范围仍有可用的 ID。 - Eugene