MySQL:如何实现行级事务锁定而不是表锁定 [英] MySQL: How to achieve row-level transaction locking instead of table locking

问题描述

这是用例:

我有一张桌子，上面有一堆可用的或不可用的唯一代码.作为事务的一部分，我想从表中选择一个可用的代码，然后稍后在事务中更新该行.由于这可以同时在许多会话中同时发生，因此我想理想地选择一个随机记录并在表上使用行级锁定，以便其他事务不会被从中选择行的查询所阻塞.桌子.

I have a table with a bunch of unique codes which are either available or not available. As part of a transaction, I want to select a code that is available from the table, then later update that row later in the transaction. Since this can happen concurrently for a lot of sessions at the same time, I want to ideally select a random record and use row-level locking on the table, so that other transactions aren't blocked by the query which is selecting a row from the table.

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

I am using InnoDB for the storage engine, and my query looks something like this:

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

但是，它不仅锁定了表中的一行，而且最终锁定了整个表.谁能给我一些如何做到这一点的指针，以使该查询不会只锁定整个表，而不会锁定整个表?

However, rather than locking just one row from the table, it ends up locking the whole table. Can anyone give me some pointers on how to make it so that this query doesn't lock the whole table but just the row?

更新

附录:通过在我的选择中指定显式键而不是执行rand()，我能够实现行级锁定.当我的查询如下:

Addendum: I was able to achieve row-level locking by specifying an explicit key in my select rather than doing the rand(). When my queries look like this:

查询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

但是，这并不能真正解决问题.

However, that doesn't really help solve the problem.

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

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

Given that rand() has some issues in MySQL, the strategy I chose is:

1. 我在可用= 1的地方选择了50个代码ID，然后在应用程序层中对数组进行混洗以向订单添加随机级别.

1. I select 50 code id's where available = 1, then I shuffle the array in the application layer to add a level of randomness to the order.

从tbl_codes中选择ID(如果可用= 1个限制50)

select id from tbl_codes where available = 1 limit 50

我开始循环地从混洗后的数组中弹出代码，直到能够选择一个带有锁的代码为止

I start popping codes from my shuffled array in a loop until I am able to select one with a lock

从tbl_codes中选择*，如果可用= 1并且id =:id

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

推荐答案

研究一下MySQL如何实际执行此查询可能会很有用:

It may be useful to look at how this query is actually executed by MySQL:

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

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

This will read and sort all rows that match the WHERE condition, generate a random number using rand() into a virtual column for each row, sort all rows (in a temporary table) based on that virtual column, and then return rows to the client from the sorted set until the LIMIT is reached (in this case just one). The FOR UPDATE affects locking done by the entire statement while it is executing, and as such the clause is applied as rows are read within InnoDB, not as they are returned to the client.

撇开上述明显的性能含义(这很糟糕)，您永远不会从中获得合理的锁定行为.

Putting aside the obvious performance implications of the above (it's terrible), you're never going to get reasonable locking behavior from it.

简短答案:

1. 使用 RAND()或您喜欢的任何其他策略选择所需的行，以找到该行的 PRIMARY KEY 值.例如:从tbl_codes中选择SELECT ID(如果可用)= 1 ORDER BY rand()LIMIT 1
2. 仅使用其 PRIMARY KEY 锁定所需的行.例如: SELECT * FROM tbl_codes WHERE id = N

1. Select the row you want, using RAND() or any other strategy you like, in order to find the PRIMARY KEY value of that row. E.g.: SELECT id FROM tbl_codes WHERE available = 1 ORDER BY rand() LIMIT 1
2. Lock the row you want using its PRIMARY KEY only. E.g.: SELECT * FROM tbl_codes WHERE id = N

希望这会有所帮助.

这篇关于MySQL:如何实现行级事务锁定而不是表锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！