Fala, galera!

Neste artigo eu quero mostrar na prática, o uso de dois query hints muito utilizados pelos desenvolvedores para evitar locks na leitura de dados, que são: NOLOCK e o READPAST, e demonstrar efetivamente qual o efeito desses hints em uma consulta.

A ideia de escrever este artigo veio através de uma dúvida enviada no grupo “SQL Server – DBA”, do Telegram, e também por um desejo antigo de escrever sobre isso sempre que vejo ambientes onde quase todas as consultas têm NOLOCK.

Depois de ler este artigo, você será capaz de entender exatamente como esses dois hints funcionam, vai utilizá-los sabiamente e apenas quando for conveniente. Nada de sair colocando NOLOCK/READPAST em todas as suas consultas, hein!

Caso seu ambiente tenha uma grande concorrência e os locks, blocks e deadlocks sejam frequentes e um problema para você, sugiro que pense em uma abordagem mais completa do que utilizar esse hints, que seria utilizar o modo de isolamento Read Committed Snapshot (RCSI), que permite utilizar o modo Read Commited sem travar as leituras quando ocorrem transações abertas.

Como nem tudo são flores, existem alguns efeitos colaterais ao se utilizar esse modo, como possível queda de performance. Caso queira saber mais sobre ele, sugiro a leitura do artigo “Read Committed Snapshot Isolation: Writers Block Writers (RCSI)“, do grande mestre, Brent Ozar.

Para demonstrar como funciona o NOLOCK e o READPAST, criaremos uma tabela com alguns registros:

IF (OBJECT_ID('dbo.Teste_Nolock') IS NOT NULL) DROP TABLE dbo.Teste_Nolock
CREATE TABLE dbo.Teste_Nolock (
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Nome VARCHAR(100)
)

INSERT INTO dbo.Teste_Nolock
VALUES ('Dirceu'), ('Resende'), ('Consultor'), ('SQL Server'), ('Power BI'), ('SSRS'), ('SSIS'), ('SSAS'), ('Blog')

Tabela atual:

NOLOCK vs READPAST

NOLOCK ou READ UNCOMMITED

Modo de isolamento bem conhecimento pelos DEVs, que permite retornar os dados atuais da tabela sem ter que aguardar o fim das transações em andamento, fazendo a “leitura suja” dos dados. Isto é, caso existam transações alterando os dados das tabelas que estão sendo consultadas, esses registros serão retornados da forma que estão, mesmo que as transações ainda estejam abertas (não foi feito commit ou rollback) e esses dados não sejam definitivos ainda.

Esse tipo de leitura pode ser bem útil em casos de alta concorrência e ocorrência de locks e deadlocks no ambiente, mas tenha em mente os efeitos colaterais de utilizar o hint NOLOCK (equivalente ao comando SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED ou WITH(READUNCOMMITTED)).

Exemplo prático do NOLOCK

Em uma sessão do SQL Server Management Studio (SSMS), execute o comando abaixo:

BEGIN TRANSACTION

UPDATE dbo.Teste_Nolock
SET Nome = 'Teste Nolock'
WHERE Nome = 'Dirceu'

Abra mais uma sessão no SSMS e execute o seguinte código:

BEGIN TRANSACTION

INSERT INTO dbo.Teste_Nolock
VALUES('Teste Nolock2')

Reparem que essas duas sessões abriram uma transação, efetuaram alterações nos dados, mas ainda não foi feito commit e nem o rollback. Ou seja, esses dados modificados ainda podem ser considerados como dados “temporários”, uma vez que eles não são dados commitados e confirmados.

Caso haja algum problema no fluxo ou simplesmente o usuário deseje desfazer essas alterações, ele pode fazer o rollback dessas transações e esses dados inseridos/alterados nunca existiram “oficialmente”.

Mesmo assim, se ainda nesse cenário você abrir uma nova sessão e tentar consultar os dados, verá que a sua sessão ficará aguardando indefinidamente, até que as transações sejam finalizadas com COMMIT ou ROLLBACK.

Isso ocorre porque o modo de leitura padrão do SQL Server é o READ COMMITED. Ou seja, retorna apenas os dados que já foram commitados no banco.

Utilizando o hint NOLOCK, você poderá retornar os dados sem ter que aguardar a finalização dessas transações, mas os dados que ainda nem foram commitados serão retornados como se fossem dados já definitivos:

Caso essas sessões realizem um ROLLBACK dos dados, você acabou consultando dados que nunca existiram, de fato. Isso em um relatório poderia acabar produzindo resultados incorretos.

READPAST

Outra forma de conseguir ler dados de tabelas que estão sendo alteradas por transações em aberto sem ter que esperar o término delas, é utilizar o hint READPAST.

Diferente do NOLOCK, o READPAST não permite leituras sujas (salvo exceções), mas entenda como ele funciona: o READPAST vai retornar apenas os dados que não estão sendo afetados por transações em aberto.

Exemplo prático do NOLOCK – 02

Em uma sessão do SQL Server Management Studio (SSMS), execute o comando abaixo:

BEGIN TRANSACTION

UPDATE dbo.Teste_Nolock
SET Nome = 'Teste Nolock'
WHERE Nome = 'Dirceu'

Abra mais uma sessão no SSMS e execute o comando a seguir:

BEGIN TRANSACTION

INSERT INTO dbo.Teste_Nolock
VALUES('Teste Nolock2')

Agora tentaremos ler os dados utilizando o hint READPAST e ver como os registros não commitados serão retornados:

Utilizando o hint READPAST, você poderá retornar os dados sem ter que aguardar a finalização dessas transações, mas os dados que ainda não foram commitados não serão retornados pelo SELECT, ou seja, registros podem ser ignorados nesse modo de leitura.

Quando você está utilizando funções de agregação, como SUM, MAX, MIN nesse modo de leitura, os valores finais podem ser bem diferentes dos valores reais, já que registros podem ser ignorados.

NOLOCK ou READPAST?

Para finalizar e resumir esse artigo, tanto o NOLOCK quanto o READPAST têm como principal justificativa de uso, a possibilidade de ler dados em tabelas sem ter que esperar que as transações ativas sejam finalizadas, minimizando a ocorrência de locks e blocks, mesmo que esses registros estejam sendo alterados por essas transações.

Ao utilizar o NOLOCK (ou READ UNCOMMITED), caso a tabela tenha 10 linhas e 3 estejam sendo alteradas por UPDATE/DELETE, o comando de SELECT vai retornar todas as 10 linhas, já com os dados “atualizados”. Novas linhas inseridas que ainda não foram commitadas também serão retornadas pelo SELECT.

Ao utilizar o READPAST, caso a tabela tenha 10 linhas e 3 estejam sendo alteradas por UPDATE/DELETE, o comando de SELECT vai retornar apenas as 7 linhas que não estão sendo alteradas. Novas linhas inseridas que ainda não foram commitadas não serão retornadas pelo SELECT.

Referências

• Níveis de isolamento da transação
• Isolation Level no SQL Server
• Transaction Isolation Level, você está usando certo?
• Entendendo Transaction Isolation Level no SQL Server