MySQL查询是否包含子查询原子的? [英] Is MySQL Query containing sub-query atomic?

问题描述

有两个表:

表1唯一会话

ID    Count 

表2(会话)

ID    Name

仅当不存在会话中的name来计数唯一会话时，我才想更新count，这是一个示例，因此目标不是通过替代方式进行，但是问题是:

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:

线程1:count返回0，在线程2启动之前，它已更新表1，但未更新表2. 线程2:找到计数0，它还将更新计数.

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.

现在对于同一用户，计数为2，这是不应该发生的.

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

任何建议/反馈.

推荐答案

不确定我是否理解要求，但是如果您希望获得唯一的会话计数，则只需要一个表.对于table2，将用户添加为主键(如果尚未添加):

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添加用户PRIMARY KEY;

ALTER TABLE table2 ADD user PRIMARY KEY;

然后使用INSERT IGNORE，仅当用户不存在时才插入:

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

在表2中插入IGNORE (姓名) 价值 (用户")；

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

然后，您可以通过从table2中选择count(*)"来获得唯一的会话.

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

这篇关于MySQL查询是否包含子查询原子的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！