sql - 使用UNION时，是否保证SQL查询可以原子执行？

Question

我正在发布一个单个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;

假设我在 READ_COMMITTED事务隔离下执行此查询，是否保证两个SELECT语句可以原子执行？还是冒着在各个SELECT语句之间更改数据的风险？ SQL规范是否讨论这种事情？

声明:当我说“原子”时，并不是指ACID中的“A”。我的意思是，我希望部门表和雇员表都将被锁定，直到查询完成。

Answer 1

是的，该语句是原子的，但是是的，数据可以在两次读取之间改变。
Read Committed仅保证您不会读取脏数据，它对读取的一致性没有任何其他保证，因为您需要更高的隔离级别。

就像您说的那样，您将接受一个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杀死它!)

涵盖此 READ COMMITED行为 is here的特定文档

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.