在使用UNION时，SQL查询是否保证以原子方式执行？

I am issuing a single SQL query consisting of multiple SELECTs grouped using UNION:

我发出一个单独的SQL查询,包含使用UNION分组的多个SELECT:

SELECT *
FROM   employee 
       LEFT JOIN department 
          ON employee.DepartmentID = department.DepartmentID
UNION
SELECT *
FROM   employee
       RIGHT JOIN department
          ON employee.DepartmentID = department.DepartmentID;

Assuming I execute this query under READ_COMMITTED transaction isolation, are the two SELECT statements guaranteed to execute atomically? Or do I run the risk of data changing between individual SELECT statements? Does the SQL specification discuss this sort of thing?

假设我在READ_COMMITTED事务隔离下执行此查询,两个SELECT语句是否保证以原子方式执行?或者我是否存在各个SELECT语句之间数据更改的风险? SQL规范是否讨论过这种事情?

CLARIFICATION: When I say "Atomic" I don't mean the "A" in ACID. I mean that I expect both department and employee tables to be read-locked until the query completes.

澄清:当我说“原子”时,我不是指ACID中的“A”。我的意思是我希望在查询完成之前对department和employee表进行读锁定。

3 个解决方案

#1

Yes the statement is atomic but yes the data can change between the 2 reads.

是的,声明是原子的,但是数据可以在2次读取之间改变。

Read Committed only guarantees that you don't read dirty data it promises nothing else about consistency of reads for that you would need a higher isolation level.

Read Committed只保证您不会读取脏数据,因为您需要更高的隔离级别,因此不会对读取的一致性做出任何承诺。

As you said that you would accept a SQL Server Example...

如你所说,你会接受一个SQL Server示例...

Connection 1

(Assumes under pessimistic read committed isolation level)

(假设在悲观的读取提交隔离级别下)

CREATE TABLE employee
(
name VARCHAR(50),
DepartmentID INT
)

CREATE TABLE department
(
DepartmentID INT
)

INSERT INTO department VALUES (1)
INSERT INTO employee VALUES ('bob',1)

declare @employee TABLE
(
name VARCHAR(50),
DepartmentID INT
)


WHILE ((SELECT COUNT(*) FROM @employee) < 2)
BEGIN
DELETE FROM  @employee

INSERT INTO @employee
SELECT employee.*
FROM   employee 
       LEFT JOIN department 
          ON employee.DepartmentID = department.DepartmentID
UNION
SELECT employee.*
FROM   employee
       RIGHT JOIN department
          ON employee.DepartmentID = department.DepartmentID

END;          

SELECT * FROM @employee

Connection 2

while (1=1)
UPDATE employee SET name = CASE WHEN name = 'bob' THEN 'bill' else 'bob' END

Now go back to connection 1

现在回到连接1

name                                               DepartmentID
-------------------------------------------------- ------------
bill                                               1
bob                                                1

(Remember to switch back to Connection 2 to kill it!)

(记得切换回Connection 2杀掉它!)

The specific documentation covering this READ COMMITED behaviour is here

这里介绍了READ COMMITED行为的具体文档

The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.

共享锁定类型确定何时释放它。在处理下一行之前释放行锁。读取下一页时会释放页锁,并在语句完成时释放表锁。

#2

Using UNION will remove any duplicate records that may be returned from either of the unioned queries, so not exactly atomic. Use UNION ALL if you want all records from all unioned queries. UNION ALL can be much faster that UNION also.

使用UNION将删除任何可能从任何联合查询返回的重复记录,因此不完全是原子的。如果要从所有联合查询中获取所有记录,请使用UNION ALL。 UNION ALL也可以比UNION快得多。

#3

EDIT: Note that my answer is incorrect but I do not want to delete it because I think it links to good questions and has good comments.

编辑:请注意我的答案是不正确的,但我不想删除它,因为我认为它链接到好的问题,并有很好的评论。

Every individual transaction is atomic.

每个单独的交易都是原子的

A UNION using multiple subqueries is a single T-SQL command, a single transaction, and will be atomic.

使用多个子查询的UNION是单个T-SQL命令,单个事务,并且是原子的。

This is, in part, a reason to avoid inefficient queries (or sprocs, for that matter) as their atomic nature can delay other transactions.

这部分地是避免低效查询(或者sprocs)的原因,因为它们的原子性质可以延迟其他事务。

EDIT: Please see this question for more interesting information on atomicity of subqueries

编辑:请参阅此问题以获取有关子查询原子性的更多有趣信息

Is update with nested select atomic operation?

是否使用嵌套选择原子操作更新?

EDIT: Apparently I am wrong.

编辑:显然我错了。

This is a good discussion on the topic: Atomic UPSERT in SQL Server 2005 where Remus poses a good example. Sorry for doubting you, Martin....

这是一个很好的讨论主题:SQL Server 2005中的Atomic UPSERT,Remus就是一个很好的例子。对不起你,马丁......