Is this SQL an atomic operation for MySQL?

Question

Is this SQL an atomic operation for MySQL ?

UPDATE
    api_report a
    JOIN
    (
        SELECT
            id
        FROM
            api_report
        WHERE
            api_report.success_nums = 7878
    ) b
        ON a.id = b.id 
SET
    a.success_nums = 4646;

Assume that I have 2 threads to execute the upper SQL, the data will be updated by 1 of the 2 thread? or by the both 2 thread concurrently? I wrote a demo, and it seems the data is updated by only 1 of the 2 thread.

Better idea: instead of worrying about statement atomicity, use transactions! — Dai, Oct 29 '20 at 08:47
Can you share more details? Are you facing any real problem that leads you to this question? — Nico Haase, Oct 29 '20 at 09:04
MySQL, like all RDBMS’s, are [*ACID*](https://en.wikipedia.org/wiki/ACID) compliant (the “A” is for “Atomic”), so “yes” - the particular statement is irrelevant. — Bohemian, Oct 29 '20 at 09:12
@Dai Syntax aside, how do transactions help here? Transactions allow rollback and isolation but do they have atomicity features? (Honest question, I'm not an expert). — Álvaro González, Oct 29 '20 at 09:15
@Bohemian I'm going to disagree there - consider MS SQL Server's `MERGE INTO` statement - which is definitely not atomic in itself - you always need to specify an explicit `HOLDLOCK` (or worse) in order to prevent race-conditions with `MERGE INTO`. — Dai, Oct 29 '20 at 09:39
@ÁlvaroGonzález Transactions are atomic: https://dba.stackexchange.com/questions/227563/how-a-transaction-is-atomic (granted, with loads of edge-cases that I won't get into) — Dai, Oct 29 '20 at 09:40
@Dai So, MySQL won't show changes in tables made from other sessions from the moment you run `START TRANSACTION`? That's pretty cool. — Álvaro González, Oct 29 '20 at 09:48
@Dai you can also set read uncommitted true, and various other techniques to subvert ACID. But in the general case queries are atomic. — Bohemian, Oct 29 '20 at 14:16

Akina · Answer 1 · 2020-10-29T09:13:06.017

0

Use correct multiple-table syntax:

UPDATE api_report a
  JOIN api_report b ON a.id = b.id
SET a.success_nums = 4646
WHERE b.success_nums = 7878

Attention! this query will update ALL matched rows including one which has success_nums = 7878. fiddle. If you does not need this then add more conditions to WHERE clause, for example, AND a.success_nums != 7878.

This SQL seems to be an atomic operation for MySQL.

edited Oct 29 '20 at 09:13

answered Oct 29 '20 at 09:07

Akina

39,301
5
14
25

@GordonLinoff Sorry, but I don't understand the relation between the question and your experiment. It is well-known that MySQL counts (and writes) actual updates only, if new value is equal to old one then this update is not counted as actual. – Akina Oct 29 '20 at 11:16

Is this SQL an atomic operation for MySQL?

1 Answers1