Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Are mysql multiple inserts within a Single query atomic?

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?

like image 231
pillarOfLight Avatar asked Feb 05 '14 17:02

pillarOfLight


People also ask

Are MySQL queries Atomic?

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.

Is batch insert Atomic?

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.

Can we insert single or multiple records using a single query in MySQL?

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.

Is insert Atomic?

An SQL DML command like INSERT is always automatically atomic, since it cannot run outside a transaction.


2 Answers

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.

like image 168
Johannes H. Avatar answered Oct 14 '22 04:10

Johannes H.


"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.

like image 32
pinkgothic Avatar answered Oct 14 '22 05:10

pinkgothic