Is this SQL an atomic operation for MySQL?

user_13150883 0
Viewed 116

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.

mysql sql
1 Answers

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.

user_101387340
Related