使用 UNION 时是否保证 SQL 查询以原子方式执行? [英] Are SQL queries guaranteed to execute atomically when using UNION?

问题描述

我正在发出一个 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

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

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.

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