Início / dba / Perguntas / 220615
Accepted
jesijesi
Asked: 2018-10-20 13:40:27 +0800 CST

Deadlock - Devido à atualização causando o movimento da página de índice dentro de um índice

  • 772

Ainda estou solucionando o impasse mencionado nestas perguntas . Com isso, obtive ajuda para entender por que um select manteria um bloqueio compartilhado e pude testar e confirmar isso também.

Agora estou olhando para o segundo spid envolvido no impasse. Essa sessão faz uma atualização usando uma chave primária (WHERE BatchId = @BatchI , BatchId é o PK) e precisa de IX em duas páginas. Inicialmente, não consegui descobrir por que ele precisava de bloqueios em duas páginas de um índice não clusterizado quando estava apenas atualizando uma linha. Então percebi que a atualização para essa linha alteraria a localização da linha no índice não clusterizado e isso poderia ser o motivo pelo qual ela precisa de bloqueios em duas páginas.

Isso é correto?

Em segundo lugar, por favor, ajude a confirmar se meu entendimento sobre esse impasse está correto. Para simplificar, vamos supor que há apenas duas páginas nesse índice não clusterizado. Página1 e Página2. Então é isso que está acontecendo mais ou menos insira a descrição da imagem aqui

sql-server performance

1 respostas

  1. Best Answer

    Assim, alguns testes e o impasse é de fato causado pelo movimento da página dentro do índice não clusterizado causado por uma atualização que altera a posição da linha no índice.

    insira a descrição da imagem aqui

    Portanto, inicialmente a Sessão 2 (atualização) remove o bloqueio IX na Página2 onde a linha do índice está originalmente localizada. Como a atualização alteraria o valor e, por sua vez, alteraria a posição da linha do índice dentro do índice, a sessão 2 também desejará remover outro bloqueio IX na página de destino onde a linha do índice terminará após a atualização.

    Para entender o motivo pelo qual um select deseja manter o bloqueio S, consulte esta pergunta.

    REPO

    set nocount on 
create table deadlck ( a int , b int, c char (3), constraint PKA primary key (a))
create index idx on deadlck(b)
--creating a big enough table so that selects use page lock instead of row lock
declare @a int =1
declare @b int =57800000 
while 1=1
begin
insert into deadlck select @a,@b,'111'
if (@a%521) =520 -- to make idx index pages fit one value in each page perfectly,  not necessary, just easier to simulate the issue while testing
set @b=@b-1
Set @a=@a+1
if @a=52100
break
end

ALTER INDEX idx ON deadlck
REBUILD WITH (FILLFACTOR = 90); -- same reason as above, i did the math with a fill factor of 90 to have the pages of idx index have one value per page and and also the pages needed to have some space so when we do the update the index entry can move to another page without have to do a page split

ALTER INDEX PKA ON deadlck
REBUILD WITH (FILLFACTOR = 90); 

--session 1
while 1=1
begin
begin tran
update deadlck set b=57799999 where a=2
rollback
end

--session 2
while 1=1
begin
begin tran
select b from deadlck with(paglock) where (b =57799999 or b=57800000)
rollback
end
    • 2

relate perguntas