如何正确避免Mysql竞态条件 [英] How to properly avoid Mysql Race Conditions

问题描述

我知道这之前已被问过，但我仍然困惑，如果可能的话，我想避免任何问题。

I know this has been asked before, but I'm still confused and would like to avoid any problems before I go into programming if possible.

我打算具有内部网站，在任何给定时间至少有100个用户活跃。用户将发布一个项目（插入到db中，值为0），该项目将通过php站点（db查询）显示。然后，用户可以选择按下按钮并将该项目锁定为自己的项目（将该项目的值指定为自己的ID）

I plan on having an internal website with at least 100 users active at any given time. Users would post an item (inserted into db with a 0 as its value) and that item would be shown via a php site (db query). Users then get the option to press a button and lock that item as theirs (assign the value of that item as their id)

如何确保2个或更多用户不要同时检索相同的项目。我知道在编程类似c ++我只使用纯ol互斥锁。是他们的一个等价的在mysql它将锁定只有一个项目条目吗？我看到过对LOCK_TABLES和GET_LOCK的引用，还有很多其他人，所以我对于最好的东西还是很困惑。

How do I ensure that 2 or more users don't retrieve the same item at the same time. I know in programming like c++ I would just use plain ol mutex lock. Is their an equivalent in mysql where it will lock just one item entry like that? I've seen references to LOCK_TABLES and GET_LOCK and many others so I'm still very confused on what would be best.

很多人都有潜力一个按钮，这将是灾难性的，如果多个人得到一个确认。

There is potential for many people all racing to press that one button and it would be disastrous if multiple people get a confirmation.

我知道这是一个竞争条件的主要例子，但是mysql对我来说是外国领域。

I know this is a prime example of a race condition, but mysql is foreign territory for me.

我显然会在更新之前查询项目的值，并确保它没有写入，但是什么是最好的方式来确保避免这种竞争条件。

I obviously will query the value of the item before I update it and make sure it hasn't written, but what is the best way to ensure that this race condition is avoided.

提前感谢。

推荐答案

要实现这一点，您需要锁定记录。
添加默认为0的列LockedBy。

To achieve this, you will need to lock the record somehow. Add a column LockedBy defaulting to 0.

当有人按下按钮时执行类似于下面的查询：

When someone pushes the button execute a query resembling this:

UPDATE表SET LockedBy = WHERE LockedBy = 0和id =;

UPDATE table SET LockedBy= WHERE LockedBy=0 and id=;

更新后验证受影响的行（在php mysql_affected_rows中）。如果值为0，表示查询未更新任何内容，因为LockedBy列不为0，因此被其他人锁定。

After the update verify the affected rows (in php mysql_affected_rows). If the value is 0 it means the query did not update anything because the LockedBy column is not 0 and thus locked by someone else.

希望这有助于

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