MySQL中的并发更新 [英] concurrent UPDATEs in MySQL

问题描述

我有一个PHP脚本调用以下MySQL查询：

I have a PHP script that calls the following MySQL query:

UPDATE table1
SET column1=1
WHERE column1=0

然后PHP脚本返回 mysqli_affected_rows 。

应该只可能获得一次阳性结果，因为没有行会出现 column1 = 0 执行此脚本后。

There should be possible to get a positive result only once, since there would be no row with column1=0 after this script has been executed.

我的问题是：如果此脚本并发执行多次，理论上是否有可能多次获得正面结果？

My question is: Would it be theoretically possible to get a positive result more than once if this script is executed many times concurrency?

推荐答案

这取决于用于表的数据库引擎。

It depends on the database engine used for the table.

如果您使用的是MyISAM，在执行查询时将锁定整个表。其他尝试访问该表的查询将被阻止，直到完成。它们运行时，将没有匹配条件的行。因此，其中只有一个将执行任何更新。

If you're using MyISAM, it will lock the whole table while it's performing the query. Other queries that try to access the table will be blocked until it's done. When they run, there will be no rows that match the condition. So only one of them will perform any updates.

如果您使用的是InnoDB，它将锁定要更新的各个行。不同的并发查询可以更新表的不同行，因此它们各自将获得非零结果。

If you're using InnoDB, it locks the individual rows that it updates. Different concurrent queries can update different rows of the table, so they'll each get non-zero results.

这篇关于MySQL中的并发更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！