Engana-me que eu gosto!
O título é um pouco desafiador, mas vamos lá explicar isto timtim por timtim.
Quem me conhece sabe que não morro de amores pelo NOLOCK. Realmente, acho que é uma das “marteladas” muitas vezes evitável.
Há tanto para dizer deste hint… muito já foi dito, basta procurarem por “SQL Server nolock” no vosso motor de busca preferido que encontram muita informação.
O que aqui escrevo é apenas mais uma coisa com que já me deparei e que dá que pensar.
Vamos criar uma simples tabela e inserir alguns registos
create table NoLockDemoIndexed ( id int primary key identity (1,1) , col1 int , col2 int ) go insert into NoLockDemoIndexed (col1, col2) select ABS(CHECKSUM(NEWID()))%1000, ABS(CHECKSUM(NEWID()))%50 from sys.objects a cross join sys.objects b go
Agora fazemos duas simples queries.
select top 10 * from NoLockDemoIndexed select top 10 * from NoLockDemoIndexed where col1 > 400

A resposta é a esperada.
Apesar de não termos ORDER BY os dados vêm na ordem do índice clustered. E… sim, é uma coisa perigosa de pensar, que vêm sempre em ordem. Já lá vamos…
Será que com NOLOCK há alguma alteração?
select top 10 * from NoLockDemoIndexed with (nolock) select top 10 * from NoLockDemoIndexed with (nolock) where col1 > 400

Também nada fora do normal. Não há nenhuma transacção aberta na tabela e ACHAMOS que está tudo igual.
Muitas vezes ouvem-me dizer que a assunção é a mãe de todas as trapalhadas.
Vamos então ASSUMIR que as queries são entregues da mesma maneira com e sem NOLOCK.
Em outra janela abram uma transacçao sem a fecharem e insiram mais algumas linhas
begin transaction go insert into NoLockDemoIndexed (col1, col2) select ABS(CHECKSUM(NEWID()))%1000, ABS(CHECKSUM(NEWID()))%50 from sys.objects a cross join sys.objects b cross join sys.objects c go 3 --commit transaction --fazer só depois de ver os resultados
Voltem à primeira janela e corram o seguinte
select top 10 * from NoLockDemoIndexed go
Naturalmente, fica bloqueada…
select top 10 * from NoLockDemoIndexed where col1 > 400
Naturalmente, também fica bloqueada…
select top 10 * from NoLockDemoIndexed with (nolock) go select top 1000 * from NoLockDemoIndexed with (nolock) where col1 > 400 go
Ora aqui está… NOLOCK em todo o seu esplendor!
As duas primeiras ficam à espera, afinal há uma transaccao aberta. NOLOCK é nosso “amigo” nestes casos, não é? Afinal “serve” para alguma coisa.
Podem ir de novo à outra janela e fazer o commit transaction. Podem-na fechar depois, se quiserem.
Agora corremos um pouco mais de código.
delete from NoLockDemoIndexed where id %2 =0 insert into NoLockDemoIndexed (col1, col2) select ABS(CHECKSUM(NEWID()))%1000, ABS(CHECKSUM(NEWID()))%50 from sys.objects a cross join sys.objects b cross join sys.objects c go 3
Apagamos metade das linhas e inserimos mais algumas. O que acontece agora quando fizermos as mesmas queries, agora com um rowgoal de 1000?
select top 1000 * from NoLockDemoIndexed select top 1000 * from NoLockDemoIndexed with (nolock)

Para já tudo bem, não é? Tudo igual?
Vamos ver no fim do dataset?

Espera lá… um dataset acaba no id 1999 e o outro no 19399?
Até podemos ver onde a query com NOLOCK salta do id 385 para o 185593, enquanto sem NOLOCK segue a sequência que, para nós, nos parece a “lógica”.

E nas que têm predicado?
select top 1000 * from NoLockDemoIndexed where col1 > 400 select top 1000 * from NoLockDemoIndexed with (nolock) where col1 > 400

Começamos bem outra vez, mas…

Mais uma vez acabamos com um dataset completamente diferente
E podemos até ver onde se dá o “salto” de novo. Lembram-se que era no id 385? Como 385 não está neste dataset, “salta” do 383 para o 185593.

Resultados parecidos, não são?
Daqui sai um corolário que todos nós já sabemos, se queremos uma “ordem” de saída de dados só se usarmos um ORDER BY, senão nunca é garantido. NUNCA. Pronto, às vezes. Mas é o que eu chamo de Bobi (depois conto melhor esta anedota).
Mas podemos ver até outra coisa a acontecer se fizermos mais umas operações.
Vamos lá a mais um pouco de F5
delete from NoLockDemoIndexed where id %5 =0 delete from NoLockDemoIndexed where id %7 =0 go insert into NoLockDemoIndexed (col1, col2) select ABS(CHECKSUM(NEWID()))%1000, ABS(CHECKSUM(NEWID()))%50 from sys.objects a cross join sys.objects b cross join sys.objects c go 5 delete from NoLockDemoIndexed where id %3 =0 go insert into NoLockDemoIndexed (col1, col2) select ABS(CHECKSUM(NEWID()))%1000, ABS(CHECKSUM(NEWID()))%50 from sys.objects a cross join sys.objects b cross join sys.objects c go 4 delete from NoLockDemoIndexed where id %5 =0 go insert into NoLockDemoIndexed (col1, col2) select ABS(CHECKSUM(NEWID()))%1000, ABS(CHECKSUM(NEWID()))%50 from sys.objects a cross join sys.objects b cross join sys.objects c go 3 delete from NoLockDemoIndexed where id %7 =0 go insert into NoLockDemoIndexed (col1, col2) select ABS(CHECKSUM(NEWID()))%1000, ABS(CHECKSUM(NEWID()))%50 from sys.objects a cross join sys.objects b cross join sys.objects c go 3
E agora?
select top 1000 * from NoLockDemoIndexed with (nolock) where col1 > 400
Até agora poderíamos pensar que o dataset teria uns “saltinhos” mas que pelo menos vinha sempre segundo a ordenação do índice, neste caso ascendente.
No meu caso de teste, nesta query os id saltam de 21283 para 15899.

Solução?
Código sem bugs e sem assunções. ORDER BY se queremos dados ordenados.
Assumir que o índice vai trazer os dados ordenados… trapalhada.
Assumir que ninguém mais tarde vai pegar no nosso código e meter lá um NOLOCK para “ficar mais rápido” ou “para não bloquear”… trapalhada.
Assumir até que o Query Optimizer vai usar o nosso índice para o nosso select… trapalhada.
“Espera lá, ó António… mas o índice é clustered, vamos sempre fazer scan ou seek à tabela, logo ao índice clustered”, dirão os mais atentos.
Sim, é verdade… mas nunca vos aconteceu que, mais tarde, alguém (ou mesmo nós) crie um nonclustered que o Query Optimizer escolha usar? E que tenha uma ordenação diferente? Nunca? 🙂
Assunção é a mãe de todas as trapalhadas. Já vos tinha dito?
Mais uma para a fogueira do “uma tabela bem indexada é meio caminho andado”. Vamos lá colocar o ORDER BY obrigatório para termos os dados ordenados.
select top 1000 * from NoLockDemoIndexed where col1 > 400 order by id select top 1000 * from NoLockDemoIndexed with (nolock) where col1 > 400 order by id

Agora está “ordenado” mas…

Nem se vê nenhum sort, pois não?