主页 / dba / 问题 / 224012
Accepted
Kirk Saunders
Asked: 2018-12-04 07:11:50 +0800 CST

使用 CTE 查询的后端表/行锁定结构

  • 772

这个问题是我之前问过的一个问题的延伸。 使用联合时表锁定

对于我们正在使用的相同查询，导致阻塞了这些UNIONSCTE 查询中使用的各种查询。

例如：

CREATE TABLE #TempTable (
    [Columns]
)

WITH CTE1 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE2 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE3 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE4 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE5 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE6 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE7 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
)

INSERT INTO #TempTable
SELECT * FROM CTE1
UNION
SELECT * FROM CTE2
UNION
SELECT * FROM CTE3
UNION
SELECT * FROM CTE4
UNION
SELECT * FROM CTE5
UNION
SELECT * FROM CTE6 WHERE [Where-Condition]
UNION
SELECT * FROM CTE7

假设这个特定场景（并且我们没有声明任何显式事务）物理表锁定结构是否会改变，因为我们正在使用 CTE 查询？

当SELECTfor CTE1 作为 的一部分完成时UNION，物理表是否仍处于锁定状态或是否仍处于释放状态？

根据我上一个问题的答案，我的印象是，在各种语句中的一个完成UNION时，表被释放。那是不正确的吗？SELECTSELECT

我们没有修改此事务的隔离级别。据我了解，这意味着我们正在使用READ_COMMITTED. 这个问题仍然适用，只有 2 个SELECT陈述。我试图了解此功能的工作原理。我正在使用的特定查询使用 7，这是我指定 7 的唯一原因。

sql-server sql-server-2012

1 个回答

  1. Best Answer

    不，锁不会因为 CTE 而改变。您所展示的是非递归 CTE。这些只是在 SQL 语句中引入子查询的另一种方式。查询

    with some_CTE as
(
  select < whatever > from < something >
)
select * from some_CTE;

    在语义上等同于

    select * from
(
  select < whatever > from < something >
) as some_alias;

    由于查询优化器可以自由地将您的语句重新排列为任何逻辑等效项，因此这两个语句很可能最终会具有相同的执行计划。（对于非常复杂的查询，CTE 或主查询有很多连接，或者 CTE 被多次引用，执行计划可能不同，但这是优化的产物，而不是 CTE 工作方式的基本属性。 )

    本段与您关于锁定的主要观点相切，但值得了解。你说“当 CTE1 的 SELECT 完成时......”它不一定像那样工作。执行可以以任何满足我们的查询含义的方式进行，但不必遵循它被写入的顺序。有关执行顺序与书面顺序可能有何不同的示例，请参见此处和此处。

    至于你的主要问题“我的印象是......当 SELECT .. 完成时表被释放。这是不正确的吗？” 我将向您介绍此Microsoft 页面：

    READ COMMITTED 的行为取决于 READ_COMMITTED_SNAPSHOT 数据库选项的设置：

    如果 READ_COMMITTED_SNAPSHOT 设置为 OFF（默认值），则数据库引擎使用共享锁来防止其他事务在当前事务运行读取操作时修改行。共享锁还阻止语句读取其他事务修改的行，直到其他事务完成。共享锁的类型决定了何时释放。在处理下一行之前释放行锁。页锁在读取下一页时释放，表锁在语句完成时释放。

    这是另一种解释，可以更好地解释它。两个来源都明确指出锁定的粒度（行、页或表）会影响何时释放锁。影响这一点的因素有很多，包括系统配置和锁升级。

    我还会注意到您的示例使用了 UNION。这会从结果中删除重复项。您可能会使用 UNION ALL 获得不同的锁定行为，但这会留下重复项并可能返回更多行，具体取决于您的数据。

    • 3

相关问题