Is join insert/update on MySQL an atomic operation?

Question

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)

Answer 1

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.

Answer 2

No. Unless you wrap them in START TRANSACTION like this

START TRANSACTION;
   SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
   UPDATE table2 SET summary=@A WHERE type=1; 
COMMIT;

Example from Mysql manual