Geek Answers Handbook

Is inserting / updating a connection in MySQL an atomic operation?

In a Mysql database with every InnoDB-based table 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)

+5
sql mysql concurrency locking atomicity
source share
4 answers

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

Atomicity in databases means just all or nothing. It does not mean the correctness of the data. Your expression succeeds or not. It has nothing to do with joins or subqueries. One statement is one statement, regardless of whether your database should use a temporary table in memory or on disk or not.

Transactions will simply tell your database to treat multiple operators as one operator. When one of the statements fails, they all roll back.

An important related topic is the level of isolation . You can read about it.

EDIT (to respond to the comment):

It is right. As long as it is a valid operator and there is no power failure or other reasons why the request may fail, this is done. Atomicity alone simply ensures that the statement is fulfilled / or fulfilled. It guarantees completeness and the data is not corrupted (the reason the write operation did not end or something else). This does not guarantee the correctness of the data. Given a query like INSERT INTO foo SELECT MAX(id) + 1 FROM bar; You must ensure that by setting the correct insulation level you do not get phantom reads or something else.

+5
source share

Not. If you don’t wrap them in a 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

+4
source share

Your sql statements will not be executed atomically with autocommit. You need to start a transaction to disable auto-switching. See http://dev.mysql.com/doc/refman/5.0/en/commit.html

+3
source share

I do not, and I will explain to you why. I had a very strange problem with MySQL.

Imagine you have a table named "table1" with one record. Column f1 has the value "A". Column f2 has the value "B"

 Update table1 set f1 = CONCAT(f1,f2), f2 = 'C';

The final value of f1 is "AB" as expected.

But if you change the order:

 Update table1 set f2 = 'C', f1 = CONCAT(f1,f2);

The final value of f1 is "AC". That is: f2 first changes, and after that f1.

My conclusion is that the update operation is clearly non-atomic. f2 first changes. f1 changes after using the updated value of f2, not the original one.

0
source share

More articles:


All Articles