是在MySQL上插入/更新一个原子操作吗？ [英] Is join insert/update on MySQL an atomic operation?

问题描述

在Mysql数据库中，每个表基于InnoDB启用自动提交，将查询子查询和/或联接是原子吗？

范例：

• INSERT INTO用户SELECT（x，y，z）FROM users，comments WHERE users.id = comments.user_id; （join）




• UPDATE用户，注释SET users.x = x1 WHERE users.age> 30; （joins）




• UPDATE个用户，注释SET users.x = x1，comments.y = y1 WHERE users.age> 30; （joins）




• UPDATE个用户，注释SET users.x = x1，comments.y = y1 WHERE users.id IN（SELECT id FROM users WHERE age> 30）; （subqueries）

解决方案

我理解你的问题，这些查询本身是否是一个原子操作？然后答案是是。其他两个答案是正确的，当他们说，所有的语句一起不是原子。

数据库中的原子性 全部或全部。 不意味着数据的正确性。您的声明成功与否。它与连接或子查询无关。一个语句是一个语句，不管您的数据库是否必须在内存或磁盘上使用临时表。

事务只是告诉数据库将多个语句视为一个语句。当其中一个语句失败时，所有的语句都会回滚。

这里有一个重要的相关主题是隔离级别。

$ b $ p $ <$> b

没错。只要它是一个有效的语句，没有电源故障或其他原因为什么查询可能失败，它正在完成。原子性本身只是保证语句被执行或不被执行。它保证完整性，并且数据没有损坏（因为写操作没有完成或什么）。 它不保证数据的正确性。给定一个查询，例如 INSERT INTO foo SELECT MAX（id）+ 1 FROM bar; 请确保通过设置正确的隔离级别，您不会获得幻影读取或任何内容。

In a Mysql database with every table based on InnoDB with Autocommit enabled, will queries with subqueries and/or joins be atomic?

Examples:

• INSERT INTO users SELECT (x,y,z) FROM users, comments WHERE users.id = comments.user_id; (joins)

• UPDATE users, comments SET users.x = x1 WHERE users.age > 30; (joins)

• UPDATE users, comments SET users.x = x1, comments.y = y1 WHERE users.age > 30; (joins)

• UPDATE users, comments SET users.x = x1, comments.y = y1 WHERE users.id IN (SELECT id FROM users WHERE age > 30); (subqueries)

解决方案

I understand your question like "is each of those queries in itself an atomic operation?". Then the answer is "yes".The other two answers are right, when they say that all your statements together are not atomic.

Atomicity in databases only means all or nothing. It does not mean correctness of data. Your statement succeeds or not. It has nothing to do with joins or subqueries. One statement is one statement, no matter if your database has to use a temporary table in memory or on disk or not.

Transactions just tell your database to treat multiple statements as one statement. When one of the statements fails, all of them are rolled back.

An important related topic here is the isolation level. You might want to read up about those.

EDIT (to answer the comment):

That's right. As long as it is a valid statement and no power failure occurs or other reasons why a query could fail, it's being done. Atomicity in itself just guarantees that the statement(s) is/are being done or not. It guarantees completeness and that data is not corrupt (cause a write operation didn't finish or something). It does not guarantee you the correctness of data. Given a query like INSERT INTO foo SELECT MAX(id) + 1 FROM bar; you have to make sure via setting the correct isolation level, that you don't get phantom reads or anything.

这篇关于是在MySQL上插入/更新一个原子操作吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！