Photo by James Sutton on Unsplash
Nota: o problema descrito abaixo aplica-se a todas as consultas selecionadas, não apenas aquelas adornadas com dicas de NOLOCK. No entanto, o facto de se aplicar a consultas NOLOCK foi uma grande surpresa para mim, daí o título.
muitas pessoas não gostam de NOLOCK (ou seja, o nível de isolamento lido não recomendado) porque pode retornar dados imprecisos., Já vi muitos argumentos a advertir os desenvolvedores de recuperarem leituras não autorizadas por causa de como eles podem devolver dados Sujos, leituras phantom, e leituras não-repetíveis .
eu sabia sobre todos esses problemas acima, mas há um problema que eu nunca ouvi falar até recentemente: NOLOCK pode bloquear outras consultas de correr.
ver o vídeo desta semana no YouTube
vamos recuar e entender por que eu tenho usado tantas vezes NOLOCK no passado. Um exemplo bastante típico de quando eu uso NOLOCK é quando eu quero deixar uma consulta correr durante a noite para retornar algum conjunto grande de dados., Estou bem com algumas inconsistências nos dados (de leituras sujas, etc…). Minha principal preocupação é que eu não quero que a consulta de longa duração fique no caminho de outros processos.
eu sempre pensei que NOLOCK era uma solução perfeita para este cenário porque ele nunca bloqueia os dados que lê-os resultados podem não ser perfeitos, mas pelo menos a consulta não vai afetar negativamente qualquer outro processo no servidor.
é aqui que a minha compreensão do NOLOCK estava errada: embora o NOLOCK não bloqueie os dados do nível da linha, irá remover um bloqueio de estabilidade do esquema.,
um bloqueio de estabilidade de esquemas (Sch-s) impede a estrutura de uma tabela de mudar enquanto a consulta está em execução. Todas as declarações selecionadas, incluindo as do nível de isolamento lido por não-internado/NOLOCK, retire uma fechadura Sch-S. Isso faz sentido porque nós não queremos começar a ler dados de uma tabela e, em seguida, ter a estrutura da coluna mudar a meio caminho através da recuperação de dados.
no entanto, isto também significa que pode haver algumas operações que ficam bloqueadas por uma fechadura Sch-S. Por exemplo, qualquer comando que solicite uma modificação do esquema (Sch-M) bloqueia neste cenário., que comandos pedem Fechaduras Sch-m?
coisas como uma reconstrução de índice ou tabela sp_recompile. Estes são os tipos de comandos que executam em meus trabalhos de manutenção noturna que eu estava tentando evitar ferir usando NOLOCK em primeiro lugar!
para reiterar, eu costumava pensar que usar a dica NOLOCK era uma ótima maneira de evitar o bloqueio durante as consultas de longa duração., No entanto, acontece que minhas consultas NOLOCK estavam realmente bloqueando minhas tarefas de índice noturno (todos os blocos de consultas selecionadas neste exemplo, mas eu acho que o NOLOCK para ser particularmente enganoso), que, em seguida, fez com que outras declarações selecionadas para ficar bloqueado também! vamos dar uma olhada nisso em ação. Aqui eu tenho uma consulta que cria um banco de dados, tabela, e então executa uma consulta de longa duração com NOLOCK:
agora, enquanto esse bilhão de linha de leitura está ocorrendo, nós podemos verificar que a consulta tirou um bloqueio Sch-s olhando para sys.,dm_tran_locks:
SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'
Enquanto o que está em execução, se tentamos recriar um índice, que reconstruir é bloqueado (mostrado como uma ESPERA):
USE GOALTER INDEX IX_Id ON dbo.Test REBUILD
a Nossa reconstrução de índice de consulta permanecerá bloqueada até que o nosso bilhões de linha NOLOCK SELECIONE a consulta terminar a execução (ou morto). Isso significa que a consulta que eu pretendia ser completamente discreta está agora bloqueando meu trabalho de manutenção de índice noturno de correr.,
ainda pior, quaisquer outras consultas que tentem executar após a consulta de reconstrução (ou quaisquer outros comandos que requeiram um bloqueio Sch-M) também vão ficar bloqueadas! If I try to run a simple COUNT ( * ) query:
USE GOSELECT COUNT(*) FROM dbo.Test
bloqueado! Isto significa que não só a minha consulta inicial NOLOCK está a fazer com que os meus trabalhos de manutenção de reconstrução do Índice esperem, como a fechadura Sch-m colocada pelo trabalho de manutenção de reconstrução está a fazer com que quaisquer consultas subsequentes naquela mesa fiquem bloqueadas e sejam forçadas a esperar também., Acabei de descarrilar a oportunidade do meu trabalho de manutenção e consultas subsequentes com uma declaração de bloqueio NOLOCK!
soluções
infelizmente este é um problema difícil e não há um único tamanho-fits-all remédio.
Solution #1: Don’t run long running queries
i could avoid running long queries at night when they might run into my index maintenance jobs., Isso evitaria que os trabalhos de manutenção de índices e as consultas subsequentes se atrasassem, mas isso significa que minha consulta inicial de bilhão de linhas selecionadas teria que correr mais cedo, impactando negativamente o desempenho do servidor durante uma hora do dia potencialmente mais movimentada.,
Solução #2: Use WAIT_AT_LOW_PRIORITY
a partir de 2014, o que eu poderia fazer uma recompilação de índice online com o WAIT_AT_LOW_PRIORITY conjunto de opções:
ALTER INDEX IX_Id ON dbo.Test REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES , ABORT_AFTER_WAIT = BLOCKERS)))
Esta consulta basicamente dá qualquer bloqueio SELECIONE consultas atualmente em execução 1 minuto para terminar de executar ou de outra forma, essa consulta irá matá-los e, em seguida, executar a reconstrução do índice. Alternativamente, poderíamos também ter configurado ABORT_AFTER_ wait = SELF e a consulta de reconstrução se mataria, permitindo que a linha de NOLOCK billion selecione para terminar a execução e não impedindo quaisquer outras consultas de correr.,
Esta não é uma grande solução porque significa que ou a consulta de longa duração é morta ou a reconstrução do índice é morta.
Solução #3: reconstruir se não houver Sch-s, reorganizar caso contrário
uma solução programática pode ser escrita que tenta reconstruir o índice, mas recua para reorganizar se souber que terá de esperar por um bloqueio Sch-M.
eu criei a placa da caldeira abaixo como um ponto de partida, mas o céu é o limite com o que você pode fazer com ele (e.g., criar um ciclo de enquanto para verificar o bloqueio a cada x segundos, criar um tempo-limite para quando o programa deve parar de tentar reconstruir e apenas reorganizar, etc…)
Esta solução é o meu favorito porque:
- Ad hoc tempo de execução de consultas não ser morto (todas as de que o tempo gasto de processamento de não ir para o lixo)
- Outras consultas select não são bloqueados pelo Sch-M tentativa de bloqueio por RECONSTRUIR
- manutenção de índices ainda ocorre, mesmo se ele acaba sendo uma REORGANIZAR em vez de uma RECONSTRUÇÃO
