Início / dba / Perguntas / 187029
Accepted
Heinzi
Asked: 2017-09-28 01:40:49 +0800 CST

INSERT e UPDATE "não relacionados" bloqueando um ao outro

  • 772

Cenário de reprodução:

CREATE TABLE test (
  ID int IDENTITY(1,1),
  mykey nvarchar(255) NOT NULL,
  exp_date datetime,
PRIMARY KEY (ID));
GO

CREATE INDEX not_expired_keys ON test (exp_date, mykey);

INSERT INTO test (mykey, exp_date) VALUES ('A', NULL);

Eu começo a transação 1:

-- add key B
BEGIN TRANSACTION;
INSERT INTO test (mykey, exp_date) VALUES ('B', NULL);
...

e, em seguida, execute a transação 2 em paralelo:

-- expire key A
BEGIN TRANSACTION;
UPDATE test SET exp_date = GETDATE() WHERE exp_date IS NULL AND mykey = 'A'; -- <-- Blocking
ROLLBACK;

Como se vê, o INSERT não confirmado da transação 1 bloqueia o UPDATE da transação 2, mesmo que eles afetem conjuntos disjuntos de linhas ( mykey = 'B'vs. mykey = 'A').

Observações:

  • O bloqueio também ocorre no nível de isolamento de transação mais baixo READ UNCOMMITTED.
  • O bloqueio desaparece se eu colocar um índice exclusivo em mykey. Infelizmente, não posso fazer isso, pois os nomes das chaves podem ser reutilizados quando uma chave expira.

Minhas perguntas:

  • (Por curiosidade:) Por que essas declarações bloqueiam umas às outras mesmo no READ UNCOMMITTEDnível?

  • Existe uma maneira fácil e confiável de fazer com que eles não se bloqueiem?

sql-server locking

2 respostas

  1. Best Answer

    Vamos dar uma olhada nos planos de execução.

    1ª consulta - Inserir

    BEGIN TRANSACTION;
INSERT INTO test (mykey, exp_date) VALUES ('B', NULL);

    E seu plano de execuçãoinsira a descrição da imagem aqui

    Vemos que o sql server está fazendo a operação Clustered Index Insert.

    Agora vamos dar uma olhada na atualização

    BEGIN TRANSACTION;
UPDATE test 
SET exp_date = GETDATE() 
WHERE exp_date IS NULL AND mykey = 'A' -- <-- Blocking

    E seu plano de execuçãoinsira a descrição da imagem aqui

    O SQL Server verifica o índice clusterizado da tabela e coloca o bloqueio U nele, mesmo que possa escolher outro índice para localizar as linhas correspondentes. O motivo é que temos apenas 1 linha na tabela e o SQL Server Optimizer acha mais fácil verificar o índice clusterizado em vez de pesquisar dados no índice não clusterizado.

    Mas e se forçarmos o sql server a usar o índice não clusterizado?

    BEGIN TRANSACTION;
UPDATE test 
SET exp_date = GETDATE() 
FROM test WITH(INDEX = not_expired_keys)
WHERE exp_date IS NULL AND mykey = 'A' -- <-- No Blocking!!!

    E seu plano de execuçãoinsira a descrição da imagem aqui

    Acho que se colocarmos mais linhas na tabela o SQL Server escolherá o índice não clusterizado para encontrar as linhas que devem ser atualizadas, e não haverá bloqueio.

    • 4

  2. Isso é essencialmente um bug no SQL Server. Em vez de implementar bloqueios de predicado, a MS optou pela maneira mais barata de bloquear um intervalo em um dos índices usados ​​para selecionar as linhas da tabela. Se não usar um índice (ou se não houver índice), ele bloqueará um intervalo na tabela.

    Sim, forçar o índice fará com que ele bloqueie o índice; no entanto, sempre leva bloqueios de página. Os bloqueios de página são ruins para hierarquias de bloqueio; linhas não relacionadas que estejam na mesma página de índice ou página de tabela serão bloqueadas. Não há como contornar isso com duas declarações de escrita. Se uma de suas declarações estiver lendo, WITH(ROWLOCK)o leitor ignorará.

    Eu abri um caso com a MS que durou dois meses antes que eles se recusassem a consertá-lo.

    • -2

relate perguntas