增加字段时如何确保MySQL数据库中没有竞争条件? [英] How to make sure there is no race condition in MySQL database when incrementing a field?

问题描述

当两个连接想要更新同一条记录时，如何防止 MySQL 数据库中的竞争条件?

How to prevent a race condition in MySQL database when two connections want to update the same record?

例如，连接 1 想要增加尝试"计数器.第二个连接也想做同样的事情.两个连接SELECT尝试"计数，增加值并且两个UPDATE尝试"增加值.突然间，尝试次数"只是尝试次数+1"而不是尝试次数+2"，因为两个连接都得到了相同的尝试次数"并加了 1.

For example, connection 1 wants to increase "tries" counter. And the second connection wants to do the same. Both connections SELECT the "tries" count, increase the value and both UPDATE "tries" with the increased value. Suddenly "tries" is only "tries+1" instead of being "tries+2", because both connections got the same "tries" and incremented it by one.

如何解决这个问题?

推荐答案

这里有 3 种不同的方法:

Here's 3 different approaches:

update table set tries=tries+1 where condition=value;

它将以原子方式完成.

如果您确实需要先选择值并在您的应用程序中更新它，您可能需要使用事务.这意味着您必须使用 InnoDB，而不是 MyISAM 表.您的查询将类似于:

If you do need to first select the value and update it in your application, you likely need to use transactions. That means you'll have to use InnoDB, not MyISAM tables. Your query would be something like:

BEGIN; //or any method in the API you use that starts a transaction
select tries from table where condition=value for update;
.. do application logic to add to `tries`
update table set tries=newvalue where condition=value;
END;

如果交易失败，您可能需要手动重试.

if the transaction fails, you might need to manually retry it.

一种常见的方法是在表中引入一个版本列.您的查询将执行以下操作:

A common approach is to introduce a version column in your table. Your queries would do something like:

select tries,version from table where condition=value;
.. do application logic, and remember the old version value.
update table set tries=newvalue,version=version + 1 where condition=value and version=oldversion;

如果该更新失败/返回受影响的 0 行，则其他人同时更新了该表.您必须从头开始 - 也就是说，选择新值，执行应用程序逻辑并再次尝试更新.

If that update fails/returns 0 rows affected, someone else has updated the table in the mean time. You have to start all over - that is, select the new values, do the application logic and try the update again.

这篇关于增加字段时如何确保MySQL数据库中没有竞争条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！