Como usar SELECT WITH NOLOCK para melhorar a Performance?


Pode parecer uma dica simples demais, mas tenho observado muitas dúvidas quanto ao uso do NOLOCK. No tempo do SQL 2000, a própria Microsoft (que ninguém me ouça dizendo isso!) recomendava o uso indiscriminado do NOLOCK [sem referência]. Por esse motivo, decidi explicar um pouco seu funcionamento.

Conhece o checklist de Performance de servidor?

SQL Server utiliza mecanismos de bloqueio (LOCK) para garantir a integridade de dados. O fato é que muitas vezes o bloqueio (LOCK) impacta diretamente na performance do sistema. Por exemplo, veja os dois comandos:

1) SELECT COUNT(Nome) FROM TabelaPessoas

2) INSERT TabelaPessoas (Nome) VALUES (‘Fabricio’)`

Se forem executados simultaneamente, os comandos efetuarão leitura (SELECT) e escrita (INSERT) contra a mesma tabela. Entretanto, o banco de dados garante a integridade e executa um comando por vez. Em outras palavras, o comando de SELECT não pode ocorrer no mesmo instante que o INSERT. Esse é um caso simples e inofensivo, mas pense agora nos sistemas de grande porte.

Um banco de dados que precisa executar 100 transações por segundo, mas precisa gerar alguns relatórios rápidos. As transações correspondem aos INSERTS, enquanto que os relatórios são SELECT. Como podemos melhorar a performance dos INSERT que concorrem com comandos SELECT?

Solução: Uso de NOLOCK

O comando SELECT permite o uso de uma opção denominada NOLOCK, evitando assim os bloqueios com os comandos de INSERT. O uso é simples, bastando adicionar algumas palavras após a declaração da tabela.

SELECT COUNT(Nome) FROM TabelaPessoas WITH (NOLOCK)

Dessa forma, o comando WITH (NOLOCK) indica que não será necessário bloquear a tabela durante a leitura dos dados.

Recomendação

O uso de NOLOCK indiscrinado pode causar problemas transitórios devido a movimentação de dados sem bloqueios. Veja os exemplos Efeitos colaterais do NOLOCK – Parte I e Efeitos colaterais do NOLOCK – Parte 2.

A partir do SQL Server 2005/2008, existe uma solução mais elegante que é o uso do READ COMMITTED SNAPSHOT no banco de dados. O melhor de tudo é que essa é uma configuração do banco de dados e não necessita alteração em código.

Links relacionados

 

Comments (13)

  1. Igor de Antonio disse:

    Já tivemos muitos problemas de lock no passado aqui na empresa, hoje usamos muito nolock…

    Preciso estudar melhor o READ COMMITTED SNAPSHOT, para ver se podemos implementar.

    Muito bom post, me fez reviver este assunto na mente.

    Parabéns.

  2. fcatae disse:

    Obrigado! Se tiver sugestões sobre como deveria ser o post sobre READ COMMITTED SNAPSHOT, avise!

    Abraços, Fabricio

  3. Thiago disse:

    Fabricio, boa tarde.

    Eu vejo em muitos artigos sobre o isolation level das bases de dados, mas, como e feita a configuração para que o banco de dados use o READ COMMITED SNAPSHOT?

    SET BancoDeDados IsolationLevel?

    Abs

  4. fcatae disse:

    A configuração é feita por banco de dados usando o comando:

    ALTER DATABASE <nome_do_banco) SET READ_COMMITTED_SNAPSHOT ON

    Acho que faltou explicar isso melhor, né?

    Abraços, Fabricio

  5. Marco Rodrigues disse:

    Vale lembrar que o uso  do RCSI aumenta também o uso de TempDB. Só dar aquela verificada se a TemDB está seguindo as recomendações de multiplo arquivos e se há espaço suficiente.

    Em aplicações como Dynamics CRM, o uso do RCSI auxiília bastante na performance do sistema.

  6. Diego Silva disse:

    Mas um alerta sobre o With (NOLOCK), cuidado com as Leituras Suja e Fantasma. NOLOCK permite que você leia dados que não foram Commitados.

  7. Joel Freitas disse:

    Boa tarde.

    Realmente o uso do (NOLOCK) é fundamental, parabéns pelo post….

  8. Guilherme Reis disse:

    NOLOCK deixa selects mais rapidos ?

  9. fcatae disse:

    Sim, NOLOCK ajuda em desempenho – em contrapartida, os dados são lidos "sujos".

  10. Robson disse:

    Pra Evitar os dados sujos

    with (readpast)

  11. Muito bom ajudou bastante, estou com um problema em uma query, e a mesma possui varios nolock
    resolvi deixar onde faz inserte e updade

  12. O ideal seria se a Microsoft mudasse para o snapshot isolation level como sendo padrão do SQL Server. Acho que assim, evitaria-se o problema de leitura suja.

    1. Oi Rodrigo! Também acho! Snapshot Isolation deveria ser o padrão. Entretanto, para evitar problemas com o legado, foi deixado os locks como mecanismos padrão da instância e a possbilidade de habilitar o Snapshot Isolation por banco de dados.
      Daí alguém pergunta: cenários de legado? Sim – principalmente em DW, onde o versionamento pode degradar a performance quando comparado a um simples TABLOCK.
      Abraços, Fabricio