Are mysql multiple inserts in the same query atom?

Question

I am doing several attachments in one :

INSERT INTO table (c1, c2) VALUES (1,2), (2,3), (3,4),...... ON DUPLICATE KEY UPDATE c2 = VALUES(c2) 

Now suppose the query indicates more than tens of thousands of VALUES values ​​(hence ellipsis) ....

Could there ever be a case when some parts of VALUES managed to get up / updated in the database, but the rest did not insert / update, possibly due to some kind of error or malfunction of db-memory? etc.?

Mysql queries ALL or Nothing?

Is it true that for every mysql query executed, all the values ​​specified in the query will be inserted / updated smoothly or none of the values ​​will be inserted / updated?

Answer 1

ACIDs (Atomicity, Consistency, Isolation, Durability properties) are used to describe this behavior in databases. Atomicity is important only if we are dealing with parallel changes . To ensure consistency, a certain level of isolation must be achieved. However, more isolated transactions with multiple transactions have lower performance than a DBMS usually does. Thus, there is a so-called "isolation level , which says which errors can occur in the DBMS, and which are not.

Now MySQL implements all isolation levels in the INNODB databases, and you can choose for each transaction: https://dev.mysql.com/doc/refman/5.1/en/set-transaction.html

MyIsam databases do not support transactions, however, individual operations must be performed atomically. (Source: https://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html ). Please note, however, that this does NOT guarantee that the data will not be changed between reads and writes in one operation - atomicity in terms of the DBMS means that the operation is completely or completely skipped. It does NOT guarantee isolation, consistency, or durability.

Answer 2

"Could there ever be a case when some parts of VALUES managed to get up / updated in the database, but the rest did not insert / update, possibly due to some kind of db / fail / memory-running- error, etc. .?

Late answer, but perhaps interesting: [ON DUPLICATE KEY] UPDATE not strictly atomic for single strings (neither for MyISAM nor for InnoDB ), but it will be atomic with respect to errors.

What's the difference? This illustrates a potential problem with strict atomicity:

 CREATE TABLE `updateTest` ( `bar` INT(11) NOT NULL, `foo` INT(11) NOT NULL, `baz` INT(11) NOT NULL, `boom` INT(11) NOT NULL, PRIMARY KEY (`bar`) ) COMMENT='Testing' ENGINE=MyISAM; INSERT INTO `updateTest` (`bar`, `foo`, `baz`, `boom`) VALUES (47, 1, 450, 2); INSERT `updateTest` (`bar`, `foo`, `baz`, `boom`) VALUES (47, 0, 400, 5) ON DUPLICATE KEY UPDATE `foo` = IF(`foo` = 1, VALUES(`foo`), `foo`), `baz` = IF(`foo` = 1, VALUES(`baz`), `baz`), `boom` = IF(`foo` = 1, VALUES(`boom`), `boom`); 

(47, 1, 450, 2) will turn into (47, 0, 450, 2) , not (47, 0, 400, 5) . If you assume strict atomicity (which does not mean that you should, you may prefer this behavior), this should not be the case - foo will certainly not change before other column values ​​are evaluated. foo should change along with the other columns - all or nothing.

If I talk about atoms in relation to errors, I mean that if you remove the IF() condition in the above example to highlight a more stringent situation, for example ...

 INSERT INTO `updateTest` (`bar`, `foo`, `baz`, `boom`) VALUES (48, 1, 450, 2); INSERT `updateTest` (`bar`, `foo`, `baz`, `boom`) VALUES (48, 0, 400, 5) ON DUPLICATE KEY UPDATE `foo` = VALUES(`foo`), `baz` = VALUES(`baz`), `boom` = VALUES(`boom`); 

... you always either end with (48, 1, 450, 2) or (48, 0, 400, 5) after your statement completes / works, and not some intermediate state, for example (48, 0, 450, 2) .

The same is true for UPDATE behavior, but there are even fewer reasons to juggle IF() , since you can just put your conditional expressions in your WHERE there.

In conclusion: outside of extreme cases, you have atomicity for single-line statements, even using MyISAM . See Johannes H. for more information .

Answer 3

This is the InnoDB table storage engine , yes, the operation is definitely atomic, and partial insertion is not possible. I believe this is not true for MyISAM, the default mechanism, since it does not meet ACID requirements and does not support transactions.

Answer 4

Separate statements such as this. If you need multiple calls to act atomically, you can use transactions in most relational databases.