INF: Compreender e resolver problemas de bloqueio do SQL Server

Traduções de Artigos Traduções de Artigos
Artigo: 224453 - Ver produtos para os quais este artigo se aplica.
Expandir tudo | Reduzir tudo

Nesta página

Sumário

Neste artigo, o termo "ligação" refere-se a uma sessão única iniciada da base de dados. Cada ligação aparece como um ID de sessão (SPID). Cada um destes SPID é frequentemente referida como um processo, embora não seja um contexto de processo separado no sentido de habitual. Em vez disso, cada SPID consiste os recursos do servidor e estruturas de dados necessárias para servir os pedidos de uma única ligação de um determinado cliente. Uma aplicação cliente individual poderá ter um ou mais ligações. Na perspectiva do SQL Server, não existe qualquer diferença entre várias ligações a partir de uma aplicação de cliente único num computador cliente individual e várias ligações de várias aplicações de cliente ou de vários computadores clientes. Uma ligação pode bloquear outra ligação, independentemente do facto procedam da mesma aplicação ou aplicações separadas no dois computadores diferentes.

Mais Informação

Bloquear é uma característica inevitável de qualquer sistema de gestão da base de dados relacionais (RDBMS) com bloqueio baseadas em concorrência. No SQL Server, o bloqueio ocorre quando um SPID detém um bloqueio num recurso específico e um SPID segundo tenta adquirir um tipo de bloqueio em conflito no mesmo recurso. Normalmente, o período de tempo para os quais o SPID primeiro bloqueia o recurso é muito pequeno. Quando se liberta o bloqueio, a segunda ligação é livre de adquirir próprio bloqueio no recurso e continuar o processamento. Isto é normal e pode ocorrer muitas vezes ao longo do curso de um dia sem afectar perceptíveis no desempenho do sistema.

O contexto de duração e a transacção de uma consulta determinar quanto tempo respectivos bloqueios são mantidos e, assim, seu impacto sobre outras consultas. Se a consulta não for executada dentro de uma transacção (e não as sugestões de bloqueio são utilizadas), os bloqueios para instruções SELECT só serão manipulados num recurso, no momento na realidade está a ser lido e não para a duração da consulta. Para instruções INSERT, UPDATE e DELETE, os bloqueios sejam mantidos para a duração da consulta, tanto por consistência dos dados e para permitir a consulta para ser revertida se necessário.

Para consultas executadas numa transacção, a duração para as quais são mantidos os bloqueios são determinados pelo tipo de consulta, o nível de isolamento de transacções e bloquear ou não as sugestões são utilizadas na consulta. Para obter uma descrição de bloqueio, sugestões de bloqueio e níveis de isolamento de transacção, consulte os seguintes tópicos no SQL Server Books Online:
  • Bloqueio no motor de base de dados
  • Bloqueio de personalização e controlo de versão de linha
  • Modos de bloqueio
  • Compatibilidade de bloqueio
  • Linha com o controlo de versão níveis de isolamento no motor de base de dados
  • Controlo de transacções (motor de base de dados)
Quando o bloqueio e bloqueio de aumento até ao ponto onde existe um efeito prejudicial no desempenho do sistema, é normalmente devido a uma das seguintes razões:
  • Um SPID detém bloqueios num conjunto de recursos durante um período alargado de tempo antes de libertá-los. Este tipo de bloqueio resolve-se ao longo do tempo, mas pode provocar degradação do desempenho.
  • Um SPID detém bloqueios num conjunto de recursos e nunca liberta-los. Este tipo de bloqueio não resolve propriamente dito e impede o acesso aos recursos afectados indefinidamente.
No primeiro cenário acima, o problema de bloqueio resolve-se ao longo do tempo como o SPID liberta os bloqueios. No entanto, a situação pode ser muito fluida como diferente SPIDs causa bloqueio de recursos diferentes ao longo do tempo, criando um alvo de movimento. Por este motivo, estas situações poderá ser difícil para resolução de problemas relacionados com a utilização do SQL Server Enterprise Manager ou consultas SQL individuais. Os resultados de situação segundo num estado consistente que pode ser mais fácil diagnosticar.

Informações de bloqueio de recolha

Para neutralizar a dificuldade de resolução de problemas de bloqueio, um administrador de base de dados pode utilizar scripts SQL que monitorizam constantemente o estado de bloqueio e bloqueio no SQL Server. Estes scripts podem fornecer instantâneos de instâncias específicas ao longo do tempo, conduzindo a uma visão global do problema. Para obter uma descrição sobre como monitorar o bloqueio com scripts SQL, consulte os seguintes artigos na base de dados de conhecimento da Microsoft:
271509Como monitorar o bloqueio no SQL Server 2005 e SQL Server 2000
Os scripts neste artigo vão efectuar as tarefas abaixo. Sempre que possível, o método para obter estas informações a partir do SQL Server Management Studio é dado.
  1. Identificar o SPID (ID de sessão) no topo da cadeia de bloqueio e a instrução SQL.
    Para além de utilizando os scripts da base de dados de conhecimento da mencionados anteriormente, pode identificar o chefe da cadeia de bloqueio utilizando funcionalidades que são fornecidas através de SQL Server Management Studio. Para tal, utilize um dos seguintes métodos:
    • Clique com o botão direito do rato no objecto de servidor, expanda relatórios, expanda Relatórios padrão e, em seguida, clique em actividade ? todas as transacções de bloqueio. Este mapa mostra as transacções no topo da cadeia de bloqueio. Se expandir a transacção, o mapa mostra as transacções que são bloqueadas pela transacção cabeça. Este relatório também mostrará o "bloqueio de instrução de SQL" e a "declaração de SQL bloqueado".
    • Utilize DBCC INPUTBUFFER(<spid>) para localizar o último extracto que foi submetido por um SPID.
  2. Encontrar o nível de aninhamento de transacção e o processo de estado de bloqueio SPID.
    O nível de aninhamento de transacção de um SPID está disponível à variável global @@ TRANCOUNT. No entanto, pode ser determinada a partir fora o SPID consultando a tabela sysprocesses do seguinte modo:

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>
    go
    						
    O valor devolvido é o valor @@ TRANCOUNT o SPID. Isto mostra o nível de aninhamento de transacção para o bloqueio SPID, que por sua vez pode explicar porque é que este possui bloqueios. Por exemplo, se o valor for superior a zero, o SPID é no meio de uma transacção (caso em que espera-se que mantém a alguns bloqueios que tiver adquirido, dependendo do nível de isolamento de transacções).

    Também pode verificar se qualquer transacção aberta a longo prazo existe na base de dados utilizando o comando DBCC OPENTRAN database_name.

Recolha de informações de rastreio do SQL Server Profiler

Além das informações acima referidas, de é frequentemente necessário capturar um rastreio Profiler das actividades no servidor para cuidadosamente investigar um problema de bloqueio do SQL Server. Se um SPID instruções múltiplas numa transação for executado, apenas o último statementthat foi submetido mostrará o relatório, memória intermédia de entrada ou saída de monitor de actividade. No entanto, um dos comandos anteriores pode ser o motivo bloqueios ainda estão a ser mantidos. Um rastreio Profiler permite-lhe ver todos os comandos executados por um SPID na transacção actual. Os passos que se seguem ajudam-na configurar o SQL Server Profiler para capturar um rastreio.
  1. Abra o SQL Server Profiler.
  2. No menu ficheiro, aponte para Novo e, em seguida, clique em Rastrear.
  3. No separador <a0>Geral</a0>, especifique um nome de rastreio e um nome de ficheiro para capturar os dados.

    Importante O ficheiro de rastreio deve ser escrito num disco rápida local ou partilhado. Evite o rastreio para uma unidade de disco ou rede lenta. Além disso certifique-se de que Server processa rastreio dados estão seleccionados.
  4. No separador Eventos de selecção, clique para seleccionar as caixas de verificação Mostrar todas as colunas e Mostrar todos os eventos.
  5. No separador Eventos de selecção, adicione os tipos de eventos que são apresentados no quadro 1 para o rastreio.

    Além disso, pode incluir os tipos de eventos adicionais que estão listados no quadro 2 para obter mais informações. Se estiver a executar num ambiente de produção de grande volume, pode optar por utilizar apenas os eventos no quadro 1, tal como estão normalmente suficientes para resolução de problemas relacionados com a maior parte dos problemas de bloqueio. Incluindo os eventos adicionais no quadro 2 pode facilitar a rapidamente determinar a origem de um problema (ou estes eventos podem ser necessários para identificar a culpado instrução num procedimento multi-statement). No entanto, incluindo eventos no quadro 2 irá também adicionar a carga sobre o sistema e aumentar o tamanho de saída de rastreio.
o quadro 1: tipos de eventos
Reduzir esta tabelaExpandir esta tabela
TítuloEvento
Erros e avisosExcepção
Erros e avisosAtenção
Auditoria de segurançaInício de sessão de auditoria
Auditoria de segurançaEncerramento de sessão de auditoria
SessõesLigação existente
Procedimentos armazenadosRPC: início
TSQLSQL:BatchStarting

Quadro 2: tipos de eventos adicionais
Reduzir esta tabelaExpandir esta tabela
TítuloEvento
TransacçõesDTCTransaction
TransacçõesSQLTransaction
Procedimentos armazenadosRPC: concluída
TSQLSQL:BatchCompleted
Procedimentos armazenadosSP:StmtStarting
Procedimentos armazenadosSP:StmtCompleted

Para mais informações sobre como utilizar o SQL Server Profiler, consulte SQL Server Books Online.

Identificar e resolver Common cenários de bloqueio

Ao examinar as informações acima referidas, pode determinar a causa da maioria dos problemas de bloqueio. O resto deste artigo é uma discussão sobre como utilizar estas informações para identificar e resolver alguns cenários comuns de bloqueio. Esta discussão pressupõe que tiver utilizado os scripts bloqueio artigo 271509 (referenciados anteriormente) para capturar informações sobre os SPID bloqueio e efectuou um rastreio Profiler com os eventos descritos acima.

Visualizar a saída do script de bloqueio

Examine os resultados sys.sysprocesses para determinar os chefes das cadeias de bloqueio
Se não tiver especificado modo rápido para os scripts de bloqueio, existirá uma secção intitulada "SPIDs lidera o bloqueio de cadeias" que lista os SPID que estão a bloquear outros SPID por parte do script de saída.
SPIDs at the head of blocking chains
se especificou a opção rápida, ainda pode determinar as cabeças de bloqueio por olhar para a saída sys.sysprocesses e seguinte a hierarquia do SPID é reportado na coluna bloqueada.
Examine os resultados sys.sysprocesses para obter informações sobre os SPID no topo da cadeia de bloqueio.
É importante avaliar os seguintes campos sys.sysprocesses:

Estado

Esta coluna mostra o estado de um SPID específico. Normalmente, um estado no modo de suspensão indica que o SPID tiver concluído sua execução e que aguarda a aplicação a enviar outra consulta ou batch. Um estado runnable, Executar ou sos_scheduler_yield indica que o SPID está actualmente a processar uma consulta. A tabela seguinte dá breves explicações dos vários valores de estado.
Reduzir esta tabelaExpandir esta tabela
EstadoSignifica
FundoO SPID está a executar uma tarefa de segundo plano, tais como a detecção de impasse.
No modo de suspensãoO SPID não está em execução actualmente. Isto normalmente indica que o SPID aguarda um comando da aplicação.
Em execuçãoO SPID está actualmente a ser executado num programador.
RunnableO SPID encontram-se a fila runnable de um programador e obter programador tempo a aguardar.
Sos_scheduler_yieldO SPID estava em execução, mas -voluntariamente tem produziram seu ciclo de tempo sobre o programador para permitir que outro SPID obter as horas de programador.
SuspensoO SPID está a aguardar um evento, tal como um bloqueio ou de uma fechadura.
AnulaçãoO SPID encontram-se a anulação de uma transacção.
DefwakeupIndica que o SPID está à espera de um recurso que está em vias de a ser libertada. O campo waitresource deverá indicar o recurso em questão.

Open_tran

Este campo indica o nível de aninhamento de transacção do SPID. Se este valor for maior que 0, o SPID é uma transacção aberta e pode ser mantendo bloqueios adquiridos por qualquer declaração na transacção.

Lastwaittype, waittype e waittime

O campo lastwaittype é uma representação em cadeia do campo waittype, que é uma coluna binária interna reservada. Se o waittype é 0 x 0000 por meio, o SPID não está a aguardar por nada e o valor lastwaittype indica o último waittype que tinha o SPID. Se o waittype não for zero, o valor lastwaittype indica o actual waittype do SPID.

Para obter uma breve descrição dos diferentes lastwaittype e waittype valores, consulte o seguinte artigo da base de dados de Conhecimento Microsoft:
822101Descrição das colunas na tabela do SQL Server 2000 e SQL Server 2005 master.dbo.sysprocesses waittype e lastwaittype
Para mais informações sobre sys.dm_os_wait_stats, consulte SQL Server Books Online.

O valor waittime pode ser utilizado para determinar se o SPID está a fazer progresso. Quando uma consulta contra a tabela sys.sysprocesses devolve um valor na coluna waittime que for menor do que o valor waittime de uma consulta de sys.sysprocesses anterior, isto indica que o bloqueio anterior foi adquirido e libertado e está agora a aguardar um bloqueio de novo (partindo do princípio waittime diferente de zero). Isto pode ser verificado através da comparação waitresource entre sys.sysprocesses saída.

Waitresource

Este campo indica o recurso que está a aguardar um SPID. A tabela seguinte lista formatos waitresource comuns e respectivo significado:
Reduzir esta tabelaExpandir esta tabela
RecursoFormatoExemplo
TabelaDatabaseID:ObjectID:IndexIDSEPARADOR: 5:261575970:1
Neste caso, base de dados do ID de 5 é a base de dados de exemplo pubs e 261575970 ID de objecto é a tabela de títulos e 1 é o índice clusterizado.
PáginaDatabaseID:FileID:PageIDPÁGINA: 5:1:104
Neste caso, a base de dados ID 5 é pubs, ficheiro ID 1 é o ficheiro de dados principal e página 104 é uma página que pertencem a tabela de títulos.

Para identificar o id de objecto que pertence a página, utilize o comando DBCC PAGE (dbid, fileid, pageid, output_option) e observe o m_objId. Por exemplo:
DBCC TRACEON ( 3604 )
DBCC PAGE ( 5 , 1 , 104 , 3 )
ChaveDatabaseID:Hobt_id (valor de hash para a chave de índice)CHAVE: 5:72057594044284928 (3300a4f361aa)

Neste caso, a base de dados ID 5 é Pubs, Hobt_ID 72057594044284928 corresponde a não agrupado index_id 2 para o id de objecto 261575970 (tabela de títulos). Utilize a vista de catálogo sys.partitions para associar o hobt_id a um id de índice específica e id de objecto. Não é possível a unhash o hash de chave de índice para um valor de chave do índice específico.
LinhaDatabaseID:FileID:PageID:Slot(row)RID: 5:1:104:3

Neste caso, a base de dados ID 5 é pubs, ficheiro ID 1 é o ficheiro de dados primário, página 104 é uma página que pertencem a tabela de títulos e ranhura 3 indica a posição da linha na página.
CompilaçãoDatabaseID:ObjectID [[compilação]]TABULAÇÃO: 5:834102012 [[compilação]] esta não é um bloqueio de tabela, mas sim uma compilação bloquear um procedimento armazenado. ID da base de dados 5 é pubs, objecto ID 834102012 usp_myprocedure procedimento armazenado. Consulte 263889 de artigo da base de dados de conhecimento para obter mais informações bloqueio causada por bloqueios de compilação de.
Outras colunas

As restantes colunas sys.sysprocesses podem fornecer informações para a raiz de um problema bem. Sua utilidade varia consoante as circunstâncias do problema. Por exemplo, pode determinar se o problema acontece apenas de determinados clientes (nome de anfitrião), determinadas bibliotecas de rede (net_library), quando o lote último apresentaram um SPID foi (last_batch), e assim sucessivamente.
Examine os resultados DBCC INPUTBUFFER.
Para qualquer SPID no topo de uma cadeia de bloqueio ou com um waittype diferente de zero, o bloqueio script executará DBCC INPUTBUFFER para determinar a consulta actual para esse SPID.

Em muitos casos, esta é a consulta que está a causar os bloqueios que estão a bloquear outros utilizadores a realizar. No entanto, se for o SPID dentro de uma transacção, os bloqueios podem tenham sido adquiridos por uma consulta executada anteriormente, não a animação actual. Por conseguinte, também deverá ver a saída do Profiler para o SPID, não apenas a inputbuffer.

Nota Uma vez que o script de bloqueio é constituída por vários passos, é possível que um SPID pode aparecer na primeira secção como chefe de uma cadeia de bloqueio, mas no momento em que a consulta DBCC INPUTBUFFER for executada, já não é bloqueio e o INPUTBUFFER não for capturada. Isto indica que o bloqueio está a processar-se para esse SPID e não pode ser um problema ou. Nesta altura, pode utilizar a versão do script bloqueio rápida para tentar garantir a que capturar o inputbuffer antes de que limpa (embora não seja ainda nenhuma garantia) ou visualizar os dados Profiler desse período de tempo para determinar que o SPID estava em execução de consultas.

A visualização de dados do Profiler

Visualizar dados Profiler eficientemente é extremamente útil na resolução de problemas de bloqueio. Mais importante coisa que compreenda é que não tem de procurar em tudo o capturada; seja selectivo. Profiler fornece capacidades para ajudá-lo eficazmente visualizem os dados capturados. Na caixa de diálogo Propriedades (no menu ficheiro, clique em Propriedades), Profiler permite-lhe limitar os dados apresentados, remover colunas de dados ou acontecimentos, agrupamento (ordenação) pelas colunas de dados e aplicar filtros. Pode procurar o rastreio completo ou apenas uma coluna específica para valores específicos (no menu Editar, clique em Localizar). Também pode guardar os dados Profiler para uma tabela do SQL Server (no menu ficheiro, aponte para Guardar como e, em seguida, clique em tabela) e executar consultas SQL contra ele.

Certifique-se de que efectuar a filtragem apenas num ficheiro de rastreio guardado anteriormente. Se efectuar estes passos num rastreio activo, corre o risco de perda de dados que foi capturados desde que o rastreio foi iniciado. Guardar um rastreio activo para um ficheiro ou tabela em primeiro lugar (no menu ficheiro, clique em Guardar como) e, em seguida, volte a abri-lo (no menu ficheiro, clique em Abrir) antes de continuar. Quando trabalha com um ficheiro de rastreio guardada, a filtragem não remove permanentemente os dados a ser filtrados, simplesmente não apresentar todos os dados. Pode adicionar e remover eventos e as colunas de dados conforme necessário para o ajudar foco as procuras.

Que está à procura:
  • Comandos de que tem o SPID no topo de uma cadeia de bloqueio executado dentro da transacção actual?
    Filtrar os dados de rastreio para um SPID específico que está no topo de uma cadeia de bloqueio (no menu ficheiro, clique em Propriedades; em seguida, no separador filtros, especifique o valor SPID). Em seguida, pode examinar os comandos executou anterior à hora que esta foi a bloquear outros SPID. Se incluir os eventos de transacções, possam identificar facilmente quando foi iniciada uma transacção. Caso contrário, pode procurar a coluna de texto de início, SAVE, COMMIT ou ROLLBACK TRANSACTION operações. Utilize o valor open_tran da tabela sysprocesses para garantir que captura todos os eventos de transação. Conhecer os comandos executados e o contexto de transação permitir-lhe-á determinar a razão pela qual um SPID possui bloqueios.

    Lembre-se de que pode remover colunas de dados e eventos. Em vez de visualizar ambos os iniciar e eventos concluídos, seleccione um. Se o SPID bloqueio não são procedimentos armazenados, remova o SP: Iniciar ou SP: concluída eventos; o SQLBatch e eventos RPC mostrará a chamada de procedimento. Ver apenas os eventos SP quando precisar de ver esse nível de detalhe.
  • O que é a duração das consultas para os SPID lidera o bloqueio de cadeias?
    Se incluir os eventos concluídos acima, a coluna duração mostrará o tempo de execução da consulta. Isto pode ajudar a identificar consultas de execução longa que estão a causar o bloqueio. Para determinar por que razão a consulta está a efectuar lentamente, visualize a CPU, Ler e as escritas de colunas, bem como o evento de Plano de execução.

Categorizar comuns cenários de bloqueio

A tabela abaixo mapeia sintomas comuns para suas causas provável. O número indicado na coluna cenário corresponde ao número na secção "Comum bloqueio cenários e resoluções" deste artigo abaixo. As colunas WaittypeOpen_Tran e estado Consulte informações sysprocesses. O resolve? coluna indica o bloqueio irá resolver sozinho ou não.

Reduzir esta tabelaExpandir esta tabela
CenárioWaittypeOpen_TranEstadoResolve?Outras sintomas
1Não-zero>= 0runnableSim, quando tiver terminado de consulta.Colunas Physical_IO, da CPU e/ou Memusage irão aumentar ao longo do tempo. Duração para a consulta será elevada quando concluída.
20 x 0000 por meio>0no modo de suspensãoNão, mas pode ser sujeitos a occisão SPID.Um sinal de atenção pode ser visto no Gestor de perfis de rastreio para este SPID, indicando um tempo limite de consulta ou cancelar ocorreu.
30 x 0000 por meio>= 0runnable' Não '. Não irá resolver até que o cliente obtém todas as linhas ou fecha a ligação. SPID possa ser abatido, mas pode demorar até 30 segundos.Se open_tran = 0 e o SPID detém bloqueios enquanto o nível de isolamento de transacções está predefinido (leitura COMMMITTED), esta é uma causa provável.
4Varia>= 0runnable' Não '. Não irá resolver até cliente cancela consultas ou fecha ligações. SPID possam ser abatidos, mas podem demorar até 30 segundos.A coluna nome de anfitrião na sysprocesses para o SPID no topo de uma cadeia de bloqueio será a mesma como um SPID está a bloquear.
50 x 0000 por meio>0anulaçãoSim.Um sinal de atenção pode ser visto no rastreio Profiler para este SPID, indicando um tempo limite de consulta ou cancelar ocorreu ou simplesmente uma instrução de anulação foi emitida.
60 x 0000 por meio>0no modo de suspensãoEventualmente. Quando o Windows NT determina a sessão é não activo mais tempo, o SQL Server ligação será quebrada.O valor last_batchsysprocesses é muito anterior à hora actual.

Cenários de bloqueio e resoluções comuns

Os cenários listados abaixo terá as características enumeradas no quadro supra. Esta secção fornece detalhes adicionais, quando aplicável, bem como os caminhos para resolução.
  1. Bloqueio provocado por um normalmente em execução consulta com um longo tempo de execução

    Resolução:
    A solução a este tipo de problema de bloqueio é procure maneiras de optimizar a consulta. Na realidade, este tipo de problema de bloqueio pode apenas ser um problema de desempenho e requerer a exercer, como tal. Para obter informações sobre como resolver uma consulta específica de atrasar a execução, consulte o seguinte artigo da base de dados de conhecimento:
    243589Como resolver consultas de execução lenta em SQL Server 7.0 ou em versões posteriores
    Para aplicação global desempenho resolver, consulte o seguinte artigo da base de dados de conhecimento:
    224587COMO: Resolver problemas de desempenho de aplicações com o SQL Server
    Para mais informações, consulte o tópico de monitorização de desempenho e optimização tópicos sobre procedimentos SQL Server 2008 Books Online sobre o seguinte Web site da MSDN:
    http://msdn.microsoft.com/en-us/library/ms187830.aspx
    Se tiver uma consulta demorada que está a bloquear outros utilizadores e não pode ser optimizada, considere movê-lo a partir de um OLTP ambiente para um sistema de suporte de decisões.
  2. Bloquear provocado por um SPID dormir que tem perdeu-se o registo de transacções nível de aninhamento

    Este tipo de bloqueio com frequência pode ser identificado por um SPID está no modo de suspensão ou aguarda um comando, ainda, cuja nível de aninhamento de transacção (@@ TRANCOUNT, open_tran de sysprocesses) é maior que zero. Isto pode ocorrer se a aplicação tem um tempo limite de consulta ou emite um Cancelar sem emissão também o número de declarações ROLLBACK e/ou COMMIT necessário. Quando um SPID recebe um tempo limite de consulta ou cancelar, irá terminar a consulta actual e batch, mas não não automaticamente recuperar nem consolidar a transacção. A aplicação é responsável, tal como o SQL Server não é possível assumir que uma transacção completa deve ser revertida simplesmente devido a uma consulta simples a ser cancelada. O tempo limite de consulta ou cancelar aparecerá como um evento de sinal de atenção para o SPID no rastreio Profiler.

    Para demonstrar isto, emita a seguinte consulta simples do analisador de consultas:

    BEGIN TRAN 
    SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2
    
    -- Issue this after canceling query
    SELECT @@TRANCOUNT
    ROLLBACK TRAN
    						
    Enquanto a consulta está em execução, faça clique sobre o vermelho botão Cancelar. Depois da consulta é cancelada, SELECT @@ TRANCOUNT indica que o nível de aninhamento de transacção é um. Isto foi um DELETE ou um UPDATE consulta, ou HOLDLOCK tivesse sido utilizada no SELECT, todos os bloqueios adquiridos seriam ainda ter lugar. Mesmo com a consulta acima, se outra consulta tivesse sido adquiridos e detidos bloqueios anteriormente na transacção, serão ainda realizadas quando SELECT acima foi cancelada.

    Resoluções:

    • Aplicações correctamente tem de gerir níveis de aninhamento transacção ou possam causar um problema de bloqueio após o cancelamento da consulta desta forma. Pode fazê-lo de várias formas:
      1. No processador de erros da aplicação cliente, apresentar um IF @@ TRANCOUNT > 0 trânsito ROLLBACK seguinte qualquer erro, mesmo que a aplicação cliente não acredita uma transacção é abrir. Isto é necessário, porque um procedimento armazenado chamado durante a secção poderia ter iniciado uma transação sem o conhecimento da aplicação cliente. Nota que certas condições, como, por exemplo, o cancelamento da consulta, impedir o procedimento de execução passados a instrução actual, assim, mesmo que o processo tenha lógica para verificar se @@ erro <>0 e abortar a transacção, este código de anulação não será executado em tais casos.
      2. Utilize SET ON XACT_ABORT para a ligação, ou em quaisquer procedimentos armazenados que começar transacções e não limpar sequência de um erro. Em caso de um erro de tempo de execução, esta definição vai abortar as transacções abertas e devolver o controlo do cliente. Tenha em atenção demonstrações T-SQL após a instrução que causou o erro não serão executadas.
      3. Se pooling de conexões está a ser utilizado numa aplicação que abre a ligação e executa um pequeno número de consultas antes de libertar a ligação novamente ao conjunto, tal como uma aplicação baseada na Web, desactivar temporariamente o agrupamento de ligações pode ajudar a atenuar o problema até que a aplicação cliente é modificada de forma a processar os erros adequadamente. Ao desactivar o agrupamento de ligações, libertar a ligação causará um logout física da ligação do SQL Server, resultando o servidor de anular quaisquer transacções abertas.
      4. Se pooling de conexões estiver activado e o servidor de destino for SQL Server 2000, actualizar o computador cliente para o MDAC 2.6 ou posterior poderá ser benéfico. Esta versão dos componentes do MDAC adiciona código para o controlador ODBC e fornecedor de OLE DB para que a ligação deverá ser "Repor" antes de que é reutilizada. Esta chamada para sp_reset_connection interrompe qualquer transacções iniciadas pelo servidor (DTC transacções iniciadas por aplicação da cliente não são afectadas), repõe a base de dados predefinida, opções de SET e por aí em diante. Tenha em atenção que a ligação não é reposta até que a reutilização do conjunto de ligação, por isso, é possível que um utilizador poderia abrir uma transacção e em seguida, liberte a ligação ao conjunto de ligação, mas não poderia ser reutilizado durante vários segundos, período durante o qual a transacção continuaria a ser aberta. Se a ligação é reutilizada não, a transacção será cancelada quando a ligação expirar e é removida do conjunto de ligação. Assim, é ideal para a aplicação de cliente abortar as transacções no seu processador de erros ou utilizar SET ON XACT_ABORT para evitar este atraso potencial.
    • Na realidade, este tipo de problema de bloqueio pode também ser um problema de desempenho e requerer a exercer, como tal. Se o tempo de execução de consulta pode ser diminuído, o tempo limite de consulta ou cancelar seria não ocorrer. É importante que a aplicação conseguir processar o tempo de espera ou cancelar cenários caso elas surjam, mas também podem beneficiar examinar o desempenho da consulta.
  3. Bloquear provocado por um SPID cujo pedido de cliente correspondente não Fetch todas as linhas de resultados para conclusão

    Depois de enviar uma consulta ao servidor, todas as aplicações devem obter imediatamente todas as linhas de resultados para conclusão. Se uma aplicação não obter todas as linhas de resultados, podem ser deixadas fechaduras nas tabelas, bloquear outros utilizadores. Se estiver a utilizar uma aplicação que submete transparentemente SQL demonstrações para o servidor, a aplicação deve obter todas as linhas de resultados. Se não tiver (e se não pode ser configurado para o fazer), que poderá não conseguir resolver o problema de bloqueio. Para evitar o problema, pode restringir funcionou incorrectamente aplicações para um relatório ou uma base de dados do suporte de decisão.

    Resolução:

    A aplicação deve ser re-written obter todas as linhas do resultado da realização.
  4. Bloquear provocado por um bloqueio de cliente/servidor distribuído

    Ao contrário de um impasse convencional, um impasse distribuído não é detectável utilizando o Gestor de bloqueio RDBMS. Isto deve-se ao facto de apenas um dos recursos envolvidos no bloqueio é um bloqueio do SQL Server. O outro lado do impasse está no nível de aplicação cliente, através da qual o SQL Server não tem nenhum controlo. O seguinte é dois exemplos de como isto pode acontecer e formas possíveis da aplicação podem evitá-lo.

    1. Cliente/servidor distribuído impasse com um único cliente thread
      Se o cliente tiver várias ligações abertas e um único thread de execução, poderão ocorrer o seguinte impasse distribuído. Para brevity, o termo "dbproc" utilizado aqui designam a estrutura de ligação do cliente.

       SPID1------blocked on lock------->SPID2
        /\                         (waiting to write results         
        |                           back to client)
        |                                 |
        |                                 |                      Server side
        | ================================|==================================
        |     <-- single thread -->       |                      Client side
        |                                 \/ 
       dbproc1   <-------------------   dbproc2
       (waiting to fetch             (effectively blocked on dbproc1, awaiting
        next row)                     single thread of execution to run)
      								
      No caso apresentado acima, um thread de aplicação de cliente único tem duas ligações abertas. Apresente uma operação de SQL dbproc1 assincronamente. Isto significa que não aguarda que a chamada seja devolvida antes de continuar. A aplicação, em seguida, envia outra operação de SQL no dbproc2 e aguarda os resultados para começar a processar os dados devolvidos. Quando o inicia dados provenientes novamente (qualquer que seja dbproc primeiro responde--assumir trata dbproc1), processa todos os dados devolvidos nesse dbproc realização. Obtém os resultados de dbproc1 até SPID1 fica bloqueado num bloqueio retido pela SPID2 (porque as duas consultas estão em execução assincronamente no servidor). Neste ponto, dbproc1 aguardará indefinidamente mais dados. SPID2 não está bloqueada num bloqueio, mas tenta enviar dados ao respectivo cliente, dbproc2. No entanto, dbproc2 é efectivamente bloqueado dbproc1 na camada de aplicação tal como o único thread de execução para a aplicação está em utilização pelo dbproc1. Isto resulta num impasse do SQL Server não consegue detectar ou resolver porque apenas um dos recursos envolvidos é um recurso do SQL Server.
    2. Cliente/servidor distribuído impasse com um thread por ligação

      Mesmo se existe um thread separado para cada ligação do cliente, ainda poderá ocorrer uma variação deste impasse distribuído como mostrado a seguinte redacção.

      SPID1------blocked on lock-------->SPID2
        /\                         (waiting on net write)        Server side
        |                                 |
        |                                 |
        | INSERT                          |SELECT
        | ================================|==================================
        |     <-- thread per dbproc -->   |                      Client side
        |                                 \/ 
       dbproc1   <-----data row-------   dbproc2
       (waiting on                     (blocked on dbproc1, waiting for it
        insert)                         to read the row from its buffer)
      								
      Neste caso é semelhante ao exemplo A, excepto dbproc2 e SPID2 estiverem a executar uma instrução SELECT com a intenção de executar linha-a-a-time processamento e entrega de cada linha através de uma memória intermédia para dbproc1 para um INSERT, UPDATE ou DELETE instrução na mesma tabela. Eventualmente, SPID1 (efectuando a INSERT, UPDATE ou DELETE) fica bloqueado num bloqueio retido pela SPID2 (efectuando a SELECT). SPID2 escreve uma linha de resultado dbproc2 o cliente. Dbproc2 tenta, em seguida, passar a linha numa memória intermédia para dbproc1, mas localiza dbproc1 está ocupado (está bloqueado a aguardar SPID1 para terminar a actual INSERT, que está bloqueada em SPID2). Neste ponto, dbproc2 está bloqueada na camada de aplicação por dbproc1 cujo SPID (SPID1) está bloqueada ao nível da base de dados por SPID2. Novamente, isto resulta num impasse do SQL Server não consegue detectar ou resolver porque apenas um dos recursos envolvidos é um recurso do SQL Server.
    Ambos os exemplos A e B são questões fundamentais que os programadores de aplicações devem ter em consideração. Tem o código de aplicações para processar correctamente nestes casos.

    Resoluções:

    Duas soluções fiáveis estão a utilizar um tempo limite de consulta ou ligações dependentes.

    • Tempo de espera de consulta
      Quando tiver sido fornecido um tempo limite de consulta, se ocorrer impasse distribuído, irá ser violada quando, em seguida, tempo de espera acontece. Consulte a biblioteca de base de dados ou a documentação do ODBC para obter mais informações sobre a utilização de um tempo limite de consulta.
    • Dependente ligações
      Esta funcionalidade permite que um cliente ter várias ligações vincular no espaço numa única transacção, para que as ligações não bloquear entre si. Para mais informações, consulte o tópico "Using ligações dependente" no SQL Server 7.0 Books Online.
  5. Bloquear provocado por um SPID que é um "ouro," ou anulação, estado

    Uma consulta de modificação de dados que foi cancelada ou cancelada fora de uma transacção definida pelo utilizador, vai ser revertida. Também pode ocorrer como um efeito secundário de reiniciar o computador do cliente e sua desligar sessão de rede. Do mesmo modo, uma consulta seleccionada como a vítima de impasse vai ser revertida. Uma consulta de modificação de dados com frequência não pode ser revertida qualquer mais rapidamente do que as alterações foram inicialmente aplicadas. Por exemplo, se uma instrução DELETE, INSERT ou UPDATE estavam em execução durante uma hora, poderia obter pelo menos uma hora para o poder recuperar. Este é o comportamento previsto, porque as alterações efectuadas devem ser completamente revertidas ou integridade transaccional e física na base de dados deverá ficar comprometida. Porque isso deve acontecer, SQL Server assinala o SPID num Estado de "ouro" ou de anulação (que significa não pode ser abatido ou seleccionada como uma vítima de impasse). Muitas vezes podem ser identificado pela observação a saída de sp_who, que poderá indicar o comando ROLLBACK. Coluna estado do sys.sysprocesses vai indicar um Estado ROLLBACK, que também serão apresentadas na saída sp_who ou no SQL Server Management Studio atividade do monitor.
    Resolução:

    Tem de aguardar que o SPID concluir a anular as alterações que foram efectuadas.

    Se o servidor é encerrado no meio desta operação, a base de dados vai estar no modo de recuperação após o reinício e ficará inacessível até que todas as abertas as transacções são processadas. Arranque recuperação demora essencialmente o mesmo período de tempo por transacção como tempo de execução de recuperação e a base de dados é inacessível durante este período. Assim, forçar o servidor para baixo para correcção de um SPID num Estado de anulação será frequentemente contraproducente.

    Para evitar esta situação, não efectuar grandes comandos INSERT, UPDATE, ou DELETE operações durante o tempo ocupado em sistemas OLTP. Se possível, efectue essas operações durante os períodos de baixa actividade.
  6. Bloquear provocado por uma ligação isolada

    Se os colectores de aplicação cliente ou a estação de trabalho do cliente for reiniciada, a sessão de rede para o servidor não pode ser imediatamente cancelada em determinadas condições. A partir da perspectiva do servidor, o cliente parece ainda estar presente e ainda podem ser mantidos quaisquer bloqueios adquiridos. Para obter mais informações, clique no número de artigo que se segue para visualizar o artigo na base de dados de conhecimento da Microsoft:
    137983Como resolver problemas de ligações isoladas no SQL Server

    Resolução:

    Se a aplicação cliente foi desligado sem limpar adequadamente aos respectivos recursos, pode terminar o SPID, utilizando o comando KILL. O comando KILL assume o valor SPID como entrada. Por exemplo, para a occisão SPID 9, simplesmente emita o seguinte comando:

    KILL 9
    						

    Nota O comando KILL pode demorar até 30 segundos para concluir, devido ao intervalo entre verificações para o comando KILL.

Participação de aplicação em problemas de bloqueio

Poderá existir uma tendência para focar problemas de optimização e plataforma lado do servidor quando virada para um problema de bloqueio. No entanto, este não conduz geralmente a uma resolução e pode absorver tempo e esforços na melhor dirigido ao examinar a aplicação cliente e as consultas que submete. Não importa qual o nível de visibilidade da aplicação expõe sobre as chamadas de base de dados a ser feitas, um problema de bloqueio, não obstante, frequentemente requer tanto à inspecção das instruções SQL exactas apresentado pela aplicação e comportamento exacta da aplicação relativamente à anulação de consulta, gestão de ligações, a obtenção de todos os resultar linhas e assim sucessivamente. Se a ferramenta de desenvolvimento não permite o controlo explícito sobre gestão de ligações, cancelamento de consulta, tempo limite de consulta, a obtenção de resultados e assim sucessivamente, problemas de bloqueio podem não ser passíveis de resolução. Este potencial deve ser examinado estreitamente antes de seleccionar uma ferramenta de desenvolvimento de aplicações para o SQL Server, especialmente para ambientes OLTP críticos.

É vital que ser exercido cuidado durante a fase de concepção e construção de base de dados e aplicações. Em especial, o consumo de recursos, nível de isolamento e comprimento do percurso de transacção devem ser avaliados para cada consulta. Cada consulta e a transacção deve ser tão leve quanto possível. Deve ser exercida disciplina de gestão de ligação em boas condições. Se isto não é efectuado, é possível que a aplicação poderá parece ter um desempenho aceitável números baixos de utilizadores, mas o desempenho pode diminuir significativamente como o número de escalas de utilizadores para cima.

Com uma aplicação adequada e estrutura da consulta, o Microsoft SQL Server é capaz de suportar vários milhares de utilizadores em simultâneo num único servidor, com o bloqueio pouco.

Propriedades

Artigo: 224453 - Última revisão: 11 de setembro de 2009 - Revisão: 5.1
A informação contida neste artigo aplica-se a:
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL 2005 Server Enterprise
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Workgroup
Palavras-chave: 
kbmt kbhowto kbtshoot kbexpertiseinter kbinfo KB224453 KbMtpt
Tradução automática
IMPORTANTE: Este artigo foi traduzido por um sistema de tradução automática (também designado por Machine translation ou MT), não tendo sido portanto revisto ou traduzido por humanos. A Microsoft tem artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais. O objectivo é simples: oferecer em Português a totalidade dos artigos existentes na base de dados do suporte. Sabemos no entanto que a tradução automática não é sempre perfeita. Esta pode conter erros de vocabulário, sintaxe ou gramática? erros semelhantes aos que um estrangeiro realiza ao falar em Português. A Microsoft não é responsável por incoerências, erros ou estragos realizados na sequência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza actualizações frequentes ao software de tradução automática (MT). Obrigado.
Clique aqui para ver a versão em Inglês deste artigo: 224453

Submeter comentários

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com