问题描述
我正在发出一个 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 查询以原子方式执行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!