Início / dba / Perguntas / 224012
Accepted
Kirk Saunders
Asked: 2018-12-04 07:11:50 +0800 CST

Estrutura de bloqueio de tabela/linha de back-end usando consultas CTE

  • 772

Esta pergunta é uma extensão de uma pergunta anterior que fiz. Bloqueio de tabela ao usar uniões

Com a mesma consulta que estamos usando que está causando o bloqueio das várias consultas usadas, essas UNIONSsão as consultas CTE.

Por exemplo:

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

Assumindo este cenário específico (e não temos nenhuma transação explícita declarada) a estrutura de bloqueio da tabela física muda, porque estamos usando consultas CTE?

Quando o SELECTfor CTE1 é concluído como parte do UNION, as tabelas físicas ainda estão bloqueadas ou os bloqueios ainda estão liberados?

Com base na resposta da minha pergunta anterior, fiquei com a impressão de que, no contexto de a, UNIONquando o SELECTpara uma das várias SELECTdeclarações completas, as tabelas são liberadas. Isso é incorreto?

Não modificamos o nível de isolamento para esta transação. Pelo que entendi, isso significa que estamos usando READ_COMMITTED. A questão ainda é aplicável com apenas 2 SELECTdeclarações. Estou tentando entender como funciona essa funcionalidade. A consulta específica com a qual estou trabalhando usa 7, que é a única razão pela qual especifiquei 7.

sql-server sql-server-2012

1 respostas

  1. Best Answer

    Não, os bloqueios não serão alterados por causa dos CTEs. O que você mostrou são CTEs não recursivos. Essas são apenas outra maneira de introduzir uma subconsulta na instrução SQL. A pergunta

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

    é semanticamente idêntico a

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

    Como o otimizador de consulta é livre para reorganizar sua instrução em qualquer equivalente lógico, é provável que essas duas instruções acabem com planos de execução idênticos. (Para consultas muito complexas, onde o CTE ou a consulta principal tem muitas junções, ou o CTE é referenciado muitas vezes, os planos de execução podem ser diferentes, mas isso é um artefato da otimização, não uma propriedade fundamental de como os CTEs funcionam. )

    Este parágrafo é tangencial ao seu ponto principal sobre travamento, mas vale a pena conhecê-lo. Você diz "Quando o SELECT para CTE1 terminar..." Não necessariamente funciona assim. A execução pode prosseguir de qualquer maneira que satisfaça o significado de nossa consulta, mas não precisa seguir a sequência em que foi escrita. Veja aqui e aqui exemplos de como a ordem de execução pode diferir da ordem escrita.

    Quanto à sua pergunta principal "Fiquei com a impressão de que .. quando o SELECT .. completa as tabelas são liberadas. Isso está incorreto?" Vou encaminhá-lo para esta página da Microsoft:

    O comportamento de READ COMMITTED depende da configuração da opção de banco de dados READ_COMMITTED_SNAPSHOT:

    Se READ_COMMITTED_SNAPSHOT estiver definido como OFF (o padrão), o Mecanismo de Banco de Dados usará bloqueios compartilhados para impedir que outras transações modifiquem linhas enquanto a transação atual estiver executando uma operação de leitura. Os bloqueios compartilhados também bloqueiam a instrução de ler linhas modificadas por outras transações até que a outra transação seja concluída. O tipo de bloqueio compartilhado determina quando ele será liberado. Os bloqueios de linha são liberados antes que a próxima linha seja processada. Os bloqueios de página são liberados quando a próxima página é lida e os bloqueios de tabela são liberados quando a instrução termina.

    Aqui está outra explicação que pode explicar melhor. Ambas as fontes deixam claro que a granularidade do bloqueio (linha, página ou tabela) afeta quando o bloqueio é liberado. Há muitas coisas que afetam isso, incluindo configuração do sistema e escalonamento de bloqueio.

    Observarei também que seu exemplo usa UNION. Isso remove duplicatas dos resultados. Você pode obter um comportamento de bloqueio diferente usando UNION ALL, mas isso deixa duplicatas e pode retornar mais linhas, dependendo de seus dados.

    • 3

relate perguntas