MyISAM和InnoDB中的单个mysql语句是原子的吗? [英] Are single mysql statements atomic in MyISAM and InnoDB?

问题描述

例如，我有一排带有C1 value = 'clean'列的行，并且两个不同的客户端在相同的时间上运行此查询:

update T1 set C1 = 'dirty' where Id = 1

在不使用交易的情况下，是否保证与引擎类型无关 ?对于一个客户端，mysql_affected_rows()的值将为1?对于另一客户端，0的值?

解决方案

是和否:-)

在两种情况下，访问都是序列化的(假设您是使用InnoDB之类的事务引擎)，因为它们排在同一行，因此它们不会互相干扰.换句话说，语句 是原子的.

但是，受影响的行数实际上取决于打开连接时的配置集. mysql_affected_rows()页面的意思是(我的粗体) :

对于UPDATE语句，默认情况下，受影响的行值为实际更改的行数.如果在连接mysqld时为mysql_real_connect()指定CLIENT_FOUND_ROWS标志，则受影响的行值为"found" 的行数；即与WHERE子句匹配.

并从 mysql_real_connect页面:

CLIENT_FOUND_ROWS:返回找到的(匹配)行的数量，而不是已更改的行的数量.

因此，就配置 CLIENT_FOUND_ROWS时发生的情况而言，受影响的行为:

UPDATE T1 SET C1 = 'dirty' WHERE id = 1

与无任何关系，仅与匹配的行有关.这两个查询均为1. >

另一方面，如果未设置CLIENT_FOUND_ROWS，则第二个查询实际上不会更改行(因为它已经填充了"dirty")，并且行数为零.

如果无论设置如何都想要 same 行为(仅显示更改)，则可以使用类似以下内容的东西:

UPDATE T1 SET C1 = 'dirty' WHERE id = 1 AND C1 <> 'dirty'

For example, I have a row with a column C1 value = 'clean', and two different clients run this query at the same time:

update T1 set C1 = 'dirty' where Id = 1

Without using transactions, is it guaranteed regardless of engine type that the value of mysql_affected_rows() would be 1 for one client and 0 for the other?

解决方案

Yes and No :-)

In both cases, the access is serialised (assuming you're using a transactional engine like InnoDB) since they hit the same row, so they won't interfere with each other. In other words, the statements are atomic.

However, the affected row count actually depends on your configuration set when you open the connection. The page for mysql_affected_rows() has this to say (my bold):

For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows "found"; that is, matched by the WHERE clause.

And from the mysql_real_connect page:

CLIENT_FOUND_ROWS: Return the number of found (matched) rows, not the number of changed rows.

So, in terms of what happens with CLIENT_FOUND_ROWS being configured, the affected rows for:

UPDATE T1 SET C1 = 'dirty' WHERE id = 1

have nothing to do with whether the data is changed, only what rows matched. This would be 1 for both queries.

On the other hand, if CLIENT_FOUND_ROWS was not set, the second query would not actually be changing the row (since it's already populated with 'dirty') and would have a row count of zero.

If you wanted the same behaviour regardless of that setting (only showing changes), you could use something like:

UPDATE T1 SET C1 = 'dirty' WHERE id = 1 AND C1 <> 'dirty'

这篇关于MyISAM和InnoDB中的单个mysql语句是原子的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！