如何避免MySQL中的竞争条件 [英] How to avoid race condition in MySQL

问题描述

我正在开发的应用程序中存在潜在的竞争状况，我想在查询中考虑和避免这种情况.

I've got a potential race condition in an application I'm developing, which I'd like to account for and avoid in my querying.

总结应用程序流程...

To summarise the application flow...

1. 在entries表中创建新行:

INSERT INTO entries ( name, email ) VALUES ( 'Foo Bar', '[e-mail]' );

通过检查对时间敏感的prizes表来确定Bar先生是否是获胜者:

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

SELECT id FROM prizes WHERE various_time_conditions = 'met' AND id NOT IN ( SELECT prize_id FROM entries );

如果他是获胜者，请相应地更新他的条目行:

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

UPDATE entries 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语法，但是我对哪种最适合我的解决方案感到困惑. /p>

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:

SELECT id FROM prizes WHERE ... AND available = 1 FOR UPDATE

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

Then set the availability if you do assign the prize:

UPDATE prizes SET available = 0 WHERE id = ...

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

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

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

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.

这篇关于如何避免MySQL中的竞争条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！