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

Question

我发出一个由使用 UNION 分组的多个 SELECT 组成的单个 SQL 查询：

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

假设我在 READ_COMMITTED事务隔离，两个SELECT语句是否保证原子执行？或者我是否面临各个 SELECT 语句之间数据更改的风险？ SQL 规范是否讨论了这类事情？

澄清：当我说“原子”时，我并不是指 ACID 中的“A”。我的意思是，我希望部门表和员工表都被读锁定，直到查询完成。

Answer 1

是的，该语句是原子的，但数据可以在两次读取之间发生变化。

已提交读仅保证您不会读取脏数据，它对读取一致性没有任何保证，因为您需要更高的隔离级别。

正如您所说，您将接受 SQL Server 示例...

连接 1

（假设在悲观读已提交隔离级别下）

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

连接 2

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

现在返回到连接 1

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

（记住切换回连接 2 以杀死它！）

具体文档涵盖此已提交行为在这里

共享锁类型决定何时
它将被释放。行锁是
在下一行之前释放
已处理。页面锁被释放
当读取下一页时，表
当语句执行时锁被释放
完成。

Answer 2

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

Answer 3

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

每笔单独的交易都是原子的。

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

这在一定程度上是避免低效查询（或存储过程）的一个原因，因为它们的原子性质可能会延迟其他事务。

编辑：
有关子查询原子性的更多有趣信息，请参阅此问题

Is update withnested selectatomic操作？

编辑：显然我错了。

这是关于该主题的很好的讨论：SQL Server 2005 中的原子 UPSERT，其中 Remus 提供了一个很好的示例。抱歉怀疑你了，马丁……