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

问题描述

我正在发出一个 SQL 查询，该查询由使用 UNION 分组的多个 SELECT 组成:

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

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 规范是否讨论过这种事情?

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?

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

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.

推荐答案

是的，该语句是原子的，但是数据可以在 2 次读取之间更改.

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

Read Committed 仅保证您不会读取脏数据，它不保证读取的一致性，因为您需要更高的隔离级别.

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.

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

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

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

(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

连接 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 杀死它！)

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

涵盖此 READ COMMITED 行为的特定文档是这里

The specific documentation covering this READ COMMITED behaviour is here

共享锁类型决定了何时它将被释放.行锁是在下一行之前释放处理.页面锁被释放当阅读下一页时，表格声明时释放锁结束.

这篇关于使用 UNION 时是否保证 SQL 查询以原子方式执行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！