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

Question

这是用例:

我有一张表格，其中包含一堆可用或不可用的唯一代码。作为事务的一部分，我想从表中选择一个可用的代码，然后稍后在事务中更新该行。由于这可能同时发生在很多 session 中，我想理想地选择一个随机记录并在表上使用行级锁定，这样其他事务就不会被从中选择行的查询阻塞 table 。

我正在使用 InnoDB 作为存储引擎，我的查询看起来像这样:

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

然而，它并没有锁定表中的一行，而是锁定了整个表。任何人都可以给我一些关于如何使这个查询不锁定整个表而只锁定行的指示吗？

更新

附录:我能够通过在我的选择中指定一个显式键而不是执行 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

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

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

鉴于rand()在MySQL中存在一些问题，我选择的策略是:

1. 我选择了 50 个代码 ID，其中 available = 1，然后我在应用程序层中打乱数组，为顺序添加一定程度的随机性。

   从可用的 tbl_codes 中选择 id = 1 限制 50

2. 我开始循环地从打乱的数组中弹出代码，直到我能够选择一个带锁的代码

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

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

希望对您有所帮助。