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

Question

下面是用例：

我有一张表，里面有一堆唯一的代码，有的可用，有的没有。作为事务的一部分，我希望从表中选择可用的代码，然后在事务的后面更新该行。由于这可以同时发生在许多会话中，所以我希望在理想情况下选择一个随机记录，并在表上使用行级锁定，这样其他事务就不会被从表中选择行的查询阻塞。

我使用InnoDB作为存储引擎，我的查询如下所示：

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

但是，它不是只从表中锁定一行，而是锁定整个表。有人能给我一些提示，说明如何使它不锁定整个表，而只锁定行吗？

更新

增编:我能够通过在select中指定一个显式键来实现行级锁定，而不是执行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's‘select = 1，然后对应用层中的数组进行洗牌，以向顺序添加一定程度的随机性。 从tbl_codes选择id，其中可用的为1，限制为50
2. 我开始从循环中的杂乱数组中弹出代码，直到我能够选择一个带锁的数组。 从可用的tbl_codes中选择*，其中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

希望这能帮上忙。

Answer 2

即使不是完全映射到您的问题，这个问题在这里也有一定的讨论：row/

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

这篇文章不涉及锁。但是，MySQL可能会锁定所有具有available = 1的行，直到事务结束才会释放它们！

那篇文章提出了一些解决方案，似乎没有一个对你有好处，除了这个，不幸的是，非常麻烦，我没有探讨它的正确性。

从table选择*，其中id >= (从table选择地板(最大值( id )* RAND())顺序按id限制1；

这是我能为您做的最好的，因为我没有命令MySQL内部。而且，这篇文章很旧。