tabela do SQL Server dicas são um tipo especial de comando explícito que é usado para substituir o comportamento padrão do SQL Server otimizador de consulta durante a consulta T-SQL execução, Isto é conseguido através da aplicação de um bloqueio específico de método, um índice específico ou processamento de consulta de funcionamento, tal índice de procura ou verificação de tabela, para ser usado pelo otimizador do SQL Server para construir o plano de execução de consulta., As dicas da tabela podem ser adicionadas à cláusula FROM da consulta T-SQL, afetando a tabela ou a visão que é referenciada apenas na cláusula FROM.
uma das dicas de tabela mais usadas nas declarações T-SQL selecionadas é a dica com (NOLOCK). O nível padrão de isolamento de transação no servidor SQL é o nível de isolamento de compromisso lido, no qual a recuperação dos dados em mudança será bloqueada até que essas alterações sejam comprometidas., A dica da tabela com (NOLOCK) é usada para sobrepor o nível de isolamento da transação padrão da tabela ou as tabelas dentro da visualização em uma consulta específica, permitindo que o usuário recupere os dados sem ser afetado pelas bloqueios, nos dados solicitados, devido a outro processo que está alterando-o. Desta forma, a consulta vai consumir menos memória em manter bloqueios contra esses dados. Além disso, nenhum impasse ocorrerá contra as consultas, que estão solicitando os mesmos dados daquela tabela, permitindo um nível mais elevado de concorrência devido a uma pegada menor., Em outras palavras, a dica da tabela com (NOLOCK) recupera as linhas sem esperar pelas outras consultas, que estão lendo ou modificando os mesmos dados, para terminar o seu processamento. Isto é semelhante ao nível de isolamento de transação lido por persistir, que permite à Consulta ver as alterações de dados antes de cometer a transação que está mudando. O nível de isolamento da transação pode ser definido globalmente no nível de conexão usando o comando SET TRANSACTION ISOLATION LEVEL T-SQL, como veremos mais adiante neste artigo.,
embora a dica da tabela NOLOCK, semelhante a todas as outras dicas de tabela, pode ser usada sem usar a palavra-chave com, a Microsoft anunciou que omitir a palavra-chave com é uma característica desactualizada e será removida das futuras versões do Microsoft SQL Server. Com isso dito, é melhor incluir a palavra-chave com especificar as dicas da tabela. Um dos benefícios de usar a palavra-chave com é que você pode especificar várias dicas de tabela usando a palavra-chave com a mesma tabela.
Em Geral, usar dicas explícitas de tabela frequentemente é considerado como uma má prática que você deve geralmente evitar., Para a sugestão de tabela NOLOCK especificamente, a leitura de dados não confirmados de que poderá ser revertida após a leitura pode levar a uma leitura Suja, o que pode ocorrer quando ler os dados que estão sendo modificados ou excluídos durante a dados não confirmados de leitura, de modo que os dados lidos podem ser diferentes, ou nunca ter existido.
a dica da tabela com (NOLOCK) também leva a leituras não Repeáveis; esta leitura ocorre quando é necessário ler os mesmos dados várias vezes e os dados mudam durante estas leituras. Neste caso, você vai ler várias versões da mesma linha.,
leitura fantasma também pode ser um resultado de usar a dica com (NOLOCK) tabela, na qual você vai obter mais registros quando a transação que está inserindo novos registros é rolada para trás, ou menos registros quando a transação que está removendo dados existentes é rolada para trás. Outro problema que pode ocorrer quando outras transações movem dados que você ainda não leu para um local que você já digitalizou, ou adicionou novas páginas para o local que você já digitalizou. Neste caso, você vai perder estes registros e não vai vê-lo no resultado retornado., Se outra transação mover os dados que você já digitalizou para um novo local que você ainda não Leu, você vai ler os dados duas vezes. Além disso, como os dados solicitados podem ser movidos ou apagados durante o seu processo de leitura, o erro abaixo pode ser enfrentado:
Msg 601, Nível 12, Estado 1
não poderia continuar a digitalizar com NOLOCK devido ao movimento dos dados.
A dica da tabela com (NOLOCK) é uma boa ideia quando o sistema usa transações explícitas pesadamente, o que bloqueia a leitura de dados com muita frequência., A dica da tabela com (NOLOCK) é usada quando se trabalha com sistemas que aceitam dados fora de sincronia, como os sistemas de relatórios.,
Para entender o uso do COM (NOLOCK) sugestão de tabela, praticamente, vamos criar uma nova tabela, utilizando a TABELA de criação de instrução T-SQL abaixo:
Depois de criar a tabela, vamos preenchê-lo com 100 mil linhas para fins de teste, usando o ApexSQL Gerar, SQL gerador de dados de teste, como mostrado na instantâneo abaixo:
uma Vez que a mesa está pronta, vamos simular um cenário de bloqueio, em que uma transação de atualização será executado dentro de uma transação que irá começar e não consolidada ou revertida.,
Com os dados da tabela bloqueado por transação, vamos executar outra instrução SELECT, em sessão SQL número 54, que recupera dados do LockTestDemo tabela, usando a instrução SELECT abaixo:
|
1
|
SELECT * FROM LockTestDemo
|
Você vai ver que o anterior instrução SELECT vai levar um longo tempo sem recuperar os registros.,ocking que consulta SELECT usando o comando sp_who2 com o número de sessão para tanto a ESCOLHA e as instruções de ATUALIZAÇÃO:
|
1
2
3
|
sp_who2 53
IR
sp_who2 54
|
O resultado irá mostrar-lhe que, anteriormente transação aberta é não realizar qualquer ação, como a ATUALIZAÇÃO de instrução foi executada com êxito., Mas devido ao fato de que a transação não está comprometida ou rolada para trás ainda, ele ainda bloqueia outras consultas que estão tentando obter dados dessa tabela. E a declaração de selecção que está a correr na sessão 54 é bloqueada pela transacção que está a correr na sessão 53, Como mostra o resultado abaixo:
a instrução de selecção anterior irá continuar à espera que a transacção seja morta, cometida ou rolada para trás, a fim de obter as linhas solicitadas a partir dessa tabela., o comando COMMIT ou ROLLBACK sob a mesma sessão da transação, se for o caso, conforme mostrado abaixo:
uma Vez que o bloqueio é liberado, você vai ver que as linhas solicitados será obtida a partir da instrução SELECT diretamente como mostra os resultados abaixo:
A solução anterior não é sempre preferível aplicável, por exemplo, quando a transação que está bloqueando a nossa consulta é fundamental e não é fácil de ser morto ou revertida, ou quando você não tem controle sobre outras transacções dentro do banco de dados., Neste caso, a dica da tabela com (NOLOCK) é útil aqui, se você puder tolerar o risco de leituras sujas ou inconsistência de dados. Como mencionado anteriormente, a dica da tabela com (NOLOCK) permite-lhe ler os dados que foram alterados, mas ainda não estão comprometidos com a base de dados., Se você executar a mesma instrução SELECT sem matar, de confirmar ou reverter a transação de ATUALIZAÇÃO, mas, desta vez adicionando o COM (NOLOCK) sugestão de tabela para o nome da tabela na instrução SELECT, como mostrado abaixo:
|
1
|
SELECT * FROM LockTestDemo COM (NOLOCK)
|
em Seguida, verificar a instrução SELECT de status usando o comando sp_who2., Você vai ver que a consulta está sendo executado sem aguardar a ATUALIZAÇÃO de transação a ser concluída com êxito e solte o fecho na mesa, como mostrado na instantâneo abaixo:
WITH (NOLOCK) sugestão de tabela funciona da mesma forma que o READUNCOMMITTED sugestão de tabela, permitindo-nos recuperar os dados é alterado, mas não se comprometeu ainda.,iv>
|
1
|
SELECT * FROM LockTestDemo COM (READUNCOMMITTED)
|
Recuperar os dados solicitados diretamente, sem aguardar a instrução UPDATE para liberar o bloqueio realizado sobre a mesa, retornando o mesmo resultado, como mostrado no conjunto de resultados abaixo:
se Levar em consideração que, COM o (NOLOCK) e READUNCOMMITTED dicas de tabela só podem ser utilizados com as instruções SELECT.,
Esta consulta irá também obter os mesmos dados diretamente, sem a utilização de qualquer sugestão de tabela e, sem aguardar a instrução UPDATE para liberar o bloqueio realizado sobre a mesa, como mostrado no conjunto de resultados abaixo:
a Partir de resultados anteriores, você pode pensar que esta é a solução perfeita para esses cenários, onde você irá obter os dados solicitados mais rápido, sem esperar por outras operações de ser cometido, tendo o risco de não ter dados precisos., Mas será que a consulta selecionada que está usando a dica com (NOLOCK) afeta negativamente outros processos no servidor SQL? Para obter a resposta, vamos primeiro verificar que tipo de bloqueios a dica da tabela com (NOLOCK) será concedida durante a sua execução.,=”26bd69ce5a”>
|
1
|
sp_lock 54
|
Você vai ver o resultado que a consulta que está usando o COM (NOLOCK) sugestão de tabela será concedida S e Sch-S fecho tipos, como mostra o resultado a seguir:
a Partir do resultado anterior, você vai ver que COM o (NOLOCK) sugestão de tabela será concedido acesso compartilhado (S) de bloqueio no nível do banco de dados., O bloqueio de acesso compartilhado (s) é usado para a operação de leitura, permitindo que as transações simultâneas leiam dados sob controle de concorrência pessimista, impedindo que outras transações de modificar o recurso bloqueado, enquanto bloqueios compartilhados existem nesse recurso, até que o bloqueio é liberado assim que a operação de leitura completa.
o segundo tipo de bloqueio que é concedido à consulta usando a dica da tabela com (NOLOCK) é o bloqueio de estabilidade do esquema (Sch-s)., Este bloqueio não impedirá qualquer outra transação de acessar os recursos, exceto para as operações DDL simultâneas, e as operações DML simultâneas que adquirem bloqueios de modificação de esquema (Sch-m) na mesma tabela, que serão bloqueadas enquanto a consulta está em execução. Isso realmente faz sentido, como você não precisa começar a ler os dados da tabela, em seguida, outra transação muda a estrutura dessa tabela durante o seu processo de recuperação de dados., O motor de banco de dados do servidor SQL usa o bloqueio de modificação do esquema (Sch-M) enquanto processa os comandos da linguagem de definição de dados (DDL), tais como adicionar uma nova coluna, largar uma coluna existente, largar ou reconstruir índices, para impedir o acesso simultâneo à tabela, até que o bloqueio seja liberado.
a minha pesquisa NOLOCK está a bloquear!
isto significa que a nomenclatura de NOLOCK nem sempre é 100% precisa. Use of the WITH (NOLOCK) table hint, that holds schema stability (Sch_S) lock, can block other quereries that attempt to acquire a schema modification (Sch-M) lock on that table., É uma questão crítica que você deve levar em consideração se há muitos usuários de execução de consultas SELECT usando o COM (NOLOCK) sugestão de tabela, impedindo-o de efectuar quaisquer alterações para o esquema de tabela ou manutenções na tabela de índices, sendo bloqueado pelo esquema de estabilidade (Sch_S) de bloqueio.,>
Ao mesmo tempo, vamos correr abaixo de consulta, que está caindo um índice na mesma tabela e criá-lo novamente, em sessão de número 58:
em Seguida, verificar o estado das duas consultas utilizando o comando sp_who2, você vai ver o resultado, a instrução SELECT que está usando o COM (NOLOCK) sugestão de tabela e de executar a sessão de número 53, está travando a QUEDA/CREATE INDEX processo de execução em sessão de número 58, conforme mostrado claramente abaixo:
Se a gente verificar os bloqueios que são executadas por cada consulta, usando o comando sys.,dm_tran_locks objeto do sistema como na consulta abaixo:
|
1
2
3
|
SELECT *
a PARTIR sys.dm_tran_locks
ONDE resource_type = ‘OBJETO’
|
Você vai ver que, a QUEDA/CREATE INDEX processo de execução em sessão de número 58 está esperando para adquirir a modificação de esquema (Sch-M) tipo de bloqueio., Isso ocorre devido ao fato de que, a modificação de esquema (Sch-M) bloqueio não pode ser adquirido, enquanto o esquema de estabilidade (Sch_S) bloqueio que já é concedido para a instrução SELECT execução em sessão de número 53, já existe, como mostrado na instantâneo abaixo:
a Minha NOLOCK consulta está bloqueado!
inversamente, uma vez que a dica da tabela com (NOLOCK) adquire o tipo de bloqueio da estabilidade do esquema (Sch-s), a declaração de selecção que está a usar a dica da tabela com (NOLOCK) será bloqueada se for realizada uma modificação do esquema nessa tabela.,
|
1
2
3
|
sp_who2 53
IR
sp_who2 54
|
Você vai ver que a instrução SELECT execução em sessão 54 é bloqueada pela instrução ALTER TABLE execução em sessão de 54, como mostrado abaixo:
em Seguida, verificar os bloqueios que são executadas por cada consulta, usando o comando sys.,dm_tran_locks system object as in the query below:
|
1
2
3
4
|
SELECT *
FROM sys.,que, a instrução SELECT que está usando o COM (NOLOCK) sugestão de tabela e de executar em sessão de número 54, estará esperando para adquirir estabilidade do esquema (Sch_S) bloqueio, devido ao fato de que a estabilidade do esquema (Sch-S) bloqueio não pode ser adquirido, enquanto a modificação de esquema (Sch_M) de bloqueio, que já é concedido para a instrução ALTER execução em sessão de número 53, já existe, como mostrado na instantâneo abaixo:
Você pode imaginar a situação, quando você estiver agendando grande número de relatórios à noite, que estão usando a COM (NOLOCK) sugestão de tabela apenas para ser seguro., Ao mesmo tempo, há trabalhos de manutenção que também estão programados para reconstruir índices fortemente fragmentados na mesma tabela! Há um número de melhores práticas e sugestões que você pode seguir, a fim de evitar os problemas que você pode enfrentar ao usar com (NOLOCK) dica tabela. Tais sugestões incluem:
Ahmad Yaseen é um Microsoft Big Data engenheiro com profundo conhecimento e experiência em SQL BI, Banco de dados SQL Server de Administração e Desenvolvimento de campos., he is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Também, ele está contribuindo com suas dicas de SQL em muitos blogs., Ver todos os posts por Ahmad Yaseen posts mais Recentes por Ahmad Yaseen (ver todas)
|
Deixe uma resposta