Início / dba / Perguntas / 294900
Accepted
YHTAN
Asked: 2021-06-28 13:48:27 +0800 CST

problema em entender o bloqueio e o índice de cobertura

  • 772

De acordo com meu estudo, descobri que muitas pessoas mencionam que o índice de cobertura ajudaria a reduzir o bloqueio de "pesquisa de chave". Eu estava ansioso para testá-lo e entendê-lo, mas meus testes não mostraram o resultado esperado.

Primeiro eu executo o código abaixo, tento atualizar a tabela na primeira transação sem commit e depois em outra sessão, executo uma query para selecionar o col2 e col3 da tableA. De acordo com o livro, a segunda sessão será bloqueada pela primeira sessão.

-- Create the table for testing
CREATE TABLE [TableA] 
(   
    [col1] INT,
    [col2] INT, 
    [col3] INT, 
    [col4] CHAR(100) DEFAULT('abc') NOT NULL
);
GO

DECLARE @int INT;
SET @int = 1;

-- Load data into the table
WHILE (@int <= 1000) 
BEGIN
    INSERT INTO [TableA] 
        ([col1], [col2], [col3], [col4])
    VALUES (@int*2, @int*2, @int*2, @int*2);
    SET @int = @int + 1;
END
GO

CREATE CLUSTERED INDEX [cidx_TableA] 
ON [TableA] ([col1]);

-- Create a non-clustered index
CREATE NONCLUSTERED INDEX [idx_TableA_col2] 
ON [TableA] ([col2]);
GO

BEGIN TRANSACTION
    UPDATE [TableA] SET col3=999 WHERE Col2=4
--Rollback

--ON another session, I query this.
SELECT [col2],col3 FROM [TableA] 
WHERE [col2]=66
option (recompile)

A segunda sessão fez usando a chave de pesquisa para o índice do cluster cidx_TableA e retornou o resultado, espero que não retorne nenhum resultado porque suponha que ele travaria pela atualização na primeira sessão. Por quê?

Ainda não criei o índice de cobertura, pois não fez nenhum bloqueio. Então, o índice de cobertura realmente funcionou?

insira a descrição da imagem aqui

sql-server blocking

1 respostas

  1. Best Answer

    Há muito o que descompactar aqui, mas direi que provavelmente a melhor decisão seria começar a analisar o isolamento de instantâneos confirmados de leitura ( RCSI ), isso significa que você não precisa mais de bloqueios para ler dados (e como bônus, você obtém resultados significativos).

    Com os índices como estão atualmente (assumindo acesso via idx_TableA_col2), sua instrução de atualização será bloqueada

    • As entradas no idx_TableA_col2índice para as linhas que são atualizadas
    • As linhas na tabela que são atualizadas

    Sua instrução select não precisa ler nenhum desses bits bloqueados porque você pode procurar WHERE [col2]=66no idx_TableA_col2índice (evitando as entradas de índice bloqueadas) e usá-lo para acessar apenas as linhas da tabela que correspondem a essa condição (novamente, essas linhas não são bloqueado).

    O problema de bloqueio que um índice de cobertura está tentando evitar é quando uma linha é atualizada, mas a consulta não precisa se preocupar com essa coluna. Sua updateinstrução está atualizando col3, que está envolvida em sua instrução select para que seu índice de cobertura ([col2],[col3])seja bloqueado para essas linhas também, pois a instrução de atualização precisará atualizar as entradas neste índice.

    Para demonstrar o problema que um índice de cobertura resolveria, você precisaria alterar a updateinstrução para não estar atualizando col3, mas fazer com que o wherefiltro retornasse as mesmas linhas:

    BEGIN TRANSACTION
    UPDATE [TableA] SET col4=999 WHERE Col2=4
--rollback

--ON another session
SELECT [col2],col3 FROM [TableA] 
WHERE [col2]=4
option (recompile)

    Mas, como mencionei no primeiro parágrafo, usar o RCSI evitará completamente esse problema.

    • 1

relate perguntas