如何避免 MySQL 中的竞争条件

在 entries 表中创建一个新行:

插入条目(姓名，电子邮件)值('Foo Bar', 'foo@example');

通过查看时间敏感的奖品表来确定 Bar 先生是否是赢家:

Find out if Mr Bar is a winner by checking a time-sensitive prizes table:

SELECT id FROM Prizes WHERE different_time_conditions = 'met' AND id NOT IN (SELECT Prize_id FROM entries);

如果他是赢家，相应地更新他的条目行:

If he's a winner, update his entry row accordingly:

UPDATE 条目 SET Prize_id = [prize id] WHERE id = [entry id];

由于每个奖品只能发放一次，我需要消除竞争条件的任何可能性，即另一个进程可以查询奖品表并更新上述第 2 步和第 3 步之间的条目表.

As each prize can only be given out once, I need to eliminate any possibility of a race condition where another process can query the prizes table and update the entry table between steps 2 and 3 above.

我一直在做一些研究，发现了大量有关事务的信息(我所有的表都使用 InnoDB)并使用 MySQL 的 SELECT ... FOR UPDATE 语法，但我很困惑这是最适合我的解决方案.

I've been doing some research and have found a load of information about transactions (all my tables use InnoDB) and using MySQL's SELECT ... FOR UPDATE syntax but I'm confused as to which is the most suitable solution for me.

您将要锁定奖品记录.因此，如果您不打算使用winner_id 之类的东西，请在奖品表上添加一些可用性标志(可能具有默认值).像这样:

You're going to want to lock the prize record. So add some availability flag on the prizes table (perhaps with a default value) if you're not going to use something like a winner_id. Something like this:

然后设置可用性，如果您分配奖品:

Then set the availability if you do assign the prize:

当然，您需要将其包装在事务中.

You'll need to wrap this inside a transaction of course.

确保每次检查奖品是否可用时，将AND available = 1 FOR UPDATE添加到查询中，因为SELECT 没有 FOR UPDATE 不会等待锁.

Make sure that every time you check to see if the prize is available, you add AND available = 1 FOR UPDATE to the query because a SELECT without the FOR UPDATE is not going to wait for a lock.