Share via

Efeitos colaterais do WITH (NOLOCK) – Parte I

  • Article

Nesse post vou comentar sobre a utilização da hint NOLOCK e os efeitos colaterais associados. Todo mundo diz que NOLOCK é importante para performance e que, sem esse artifício, ocorreriam bloqueios desnecessários e situações de deadlocks. Com certeza isso é verdade, pois não são alocadas estruturas de table, page, row ou key lock. Por outro lado, poucas pessoas conhecem os efeitos colaterais dessa hint. Um exemplo muito curioso foi descrito por Lubor Kollar, que mostrou um SELECT fazendo leitura do mesmo registro duas vezes – ou perdendo registros! (sem crises: a situação é bastante específica e depende de vários fatores ocorrendo ao mesmo tempo).

Links relacionados

A utilização da hint NOLOCK pode causar erros transitórios decorrentes do acesso concorrente às mesmas informações. Por exemplo, imagine a situação de um comando DELETE apagando os registros que são lidos durante uma operação SELECT no mesmo instante. Estou copiando literalmente o exemplo do Craig Freedman. Primeiro criamos as tabelas:

 CREATE TABLE t1 (k INT,data INT)
INSERTt1 VALUES(0,0), (1,1)

CREATE TABLE t2 (pk INT PRIMARY KEY)
INSERTt2 VALUES(0), (1)

Na sessão 1, iniciamos uma transação que atualiza T2 e mantém bloqueios na tabela:

 BEGIN TRAN
UPDATE t2 SET pk = pk WHERE pk = 0

Na sessão 2, rodamos a query com a hint NOLOCK. Note que a query fica esperando a liberação do lock da tabela T2.

 SELECT * FROM t1 WITH (NOLOCK)
WHERE EXISTS (SELECT * FROM t2 WHERE t1.k = t2.pk)

Na sessão 1, completamos a transação e apagamos um registro da tabela T1 – que está sendo utilizado na sessão 2.

 DELETE t1 WHERE k = 0
COMMIT TRAN

Na sessão 2, o comando SELECT falha!

 Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.

Explicação

Na sessão 2, o comando SELECT realiza uma operação de leitura de T1 enquanto que, no exato momento, a sessão 1 está apagando o registro de T1. Acessos concorrentes e sem bloqueios! Do ponto de vista do SQL Server, um erro de consistência pode ocorrer a qualquer instante. Qual explicação? NOLOCK : solicitamos que nenhum lock seja obtido na tabela.

Analisando microscopicamente, o comando SELECT iniciou a operação de Table Scan em T1, realizando a leitura do registro k=0, e depois ficou bloqueado na tabela T2. Antes de avançar na leitura da tabela, uma outra sessão apagou o registro k=0 e liberou o bloqueio em T2. SELECT continua a operação de Table Scan fazendo a leitura a partir do registro k=0 para buscar k=1, mas… cade o registro k=0? Ele foi apagado. Nesse momento, o table scan foi cancelado com o erro 601 – severity 12. Note que esse erro apresenta baixa severidade porque foi uma consequência de uma situação transitória.

Na sessão Table Hints do do Books Online, a situação é descrita como transitória e que, caso a aplicação receba esse erro, deve re-tentar executar o comando.

If you receive the error message 601 when READUNCOMMITTED is specified, resolve it as you would a deadlock error (1205), and retry your statement.

Conclusão

Lembre-se que há pontos negativos no uso indiscriminado de NOLOCK. Além do comportamento de “leituras sujas”, podem ser encontrados problemas de consistência durante operações de Table/Index Scan.

No próximo post, mostrarei um segundo problema relacionado com o NOLOCK: erros críticos podem ser encobertos pela utilização da hint.

 

Referências

Table Hints

https://msdn.microsoft.com/en-us/library/ms187373.aspx

Troubleshooting Error 601

https://technet.microsoft.com/en-us/library/bb326281.aspx

Lubor Kollar

https://blogs.msdn.com/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx

Craig Freedman

https://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx

Comments

  • Anonymous
    November 19, 2010
    E tem consultor que recomenda nolock como o salvador da patria pra todo lock ...

  • Anonymous
    November 21, 2010
    Haha... é verdade! :)

  • Anonymous
    February 01, 2011
    Belo post Catae. Eu criei uma variação do script com um cenário um pouco pior :-) ... Onde o NOLOCK esta nas DUAS tabelas... normalmente quando mostramos isso para os desenvolvedores/DBAs, eles alegam que eles SEMPRE colocam o NOLOCK em todos as tabelas, por isso eles não teriam problema. Já que no select da sessão 2 eles não ficariam em lock... Bom, vamos lá... Rode o comando da conexão 1 e deixe rodando e depois rode o comando da sessão 2 e espere um pouco até receber o erro :-). O delete da conexão 1 fica gerando lock na linha 0 da tabela Tab1. O select da conexão 2 lê a linha 0 da Tab1, faz o join com a Tab2, depois quando vai ler a próxima linha, a linha 0 não existe mais, dai.... error 601 -- Preparando o ambiente IF OBJECT_ID('Tab1') IS NOT NULL  DROP TABLE Tab1 IF OBJECT_ID('Tab2') IS NOT NULL  DROP TABLE Tab2 GO CREATE TABLE Tab1 (ID INT, Col1 Char(500) DEFAULT NEWID()) GO INSERT Tab1(ID) VALUES(0), (1) GO CREATE TABLE Tab2 (ID INT PRIMARY KEY, Col1 Char(500) DEFAULT NEWID()) GO INSERT Tab2(ID) VALUES(0), (1) GO -- Conexão 1 SET NOCOUNT ON WHILE 1 = 1 BEGIN  -- Conexão 1  BEGIN TRAN  DELETE Tab1 WHERE ID = 0  COMMIT TRAN  INSERT INTO Tab1(ID) VALUES(0) END -- Conexão 2 SET NOCOUNT ON WHILE 1=1 BEGIN  IF OBJECT_ID('tempdb.dbo.#Tab1') IS NOT NULL    DROP TABLE #Tab1  SELECT *    INTO #Tab1    FROM Tab1 WITH(NOLOCK)   WHERE EXISTS (SELECT *                   FROM Tab2 WITH(NOLOCK)                  WHERE Tab1.ID = Tab2.ID) END

  • Anonymous
    May 06, 2011
    Ótimo exemplo Fabiano! Na verdade, aquele script que passei com concorrência de usuários causa o mesmo efeito. O lock apenas facilita a situação. Mas a situação é bem parecida - um erro transitório 601 porque o registro sumiu. Abraços, Fabricio

  • Anonymous
    August 26, 2014
    Catae, Show de post, simples e direto, se você me permite vou utilizer os scripts como exercícios nas minhas aulas de Programação de Banco de Dados. Valeu.

  • Anonymous
    August 28, 2014
    Claro! Voce pode usar qualquer artigo ou script. Tive até uma ideia... vou colocar no github. :)