Efeitos colaterais do WITH (NOLOCK) – Parte I

Efeitos colaterais do WITH (NOLOCK) – Parte I

  • Comments 4

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
http://msdn.microsoft.com/en-us/library/ms187373.aspx

Troubleshooting Error 601
http://technet.microsoft.com/en-us/library/bb326281.aspx

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

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

  • E tem consultor que recomenda nolock como o salvador da patria pra todo lock ...

  • Haha... é verdade! :)

  • 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

  • Ó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

Page 1 of 1 (4 items)
Leave a Comment
  • Please add 1 and 5 and type the answer here:
  • Post