Is MySQL Query containing sub-query atomic?

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.

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)

Ref: Mysql Internel Manual

So MySQL employed the mechanism of nested transactions to provide the "all or nothing" guarantee for SQL statements that the standard requires. MySQL would create a nested transaction at the start of each SQL statement, and destroy (commit or abort) the nested transaction at statement end. MySQL people internally called such a nested transaction a "statement transaction". And that's what gave birth to the term "statement transaction".

From my own understanding, I consider nested sql in MySQL atomic.

Not sure if I understand the requirement but if you wanted to get a unique session count you only need one table. For table2 add user as primary key (if not already):

ALTER TABLE table2 ADD user PRIMARY KEY;

Then use INSERT IGNORE which will only insert if user does not already exist:

INSERT IGNORE INTO table2 (name) VALUES ('user');

Then you can get unique session by "select count(*) from table2".