I'm doing multiple inserts in a single query:
INSERT INTO table (c1, c2) VALUES (1,2),
(2,3),
(3,4),......
ON DUPLICATE KEY UPDATE c2 = VALUES(c2)
Now suppose that there's over tens of thousands of VALUES specified in the query (hence the ellipsis)....
Can there ever be a case in which some parts of the VALUES managed to get inserted/updated in the database but the rest did not get inserted/updated possibly due to some sort of db error/failure/memory-running-out etc?
Are mysql queries ALL or Nothing?
Is it true that for every mysql query executed, either all values specified in the query will be inserted/updated smoothly, or none of the values will be inserted/updated?
MySQL 8.0 supports atomic Data Definition Language (DDL) statements. This feature is referred to as atomic DDL. An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic operation.
Batching inserts, updates and deletes. Batch operations for both single partition and multiple partitions ensure atomicity. An atomic transaction is an indivisible and irreducible series of operations such that either all occur, or nothing occurs.
The SQL INSERT query is used in a manner wherein we make use of a single INSERT query to insert multiple records within a single point of execution.
An SQL DML command like INSERT is always automatically atomic, since it cannot run outside a transaction.
ACID (Atomicity, Consistency, Isolation, Durability) properties are used to describe such behaviour in databases. Atomicity is only important if we're dealing with concurrent modifications. To ensure Consistency, a certain level of Isolation must be reached. The more isolated multiple transactions run, however, the less performance the DBMS usually has. So there is the so called "isolation level", which states what errors can possibly occur in a DBMS and which cannot.
Now, MySQL implements all isolation levels in INNODB databases, and you can choose for each transaction: https://dev.mysql.com/doc/refman/5.1/en/set-transaction.html
MyIsam databases don't support transactions, single operations should however run atomically. (Source: https://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html). Note however, that this does NOT guarantee data isn't changed between the reads and writes in one operation - atomicity in DBMS terms only means that the operation is either completely done or completely skipped. It does NOT guarantee isolation, consistency or durability.
"Can there ever be a case in which some parts of the VALUES managed to get inserted/updated in the database but the rest did not get inserted/updated possibly due to some sort of db error/failure/memory-running-out etc?"
Late answer, but perhaps interesting: [ON DUPLICATE KEY] UPDATE
is not strictly atomic for single rows (neither for MyISAM
, nor for InnoDB
), but it will be atomic in regards to errors.
What's the difference? Well, this illustrates the potential problem in assuming 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 have turned into (47, 0, 450, 2)
, and not into (47, 0, 400, 5)
. If you assume strict atomicity (which is not to say you should; you might prefer this behaviour), that shouldn't happen - foo
should certainly not change before the other columns' values are even evaluated. foo
should change together with the other columns - all or nothing.
If I say atomic in regards to errors, I mean that if you remove the IF()
condition in the above example that's highlighting the stricter situation, like this...
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 will always either end up with (48, 1, 450, 2)
or (48, 0, 400, 5)
after your statement has finished/crashed, and not some in-between state like (48, 0, 450, 2)
.
The same is true for the behaviour of UPDATE
, but there's even less of a reason to juggle IF()
statements there, since you can just put your conditionals into your WHERE
clause there.
In conclusion: Outside of edge-cases, you do have atomicity for single-row statements, even using MyISAM
. See Johannes H.'s answer for further information.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With