Você está offline; aguardando reconexão

INF: Compreender e resolver problemas de bloqueio do SQL Server

IMPORTANTE: Este artigo foi traduzido pelo software de tradução automática da Microsoft e eventualmente pode ter sido editado pela Microsoft Community através da tecnologia Community Translation Framework (CTF) ou por um tradutor profissional. A Microsoft oferece artigos traduzidos automaticamente por software, por tradutores profissionais e editados pela comunidade para que você tenha acesso a todos os artigos de nossa Base de Conhecimento em diversos idiomas. No entanto, um artigo traduzido pode conter erros de vocabulário, sintaxe e/ou gramática. A Microsoft não é responsável por qualquer inexatidão, erro ou dano causado por qualquer tradução imprecisa do conteúdo ou por seu uso pelos nossos clientes.

Clique aqui para ver a versão em Inglês deste artigo: 224453
Sumário
Neste artigo, o termo "conexão" refere-se a uma sessão de logon única do banco de dados. Cada conexão aparecerá como um identificador de sessão (SPID). Cada um desses SPIDs é conhecida como um processo, embora não seja um contexto de processo separado no sentido usual. Em vez disso, cada SPID consiste em recursos do servidor e estruturas de dados necessárias para atender as solicitações de uma única conexão de um determinado cliente. Um aplicativo único cliente pode ter uma ou mais conexões. Da perspectiva do SQL Server, não há nenhuma diferença entre várias conexões de um aplicativo cliente único em um único computador cliente e várias conexões de vários aplicativos cliente ou vários computadores cliente. Uma conexão pode bloquear a outra conexão, independentemente se eles provêm do mesmo aplicativo ou aplicativos separados em dois computadores cliente diferentes.
Mais Informações
O bloqueio é uma característica inevitável de qualquer sistema de gerenciamento de banco de dados relacional (RDBMS) com simultaneidade com base em bloqueio. No SQL Server, o bloqueio ocorre quando um SPID mantém um bloqueio em um recurso específico e um segundo SPID tenta adquirir um tipo de bloqueio conflitantes no mesmo recurso. Normalmente, o período para o qual o SPID primeiro bloqueia o recurso é muito pequeno. Ao liberar o bloqueio, a segunda conexão é livre para adquirir seu próprio bloqueio no recurso e continuar o processamento. Esse é um comportamento normal e pode acontecer várias vezes durante o curso de um dia com nenhum efeito perceptível no desempenho do sistema.

O contexto da transação e a duração de uma consulta determina quanto tempo seus bloqueios são mantidos e, conseqüentemente, seu impacto em outras consultas. Se a consulta não é executada em uma transação (e não as dicas de bloqueio são usadas), os bloqueios para instruções SELECT somente serão mantidos em um recurso no momento em que, na verdade, está sendo lido, mas não para a duração da consulta. Para as instruções INSERT, UPDATE e DELETE, os bloqueios são mantidos para a duração da consulta de consistência dos dados e permitir que a consulta deve ser revertida se necessário.

Para consultas executadas dentro de uma transação, a duração para que os bloqueios são mantidos são determinados pelo tipo de consulta, o nível de isolamento de transação e bloquear ou não as dicas são usadas na consulta. Para obter uma descrição de bloqueio, dicas de bloqueio e níveis de isolamento de transação, consulte os seguintes tópicos nos Manuais Online do SQL Server:
  • Bloqueio do Mecanismo de Banco de Dados
  • Personalizando o Versionamento de Bloqueio e de Linha
  • Modos de bloqueio
  • Compatibilidade de Travamento
  • Níveis de isolamento com base no controle de versão de linha no mecanismo de banco de dados
  • Controle de Transações (Mecanismo de Banco de Dados)
Quando travamentos e bloqueios aumentam até o ponto em que há um efeito negativo no desempenho do sistema, geralmente é devido a um dos seguintes motivos:
  • Um SPID mantém bloqueios em um conjunto de recursos para uma extendedperiod de tempo antes de lançá-las. Esse tipo de bloqueio elimina o tempo de itselfover, mas pode causar degradação do desempenho.
  • Um SPID mantém bloqueios em um conjunto de recursos e nunca releasesthem. Esse tipo de bloqueio não resolver e impede o acesso a recursos afetados indefinidamente.
Na primeira situação acima, o problema de bloqueio elimina propriamente dito ao longo do tempo como o SPID libera os bloqueios. No entanto, a situação pode ser bastante tranqüila causa diferente de SPIDs bloqueio nos recursos diferentes ao longo do tempo, criando um destino de movimento. Por esse motivo, essas situações podem ser difícil solucionar problemas usando o SQL Server Enterprise Manager ou consultas SQL individuais. A segunda situação resulta em um estado consistente que pode ser mais fácil de diagnosticar.

Coletando informações de bloqueio

Para neutralizar a dificuldade de solução de problemas de bloqueio, um administrador de banco de dados pode usar scripts SQL que monitoram constantemente o estado de bloqueio e de bloqueio no SQL Server. Esses scripts podem fornecer instantâneos de instâncias específicas ao longo do tempo, levando a uma idéia geral do problema. Para obter uma descrição de como monitorar o bloqueio com scripts SQL, consulte os seguintes artigos da Base de dados de Conhecimento da Microsoft:
271509 Como monitorar o bloqueio no SQL Server 2005 e no SQL Server 2000
Os scripts neste artigo executará as tarefas abaixo. Sempre que possível, é fornecido o método para obter essas informações do SQL Server Management Studio.
  1. Identifica o SPID (identificação da sessão) no topo da cadeia de bloqueio e a instrução SQL.
    Além de usar os scripts no artigo da Base de conhecimento mencionado anteriormente, é possível identificar a cabeça da cadeia de bloqueio usando recursos que são fornecidos com o SQL Server Management Studio. Para fazer isso, use um dos seguintes métodos:
    • Clique com botão direito no objeto do servidor, expanda relatórios, expanda Relatórios padrãoe clique em atividade – todas as transações de bloqueio. Este relatório mostra as transações no topo da cadeia de bloqueio. Se você expandir a transação, o relatório mostrará as transações que são bloqueadas pela transação principal. Este relatório também mostrará o "bloqueio SQL Statement" e a "declaração de SQL bloqueado".
    • Use DBCC INPUTBUFFER (<spid>) para localizar a última instrução foi enviada por um SPID.</spid>
  2. Encontre o nível de aninhamento de transação e o status do processo de SPID bloqueio.
    O nível de aninhamento de transação de um SPID está disponível na variável global the@@TRANCOUNT. No entanto, isso pode ser determinado de theSPID externa consultando a tabela de processos da seguinte maneira:

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>go						
    O valor retornado é o valor de @@TRANCOUNT para o SPID. Este showsthe aninhamento nível de transação para o SPID bloqueio, que por sua vez pode explainwhy que ele está mantendo bloqueios. Por exemplo, se o valor for maior que zero, o theSPID está no meio de uma transação (nesse caso espera-se que itretains determinado bloqueará adquiriu, dependendo da transação isolationlevel).

    Você também pode verificar para ver se qualquer longo prazo abrir transactionexists no banco de dados usando o DBCC OPENTRANDatabase_Name.

Coleta informações de rastreamento do SQL Server Profiler

Além das informações acima, geralmente é necessário capturar um rastreamento do Profiler das atividades no servidor completamente investigar um problema de bloqueio no SQL Server. Se um SPID executa várias instruções em uma transação, apenas o último statementthat foi enviado mostrará no relatório, buffer de entrada ou saída de monitor de atividade. No entanto, um dos comandos anteriores pode ser o motivo pelo qual os bloqueios ainda estão sendo mantidos. Um rastreamento do Profiler permitirá que você veja todos os comandos executados por um SPID dentro da transação atual. As etapas a seguir o ajudarão a configurar o SQL Server Profiler para capturar um rastreamento.
  1. Abra o SQL Server Profiler.
  2. No menu arquivo , aponte para novoe clique em Rastrear.
  3. Na guia Geral , especifique um nome de rastreamento e um nome de arquivo para capturar os dados.

    Importante: O arquivo de rastreamento deve ser gravado em um disco local ou compartilhado rápido. Evite o rastreamento em uma unidade de disco ou rede lenta. Também verifique se que servidor processa o rastreamento de dados são selecionados.
  4. Na guia Eventos de seleção , clique para selecionar Mostrar todos os eventos e as caixas de seleção Mostrar todas as colunas .
  5. Na guia Eventos de seleção , adicione os tipos de eventos que estão listados na tabela 1 para o rastreamento.

    Além disso, você pode incluir os tipos de evento adicionais que estão listados na tabela 2 para obter mais informações. Se você estiver executando em um ambiente de produção de alto volume, você pode decidir usar somente os eventos na tabela 1, como eles são geralmente suficientes para solucionar a maioria dos problemas de bloqueio. Incluindo eventos adicionais na tabela 2 pode torná-lo mais fácil de determinar rapidamente a origem de um problema (ou esses eventos podem ser necessários para identificar a instrução culpado em um procedimento de várias instruções). No entanto, incluindo eventos na tabela 2 também adicionar a carga no sistema e aumentar o tamanho de saída de rastreamento.
Tabela 1: Tipos de evento
TítuloEvento
Erros e avisosExceção
Erros e avisosAtenção
Auditoria de segurançaLogon de auditoria
Auditoria de segurançaLogout de auditoria
SessõesConexão existente
Procedimentos armazenadosRPC: iniciando
TSQL: BatchStarting

Tabela 2: Tipos de evento adicionais
TítuloEvento
TransaçõesRepositório de transações
TransaçõesSQLTransaction
Procedimentos armazenadosRPC: concluído
TSQLBatchCompleted
Procedimentos armazenadosSP:StmtStarting
Procedimentos armazenadosSP:StmtCompleted

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

Identificando e resolvendo os cenários comuns de bloqueio

Examinando as informações acima, você pode determinar a causa da maioria dos problemas de bloqueio. O restante deste artigo é uma discussão de como usar essas informações para identificar e resolver alguns cenários comuns de bloqueio. Essa discussão assume que você usou scripts bloqueio no artigo 271509 (mencionados anteriormente) para capturar informações sobre os SPIDs bloqueio e fez um rastreamento do Profiler com os eventos descritos acima.

Visualizando a saída do Script bloqueio

Examine a saída sysprocesses para determinar os cabeçotes de cadeias de bloqueio
Se você não especificar um modo rápido para os scripts de bloqueio, haverá uma seção intitulada "SPIDs no topo do bloqueio cadeias" thatlists os SPIDs que estão bloqueando outros SPIDs na saída do script.
SPIDs at the head of blocking chains
Se você especificou a opção rápida, você ainda poderá determinar cabeçotes theblocking examinando a saída sysprocesses e seguindo a hierarquia do SPID é relatado na coluna bloqueada.
Examine a saída sysprocesses para obter informações sobre os SPIDs no topo da cadeia de bloqueio.
É importante avaliar os seguintes campos sysprocesses :

Status

Essa coluna mostra o status de um determinado SPID. Normalmente, um status de repouso indica que o SPID concluiu a execução e está aguardando o aplicativo enviar outra consulta ou lote. Um status de executáveis, executandoou sos_scheduler_yield indica que o SPID está processando uma consulta. A tabela a seguir fornece breves explicações sobre os vários valores de status.
StatusSignificado
Plano de fundoO SPID está executando uma tarefa de plano de fundo, como detecção de deadlock.
Em repousoO SPID não está em execução no momento. Isso geralmente indica que o SPID está aguardando um comando do aplicativo.
Em execuçãoO SPID está sendo executado em um agendador.
ExecutávelO SPID está na fila executável de um programador e aguardando para obter o tempo do Agendador.
Sos_scheduler_yieldO SPID estava sendo executado, mas ele voluntariamente gerou sua fração de tempo o Agendador para permitir que outro SPID obter tempo de Agendador.
SuspensoO SPID está aguardando um evento, como um bloqueio ou uma trava.
ReversãoO SPID é na reversão de uma transação.
DefwakeupIndica que o SPID está aguardando um recurso que está sendo liberada. O campo waitresource deve indicar o recurso em questão.

Open_tran

Este campo indica o nível de aninhamento de transação do SPID. Se este valor for maior que 0, o SPID está dentro de uma transação aberta e pode ser mantendo bloqueios adquiridos por qualquer instrução dentro da transação.

Lastwaittype e waittype tempo_de_espera

O campo lastwaittype é uma representação de seqüência de caracteres do campo waittype , que é uma coluna binária interna reservada. Se o waittype 0x0000, o SPID não está esperando do 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 waittype atual do SPID.

Para obter uma descrição das diferentes lastwaittype e waittype valores, consulte o seguinte artigo da Base de dados de Conhecimento da Microsoft:
822101 Descrição das colunas waittype e lastwaittype na tabela master.dbo.sysprocesses no SQL Server 2000 e SQL Server 2005
Para obter mais informações sobre DM os_wait_stats, consulte os Manuais Online do SQL Server.

O valor de tempo_de_espera pode ser usado para determinar se o SPID está fazendo progresso. Quando uma consulta com base na tabela sysprocesses retorna um valor na coluna tempo_de_espera menor que o valor de tempo_de_espera de uma consulta anterior do sysprocesses, isso indica que o bloqueio anterior foi adquirido e liberado e está aguardando um bloqueio novo (supondo tempo_de_espera diferente de zero). Isso pode ser verificado comparando waitresource entre sysprocesses saída.

Waitresource

Este campo indica o recurso um SPID está aguardando. A tabela a seguir lista os formatos comuns de waitresource e seus significados:
RecursoFormatoExemplo:
TabelaDatabaseID:ObjectID:IndexIDGUIA: 5:261575970:1
Nesse caso, o banco de dados ID 5 é o banco de dados de exemplo pubs e objeto 261575970 ID é a tabela de títulos e 1 é o índice de cluster.
PáginaDatabaseID:FileID:PageIDPÁGINA: 5:1:104
Nesse caso, o banco de dados ID 5 é pubs, arquivo ID 1 é o arquivo de dados principal e 104 é uma página que pertencem à tabela de títulos .

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

Nesse caso, o banco de dados ID 5 é Pubs, Hobt_ID 72057594044284928 corresponde à index_id sem cluster 2 para id de objeto 261575970 (tabela detítulos ). Use o modo de exibição de catálogo sys.partitions para associar hobt_id a uma id de índice específico e um id de objeto. Não há nenhuma maneira de unhash o hash de chave de índice para um valor de chave de índice específico.
LinhaDatabaseID:FileID:PageID:Slot(row)LIVRAR-SE: 5:1:104:3

Nesse caso, o banco de dados ID 5 é pubs, arquivo ID 1 é o arquivo de dados principal, 104 é uma página que pertencem à tabela de títulos e slot 3 indica a posição da linha na página.
CompilaçãoDatabaseID:ObjectID [[compilação]]GUIA: 5:834102012 [[compilação]] não é um bloqueio de tabela, mas em vez disso, uma compilação de bloqueio em um procedimento armazenado. Banco de dados ID 5 é pubs, objeto ID 834102012 usp_myprocedure procedimento armazenado. Consulte 263889 de artigo do Knowledge Base para obter mais informações sobre bloqueio causados por bloqueios de compilação.
Outras colunas

As colunas restantes do sysprocesses podem fornecer informações sobre a raiz de um problema também. Sua utilidade varia de acordo com as circunstâncias do problema. Por exemplo, você pode determinar se o problema ocorre apenas em determinados clientes (nome do host), em determinadas bibliotecas de rede (net_library), quando o último lote enviado por um SPID (last_batch) e assim por diante.
Examine a saída do DBCC INPUTBUFFER.
Para qualquer SPID no topo de uma cadeia de bloqueio ou com zero anon waittype, o bloqueio de script será executado DBCC INPUTBUFFER todetermine consulta atual para essa SPID.

Em muitos casos, esta é a consulta que está causando o que são de bloqueios impedindo que outros usuários a ser realizada. No entanto, se o SPID está no atransaction, é possível que os bloqueios tiverem sido adquiridos por uma consulta executada anteriormente, não aquela atual. Portanto, você também deverá exibir a saída do Profiler para o SPID, não apenas o inputbuffer.

Observação: Como o bloqueio de script consiste em várias etapas, ele ispossible um SPID pode aparecer na primeira seção como a cabeça do blockingchain, mas no momento da execução da consulta de DBCC INPUTBUFFER, é não longerblocking e o INPUTBUFFER não for capturada. Isso indica que o blockingis Resolvendo próprio para essa SPID e ele pode ou não pode ser um problema. Em thispoint, use a versão rápida do bloqueio de script para tentar toensure captura a inputbuffer antes de limpar (embora ainda seja noguarantee) ou exibir o Profiler dados desse intervalo de tempo para determinar whatqueries o SPID estava sendo executado.

Exibindo os dados do Profiler

Exibir dados do Profiler com eficiência é extremamente valiosa na resolução de problemas de bloqueio. O mais importante a ser percebido é que você não precisa ver tudo capturadas; seja seletivo. Gerador de perfil fornece recursos para ajudá-lo efetivamente exibir os dados capturados. Na caixa de diálogo Propriedades (no menu arquivo , clique em Propriedades) Profiler permite limitar os dados exibidos pelo remover colunas de dados ou eventos, agrupamento (classificação) por colunas de dados e aplicar filtros. Você pode pesquisar o rastreamento inteiro ou somente uma coluna específica para valores específicos (no menu Editar , clique em Localizar). Você também pode salvar os dados do Profiler para uma tabela do SQL Server (no menu arquivo , aponte para Salvar como e, em seguida, clique em tabela) e executar consultas SQL em relação a ele.

Cuidado que você executar a filtragem somente em um arquivo de rastreamento salvo anteriormente. Se você executar estas etapas em um rastreamento ativo, você corre o risco de perda de dados que foram capturados desde que o rastreamento começou. Salvar um rastreamento ativo em um arquivo ou tabela (no menu arquivo , clique em Salvar como) e, em seguida, reabra-o (no menu arquivo , clique em Abrir) antes de prosseguir. Ao trabalhar em um arquivo de rastreamento salvos, a filtragem não remove permanentemente os dados que estão sendo filtrados, ele apenas não exibe todos os dados. Você pode adicionar e remover eventos e colunas de dados conforme necessário para facilitar as pesquisas.

O que procurar:
  • Quais comandos tem o SPID no topo de uma chainexecuted de bloqueio dentro da transação atual?
    Filtrar os dados de rastreamento SPID está no topo de uma cadeia de bloqueio aparticular (no menu arquivo , clique em Propriedades, na guia filtros , especifique o valor SPID). Você pode então examinar prévia comandos possui executado até o momento em que ele estava bloqueando outros SPIDs. Se você incluir os eventos theTransaction, eles podem identificar facilmente quando uma transação foi iniciada. Caso contrário, você pode pesquisar a coluna do texto inicial, salvar, COMMIT ou ROLLBACK TRANSACTIONoperations. Use o valor open_tran da tabela de processos para garantir que você capture todos os eventos de transação. Conhecer os comandos executados e o contexto de transação permitirá que todetermine por um SPID está mantendo bloqueios.

    Lembre-se de que você pode removeevents e dados de colunas. Em vez de olhar inicial e completedevents, escolha um. Se os SPIDs bloqueio não são procedimentos armazenados, remova oSP: iniciando ou SP: concluído eventos; os eventos SQLBatch e RPC mostrará a chamada de procedimento. Modo de whenyou de eventos de SP precisa ver esse nível de detalhe.
  • Qual é a duração das consultas de SPIDs no bloqueio cadeias de headof?
    Se você incluir os eventos concluídos acima, a Duration coluna mostrará o tempo de execução da consulta. Isso pode ajudar a youidentify consultas de execução demorada que estão causando o bloqueio. Para determinar por que thequery estiver lento, exiba a CPU, leiturae escreve colunas, bem como o Plano de execução de evento.

A categorização de cenários comuns de bloqueio

A tabela a seguir mapeia os sintomas comuns para suas causas prováveis. O número indicado na coluna cenário corresponde ao número na seção "Common bloqueio cenários e resoluções" deste artigo abaixo. As colunas Waittype, Open_Trane Status consultem informações de processos . O resolve? coluna indica se ou não o bloqueio será resolvido por conta própria.

CenárioWaitTypeOpen_TranStatusResolve?Outros sintomas
1Diferente de zero> = 0executávelSim, quando termina a consulta.Colunas Physical_IO, CPU e/ou Memusage aumentará ao longo do tempo. Duração da consulta será alta quando concluído.
20x0000> 0em repousoNão, mas pode ser eliminado SPID.Um sinal de atenção pode ser visto no rastreamento do Profiler para essa SPID, indicando um tempo limite da consulta ou Cancelar ocorreu.
30x0000> = 0executávelNão. Não resolverá até que o cliente busca todas as linhas ou fecha a conexão. SPID pode ser eliminado, mas pode levar até 30 segundos.Se open_tran = 0 e o SPID mantém bloqueios enquanto o nível de isolamento de transação é padrão (leia COMMMITTED), que é uma causa provável.
4Varia de> = 0executávelNão. Não resolverá até que o cliente cancela consultas ou fecha conexões. SPIDs podem ser eliminados, mas podem levar até 30 segundos.A coluna de nome de host em processos para o SPID no topo de uma cadeia de bloqueio será o mesmo como o SPID está bloqueando.
50x0000> 0reversãoSim.Um sinal de atenção pode ser visto no rastreamento do Profiler para essa SPID, indicando um tempo limite da consulta ou Cancelar ocorreu, ou simplesmente uma instrução rollback foi emitida.
60x0000> 0em repousoEventualmente. Quando o Windows NT determina que a sessão não está ativa, a conexão do SQL Server será interrompida.O valor last_batch em processos é muito anterior à hora atual.

Situações de bloqueio comuns e resoluções

Os cenários listados abaixo terá as características listadas na tabela acima. Esta seção fornece detalhes adicionais quando aplicável, bem como caminhos para resolução.
  1. Bloqueio causados por um normalmente executando consulta com um tempo de execução

    Resolução:
    A solução para esse tipo de problema de bloqueio é procurar forways para otimizar a consulta. Na verdade, essa classe de bloqueio de problema pode justbe um problema de desempenho e exige a buscá-lo como tal. Para informationon uma consulta de execução lenta específica de solução de problemas, consulte o seguinte artigo da Base de Conhecimento Microsoft:
    243589 Como solucionar problemas de consultas de execução lenta no SQL Server 7.0 ou versões posteriores
    Para performancetroubleshooting geral de aplicativos, consulte o seguinte artigo da Base de Conhecimento:
    224587 COMO: Solucionar problemas de desempenho de aplicativos com o SQL Server
    Para obter mais informações, consulte o tópico Manuais Online do SQL Server 2008 monitoramento de desempenho e ajuste de tópicos de ajuda no site da MSDN: Se você tiver uma consulta de execução demorada blockingother usuários e não pode ser otimizada, considere a possibilidade de movê-lo de um OLTPenvironment para um sistema de suporte à decisão.
  2. Bloqueio causados por um SPID em espera que perdeu o controle sobre o nível de aninhamento de transação

    Esse tipo de bloqueio geralmente pode ser identificado por um SPIDthat está dormindo ou está aguardando um comando, mas cujo nível de aninhamento de transação (@@TRANCOUNT, open_tran de processos) é maior que zero. Isso pode ocorrer se o applicationexperiences um tempo limite de consulta, ou emitir um cancelamento sem emitir também o número de therequired de instruções de REVERSÃO e/ou confirmação. Quando um SPID recebe um tempo limite de aquery ou em Cancelar, ele terminará a consulta atual e o lote, butdoes não automaticamente reverter ou confirmar a transação. Isresponsible o aplicativo, como o SQL Server não pode assumir que uma transactionmust toda revertida simplesmente devido a uma única consulta está sendo cancelada. Querytimeout ou Cancelar aparecerá como um evento de sinal de atenção para o SPID no rastreamento de theProfiler.

    Para demonstrar isso, emita o seguinte queryfrom simple Query Analyzer:

    BEGIN TRAN SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2-- Issue this after canceling querySELECT @@TRANCOUNTROLLBACK TRAN						
    Enquanto a consulta está sendo executado, clique no vermelho botão Cancelar . Depois que a consulta for cancelada, selecione @@TRANCOUNT indicatesthat o nível de aninhamento de transação é um. Isso foi uma exclusão ou um UPDATEquery ou HOLDLOCK tenha sido usada em SELECT, todos o bloqueios adquiridos de wouldstill ser mantida. Mesmo com a consulta acima, se outra consulta havia adquirido bloqueios andheld anteriormente na transação, eles seriam ainda mantidos quando o aboveSELECT foi cancelado.

    Resoluções:

    • Aplicativos corretamente devem gerenciar níveis de aninhamento de transação, ou eles podem causar um problema de bloqueio após o cancelamento da consulta dessa maneira. Isso pode ser feito de várias maneiras:
      1. No manipulador de erro do aplicativo cliente, enviar um se @@TRANCOUNT > 0 TRAN REVERSÃO após qualquer erro, mesmo se o aplicativo cliente não acredita que uma transação é aberto. Isso é necessário, porque um procedimento armazenado chamado durante o lote pode ter iniciado uma transação sem o conhecimento do aplicativo cliente. Observe que determinadas condições, como cancelar a consulta, impedem o procedimento em execução após a instrução atual, mesmo que o procedimento tem lógica para verificar se @@ERROR <> 0 e anular a transação, esse código de reversão não será executado em tais casos.
      2. Use SET XACT_ABORT ON para a conexão, ou em quaisquer procedimentos armazenados que começam as transações e não limpar após um erro. Em caso de erro de tempo de execução, esta configuração irá anular quaisquer transações abertas e retornar o controle para o cliente. Observe que as instruções T-SQL após a declaração que causou o erro não serão executadas.
      3. Se o pool de conexões está sendo usado em um aplicativo que abre a conexão e executa um pequeno número de consultas antes de liberar a conexão de volta para o pool, como um aplicativo baseado na Web, desabilitar temporariamente o pool de conexões pode ajudar a aliviar o problema até que o aplicativo cliente seja modificado para manipular os erros de forma adequada. Ao desabilitar o pool de conexões, liberando a conexão causará um logout físico da conexão do SQL Server, resultando no servidor reverter todas as transações abertas.
      4. Se o pool de conexões está ativado e o servidor de destino é SQL Server 2000, atualizar o computador cliente para o MDAC 2.6 ou posterior pode ser benéfico. Esta versão dos componentes MDAC adiciona código para o driver ODBC e o provedor OLE DB para que a conexão deve ser "Redefinir" antes de ela é reutilizada. Esta chamada para sp_reset_connection anula quaisquer transações iniciadas pelo servidor (transações DTC iniciadas pelo aplicativo cliente não são afetadas), redefine o banco de dados padrão, defina as opções e assim por diante. Observe que a conexão não é redefinida até que ela é reutilizada do pool de conexão, portanto, é possível que um usuário pode abrir uma transação e solte a conexão com o pool de conexão, mas não pode ser reutilizado por vários segundos, durante esse período a transação permanecerá aberta. Se a conexão não seja reutilizada, a transação será anulada quando a conexão expira e é removida do pool de conexão. Portanto, é ideal para o aplicativo cliente anular transações no seu manipulador de erro ou usar SET XACT_ABORT ON para evitar essa demora possível.
    • Na verdade, essa classe de bloqueio de problema também pode ser um problema de desempenho e precisam buscá-lo como tal. Se o tempo de execução da consulta pode ser diminuído, o tempo limite da consulta ou Cancelar não ocorreria. É importante que o aplicativo seja capaz de lidar com o tempo limite ou Cancelar cenários eles surgem, mas também pode beneficiar examinando o desempenho da consulta.
  3. Bloqueio causados por um SPID cujo aplicativo de cliente correspondente não buscar todas as linhas de resultado para conclusão

    Após o envio de uma consulta para o servidor, todos os applicationsmust buscar imediatamente todas as linhas do resultado até a conclusão. Se um aplicativo buscar todas as linhas de resultado, bloqueios podem ser deixados em tabelas, bloqueando outros usuários. Se você estiver usando um aplicativo que envia transparentemente SQLstatements para o servidor, o aplicativo deve buscar todas as linhas do resultado. Se itdoes não (e se ele não pode ser configurado para fazê-lo), pode ser que você não pode toresolve o problema de bloqueio. Para evitar o problema, você pode se comportam restrictpoorly aplicativos para um relatório ou um supportdatabase de decisão.

    Resolução:

    O aplicativo deve ser escrito novamente para todas as linhas do resultado de busca até a conclusão.
  4. Bloqueio causados por um Deadlock de cliente/servidor distribuído

    Ao contrário de um deadlock convencional, um deadlockis distribuídos não podem ser detectados usando o RDBMS bloquear o Gerenciador. Isso ocorre porque a thatonly de fato um dos recursos envolvido no deadlock é um bloqueio do SQL Server. O lado do Theother do travamento está no nível do aplicativo cliente, sobre os quais SQLServer não tem controle. A seguir estão dois exemplos de como isso pode acontecer e maneiras possíveis o aplicativo podem evitá-lo.

    1. Cliente/servidor distribuído bloqueio com um Thread de cliente único
      Se o cliente tiver várias conexões abertas e uma única thread de execução, o deadlock distribuído seguinte pode ocorrer. Por questões de brevidade, o termo "dbproc" usado aqui refere-se à estrutura de conexão de 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 mostrado acima, um thread de aplicativo único cliente tem duas conexões abertas. Ele envia assincronamente uma operação SQL em dbproc1. Isso significa que ele não aguardará a chamada retorne antes de continuar. O aplicativo envia outra operação SQL em dbproc2 e espera os resultados para iniciar o processamento dos dados retornados. Quando os dados começam voltem (qualquer dbproc primeiro responde – suponha que essa é dbproc1), ele processa a conclusão de todos os dados retornados em que dbproc. Ele busca resultados de dbproc1 até SPID1 obtém bloqueado em um bloqueio mantido por SPID2 (porque as duas consultas estão executando de maneira assíncrona no servidor). Neste ponto, dbproc1 aguardará indefinidamente mais dados. SPID2 não está bloqueado em um bloqueio, mas tenta enviar dados ao seu cliente, dbproc2. No entanto, dbproc2 é efetivamente bloqueado no dbproc1 na camada de aplicativo como o único thread de execução do aplicativo está em uso por dbproc1. Isso resulta em um deadlock do SQL Server não pode detectar ou resolver porque apenas um dos recursos envolvidos é um recurso do SQL Server.
    2. Cliente/servidor distribuído bloqueio com um Thread por conexão

      Mesmo que exista um thread separado para cada conexão no cliente, uma variação desse deadlock distribuído ainda pode ocorrer conforme mostrado a seguir.

      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, exceto dbproc2 e SPID2 estão executando uma instrução SELECT com a intenção de executar linha no tempo de processamento e enviar cada linha por meio de um buffer para dbproc1 para um INSERT, UPDATE ou exclua a instrução na mesma tabela. Por fim, SPID1 (executando o INSERT, UPDATE ou DELETE) fica bloqueado em um bloqueio mantido por SPID2 (executar SELECT). SPID2 grava uma linha de resultado dbproc2 o cliente. Dbproc2, em seguida, tenta passar a linha em um buffer para dbproc1, mas localiza dbproc1 está ocupado (ele está bloqueado aguardando SPID1 para concluir a inserção atual, está bloqueada no SPID2). Neste ponto, dbproc2 é bloqueado na camada de aplicativo por dbproc1 cujo SPID (SPID1) está bloqueado no nível do banco de dados por SPID2. Novamente, isso resulta em um deadlock do SQL Server não pode detectar ou resolver porque apenas um dos recursos envolvidos é um recurso do SQL Server.
    Os dois exemplos A e B são problemas fundamentais thatapplication desenvolvedores devem estar cientes dos. Eles devem escrever código aplicativos handlethese casos adequadamente.

    Resoluções:

    Duas soluções confiáveis devem usar um querytimeout ou conexões acoplados.

    • Tempo limite da consulta
      Quando o tempo limite da consulta foi fornecido se ocorrer deadlock distribuído, ele será quebrado quando e tempo limite ocorre. Consulte a biblioteca de banco de dados ou a documentação do ODBC para obter mais informações sobre o uso de um tempo limite da consulta.
    • Acoplado conexões
      Esse recurso permite que um cliente ter várias conexões para ligá-los ao espaço de uma única transação, para que as conexões não bloqueiam uns aos outros. Para obter mais informações, consulte o tópico "Usando conexões vinculado" nos Manuais Online do SQL Server 7.0.
  5. Bloqueio causados por um SPID está em "Ouro" ou Rollback, estado

    Uma consulta de modificação de dados KILLed ou canceledoutside de uma transação definida pelo usuário, também será revertida. Isso também pode occuras um efeito colateral de sessiondisconnecting sua rede e a reinicialização do computador cliente. Da mesma forma, uma consulta selecionada como a vítima de deadlock será rolledback. Uma consulta de modificação de dados geralmente não pode ser revertida qualquer mais rápido do que o thechanges inicialmente foram aplicadas. Por exemplo, se uma exclusão, inserção ou UPDATEstatement estavam sendo executados para uma hora, pode levar pelo menos uma hora para reversão. Esse é o comportamento esperado, porque as alterações devem ser completelyrolled back ou integridade transacional e física no banco de dados seria becompromised. Porque isso deve acontecer, o SQL Server marca o SPID em um estado "ouro" ou reverter (que significa que não pode ser eliminado ou selecionadas como uma deadlockvictim). Geralmente podem ser identificado observando a saída do sp_who, que pode indicar o comando ROLLBACK. A coluna Status da sysprocesses indica um status de REVERSÃO, que também aparecerá na saída sp_who ou no SQL Server Management Studio Activity Monitor.
    Resolução:

    Você deve aguardar o SPID concluir Revertendo thechanges que foram feitas.

    Se o servidor está desligado no meio deEsta operação, o banco de dados estará no modo de recuperação ao reiniciar e itwill ser inacessíveis até que todas as transações abertas sejam processadas. Startuprecovery usa essencialmente a mesma quantidade de tempo por transação como timerecovery de execução e o banco de dados está inacessível durante esse período. Assim, o servidor forcingthe para corrigir um SPID em um estado de reversão será geralmente becounterproductive.

    Para evitar essa situação, não execute largebatch INSERT, UPDATE ou excluir operações durante o horário ocupado em sistemas OLTP. Se possível, realize essas operações durante períodos de baixa atividade.
  6. Bloqueio causados por uma conexão órfão

    Se as interrupções de aplicativo do cliente ou o clientworkstation for reiniciado, a sessão de rede para o servidor talvez não beimmediately cancelada em algumas condições. Da perspectiva do servidor, theclient ainda parece estar presente e quaisquer bloqueios adquiridos ainda podem beretained. Para obter mais informações, clique no número de artigo a seguir para visualizar o artigo na Base de Conhecimento Microsoft:
    137983 Como solucionar problemas de conexões órfãos no SQL Server

    Resolução:

    Se o aplicativo cliente foi desconectado withoutappropriately a limpeza de seus recursos, você poderá eliminar o SPID pelo comando KILL de usingthe. O comando KILL leva o valor SPID como entrada. Por exemplo, para kill SPID 9, basta execute o seguinte comando:

    KILL 9						

    Observação: O comando KILL pode levar até 30 segundos para ser concluída, devido ao intervalo entre as verificações para o comando KILL.

Envolvimento de aplicativo em problemas de bloqueio

Pode haver tendem a se concentrar nos problemas de ajuste e plataforma de servidor ao se deparar com um problema de bloqueio. No entanto, isso normalmente não resulta em uma resolução e pode absorver tempo e energia melhor direcionados para examinar o aplicativo cliente e as consultas que ele envia. Não importa o nível de visibilidade do aplicativo expõe sobre as chamadas de banco de dados está sendo feitas, um problema de bloqueio, mesmo assim, com freqüência requer a inspeção das instruções SQL exatas enviada pelo aplicativo e o comportamento exato do aplicativo em relação ao cancelamento de consulta, gerenciamento de conexão, buscando todos resultar linhas e assim por diante. Se a ferramenta de desenvolvimento não permitem o controle explícito sobre o gerenciamento de conexão, cancelamento de consulta, tempo limite de consulta, buscando resultado e assim por diante, problemas de bloqueio não podem ser resolvidos. Esse potencial deve ser examinado atentamente antes de selecionar uma ferramenta de desenvolvimento de aplicativos para SQL Server, especialmente para ambientes OLTP essenciais para os negócios.

É vital que muita atenção ser exercidos durante a fase de design e construção do banco de dados e do aplicativo. Em particular, o consumo de recursos, o nível de isolamento e o comprimento do caminho de transação devem ser avaliadas para cada consulta. Cada consulta e a transação devem ser mais simples possível. Disciplina de gerenciamento de conexão deve ser tomada. Se isso não for feito, é possível que o aplicativo pode parecer ter um desempenho aceitável em baixo número de usuários, mas o desempenho pode cair significativamente como o número de usuários pode ser dimensionado para cima.

Com o aplicativo apropriado e design da consulta, o Microsoft SQL Server é capaz de suportar milhares de usuários simultâneos em um único servidor, com pouco bloqueio.

Aviso: este artigo foi traduzido automaticamente

Propriedades

ID do Artigo: 224453 - Última Revisão: 07/03/2016 17:26:00 - Revisão: 6.0

Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, 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

  • kbsqlsetup kbhowto kbtshoot kbexpertiseinter kbinfo kbmt KB224453 KbMtpt
Comentários