Is MySQL Query containing sub-query atomic?

Question

There are two tables:

Table 1 Unique Sessions

ID    Count 

Table 2 (Sessions)

ID    Name

I would like to update count only if name does not exist into sessions to count unique sessions, this is an example so objective is not to do it by alternative way, but question is:

Rowsaffected = Update table1 
                   set Count = Count + 1 
               where (Select count(*) from table2 where Name = 'user1' ) = 0;

Insert into table2 (NAME) values('user');

Is first query an atomic query ? If yes, then there is no issue.

If no, then What if there are multiple threads running to perform above mentioned operations ? There is a chance that:

Thread 1: count returns 0, it has updated table 1 but not table 2 before Thread 2 starts. Thread 2: It finds count 0, it is also going to update count.

Now for same user, count is 2 which shouldn't happen.

Any suggestion / feedback.

Answer 1

Better to use an exists clause: https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html

Rowsaffected = Update table1 set Count = Count + 1 where NOT EXISTS (Select ID from table2 where Name = 'user1' );

References just for the atomic part of your question, but for certainty you could just wrap both statements in a transaction. It doesn't look strictly necessary though depending on your concurrency model you might want to look at raising the transaction isolation level.

MySQL treats statements as atomic https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html

Subqueries technically expressions & as such are part of the statement they appear in. https://dev.mysql.com/doc/refman/8.0/en/expressions.html Although syntacticallly similar to a standalone statment, they are run separately - the optimiser considers the whole statement as a single unit of work.

This does assume you are using a storage engine that supports transactions: https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html (at the end of the table at the bottom of the page)

Isolation levels require that the read locks taken whilst the query runs will remain until the write locks have been taken & all the locks will be released together. https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

(although the documentation doesn't seem to state this explicitly, it wouldn't be able to guarantee isolation levels otherwise)