INF: Entendendo e resolver problemas de bloqueio do SQL Server

Traduções deste artigo Traduções deste artigo
ID do artigo: 224453 - Exibir os produtos aos quais esse artigo se aplica.
Expandir tudo | Recolher tudo

Neste artigo

Sumário

Neste artigo, o termo "conexão" refere-se a uma sessão de logon única do banco de dados. Cada conexão aparece 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 de usual. Em vez disso, cada SPID consiste de recursos do servidor e estruturas de dados necessárias para atender a solicitações de uma única conexão de um determinado cliente. Um aplicativo único cliente pode ter uma ou mais conexões. Do ponto de vista do SQL Server, não existe 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 de cliente ou vários computadores cliente. Uma conexão pode bloquear a outra conexão, independentemente se eles emanar do mesmo aplicativo ou aplicativos diferentes 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 SPID segundo tenta adquirir um tipo de bloqueio conflitantes no mesmo recurso. Normalmente, o intervalo de tempo para o qual o SPID primeiro bloqueia o recurso é muito pequeno. Quando ele libera o bloqueio, a segunda conexão fica livre para obter seu próprio bloqueio do recurso e continuar o processamento. Isso é normal e pode acontecer várias vezes durante o curso de um dia sem efeito perceptível no desempenho do sistema.

Contexto de uma consulta de transações e duração determinar quanto tempo seus bloqueios são mantidos e, assim, 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, na verdade, está sendo lido, mas não para a duração da consulta. Para instruções INSERT, UPDATE e DELETE, os bloqueios são mantidos para a duração da consulta, tanto para consistência de dados e para permitir que a consulta a ser revertida se necessário.

Para consultas executadas em uma transação, a duração para o qual 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ções, consulte os seguintes tópicos no manual online do SQL Server:
  • O bloqueio no mecanismo de banco de dados
  • Personalizando o bloqueio e versão de linha
  • Modos de bloqueio
  • Compatibilidade de bloqueio
  • Linha com base no versão níveis de isolamento no mecanismo de banco de dados
  • Controle de transações (Database Engine)
Quando o bloqueio e bloqueio de aumento para o ponto em que há um efeito prejudicial no desempenho do sistema, normalmente é devido a um dos seguintes motivos:
  • Um SPID mantém bloqueios em um conjunto de recursos por um longo período de tempo antes liberando-os. Esse tipo de bloqueio resolve-se ao longo do tempo, mas pode causar degradação do desempenho.
  • Um SPID mantém bloqueios em um conjunto de recursos e nunca versões-los. Esse tipo de bloqueio não resolve a mesmo e impede o acesso aos recursos afetados indefinidamente.
Na primeira situação acima, o problema de bloqueio elimina-se ao longo do tempo como o SPID libera os bloqueios. No entanto, a situação pode ser muito fluida como diferente SPIDs causa bloqueio em recursos diferentes ao longo do tempo, criando um destino de movimentação. Por esse motivo, essas situações podem ser difícil solucionar problemas usando o SQL Server Enterprise Manager ou consultas SQL individuais. Os resultados de situação segundo 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 monitoram constantemente o estado de bloqueio e bloqueio no SQL Server. Esses scripts podem fornecer instantâneos de instâncias específicas ao longo do tempo, levando a uma visão geral do problema. Para obter uma descrição de 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 no SQL Server 2000
Os scripts neste artigo executará as tarefas abaixo. Sempre que possível, o método para obter essas informações do SQL Server Management Studio é fornecido.
  1. Identifique o SPID (ID de sessão) no topo da cadeia de bloqueio e a instrução SQL.
    Juntamente com usando os scripts do artigo da Base de dados de Conhecimento mencionado anteriormente, você pode identificar o cabeçote da cadeia de bloqueio usando recursos fornecidos por meio do SQL Server Management Studio. Para fazer isso, use um dos seguintes métodos:
    • Clique com o botão direito do mouse no objeto de servidor, expanda relatórios, expanda Relatórios padrão e, em seguida, clique em Activity ? 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 "Bloqueado SQL geral."
    • Use DBCC INPUTBUFFER(<spid>) para localizar a última instrução foi enviada por um SPID.
  2. Encontrar o nível de aninhamento de transação e o status de processo do bloqueio SPID.
    O nível de aninhamento de transação de um SPID está disponível na variável global @@ TRANCOUNT. No entanto, ele pode ser determinado de fora o SPID consultando a tabela sysprocesses 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. Mostra o nível de aninhamento de transação para o SPID bloqueio, que por sua vez, pode explicar por que ele está mantendo bloqueios. Por exemplo, se o valor for maior que zero, o SPID é no meio de uma transação (nesse caso, espera-se que ele retém certos bloqueios que tenha adquirido, dependendo do nível de isolamento de transação).

    Você também pode verificar se existe qualquer transação aberta a longo prazo no banco de dados por meio de DBCC OPENTRAN database_name.

Coleta informações de rastreamento do SQL Server Profiler

Juntamente com as informações acima, muitas vezes é necessário capturar um rastreamento do Profiler das atividades no servidor para investigar detalhadamente um problema de bloqueio no SQL Server. Se um SPID executar 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 a razão ainda estão sendo mantidos bloqueios. Um rastreamento do Profiler permitirá que você veja todos os comandos executados por um SPID dentro da transação atual. As etapas a seguir ajudam você a configurar o SQL Server Profiler para capturar um rastreamento.
  1. Abra o SQL Server Profiler.
  2. No menu arquivo, aponte para novo e 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 escrito em um disco local ou compartilhado rápido. Evite o rastreamento para uma unidade de disco ou rede lenta. Verifique também que Server processa o rastreamento de dados estã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 eventos 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, pode decidir usar somente os eventos na tabela 1, como são normalmente suficientes para solucionar a maioria dos problemas de bloqueio. Incluindo eventos adicionais na tabela 2 pode tornar mais fácil determinar rapidamente a origem de um problema (ou esses eventos podem ser necessários para identificar a instrução culpado em um procedimento multi-statement). No entanto, incluindo eventos na tabela 2 será também adicionar a carga no sistema e aumentar o tamanho de saída do rastreamento.
a tabela 1: tipos de eventos
Recolher esta tabelaExpandir esta tabela
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
TSQLSQL:BatchStarting

Tabela 2: tipos de eventos adicionais
Recolher esta tabelaExpandir esta tabela
TítuloEvento
TransaçõesDTCTransaction
TransaçõesSQLTransaction
Procedimentos armazenadosRPC: Completed
TSQLSQL:BatchCompleted
Procedimentos armazenadosSP:StmtStarting
Procedimentos armazenadosSP:StmtCompleted

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

Identificando e resolvendo o bloqueio de cenários comuns

Examinando as informações acima, você pode determinar a causa dos problemas de bloqueio mais. O restante deste artigo é uma discussão sobre como usar essas informações para identificar e resolver alguns cenários comuns de bloqueio. Esta discussão pressupõe que 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.

Exibindo a saída do script de bloqueio

Examine a saída sys.sysprocesses para determinar os cabeçotes de cadeias de bloqueio
Se você não tiver especificado o modo rápido para os scripts de bloqueio, haverá uma seção intitulada "SPIDs no topo do bloqueio cadeias" que lista os SPIDs estão bloqueando outros SPIDs na saída do script.
SPIDs at the head of blocking chains
se especificado na opção rápida, você ainda pode determinar os cabeçotes de bloqueio, examinar a saída sys.sysprocesses e seguir a hierarquia do SPID é relatado na coluna bloqueada.
Examine a saída sys.sysprocesses para obter informações sobre os SPIDs no topo da cadeia de bloqueio.
É importante avaliar os seguintes campos sys.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 para que o aplicativo enviar outra consulta ou em lotes. Um status de executáveis, Executar ou sos_scheduler_yield indica que o SPID está atualmente processando uma consulta. A tabela a seguir fornece explicações breves de vários valores de status.
Recolher esta tabelaExpandir esta tabela
StatusO que significa
Plano de fundoO SPID está executando uma tarefa em segundo plano, como a detecção de deadlock.
Em repousoO SPID não está sendo executado no momento. Isso geralmente indica que o SPID está aguardando um comando do aplicativo.
ExecutandoO SPID está sendo executado em um agendador.
ExecutávelO SPID está na fila executável de um agendador e aguardando para obter o tempo do Agendador.
Sos_scheduler_yieldO SPID estava sendo executado, mas ele voluntariamente gerou sua fração de tempo sobre o Agendador para permitir que outro SPID obter tempo do Agendador.
SuspensoO SPID está aguardando um evento, tal como um bloqueio ou uma trava.
ReversãoO SPID está na reversão de uma transação.
DefwakeupIndica que o SPID está aguardando um recurso que está no processo de sendo liberado. O campo waitresource deve indicar o recurso em questão.

Open_tran

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

Lastwaittype, waittype e waittime

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

Para obter uma breve descrição das diferentes lastwaittype e waittype valores, consulte o seguinte artigo da base de dados de Conhecimento da Microsoft:
822101Descriçã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 sys.dm_os_wait_stats, consulte os manuais online do SQL Server.

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

Waitresource

Este campo indica o recurso que está aguardando um SPID. A tabela a seguir lista os formatos comuns de waitresource e seus significados:
Recolher esta tabelaExpandir esta tabela
RecursoFormatoExemplo
TabelaDatabaseID:ObjectID:IndexIDGUIA: 5:261575970:1
Nesse caso, 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 página 104 é uma página que pertencem à tabela de títulos.

Para identificar a identificação do objeto que pertence a página, use o comando DBCC PAGE (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)KEY: 5:72057594044284928 (3300a4f361aa)

Nesse caso, o banco de dados ID 5 é pubs, Hobt_ID 72057594044284928 corresponde ao não agrupado index_id 2 para id de objeto 261575970 (títulos de tabela). Use o modo de exibição de catálogo sys.partitions para associar hobt_id para uma id de índice específico e id de objeto. Não há nenhuma maneira 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, página 104 é uma página que pertencem à tabela de títulos e slot 3 indica a posição da linha na página.
CompilarDatabaseID:ObjectID [[compilação]]TAB: 5:834102012 [[compilação]] não é um bloqueio de tabela, mas em vez disso, uma compilação bloquear em um procedimento armazenado. IDENTIFICAÇÃO do banco de dados 5 é pubs, objeto ID 834102012 usp_myprocedure procedimento armazenado. Consulte 263889 do artigo da Base de Conhecimento para obter mais informações no bloqueio causados por bloqueios de compilação.
Outras colunas

As colunas restantes sys.sysprocesses podem fornecer percepção da raiz do problema também. Sua utilidade varia de acordo com as circunstâncias do problema. Por exemplo, você pode determinar se o problema acontece apenas de determinados clientes de (nome do host), em determinadas bibliotecas de rede (net_library), quando o último lote enviado por um SPID era (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 um waittype diferente de zero, o bloqueio script será executado DBCC INPUTBUFFER para determinar a consulta atual para essa SPID.

Em muitos casos, esta é a consulta que está causando os bloqueios são bloqueadas, outros usuários seja mantido. No entanto, se o SPID estiver dentro de uma transação, os bloqueios podem sido adquiriu por uma consulta executada anteriormente, não aquele atual. Portanto, você também deve exibir a saída do Profiler para o SPID, não apenas o inputbuffer.

Observação: Como o script bloqueio consiste em várias etapas, é possível que um SPID pode aparecer na primeira seção como cabeçalho de uma cadeia de bloqueio, mas no momento em que a consulta de DBCC INPUTBUFFER é executada, ele esteja bloqueando não e a INPUTBUFFER não for capturada. Isso indica que o bloqueio está resolvendo próprio para essa SPID e ele pode ou não ser um problema. Neste ponto, você pode usar a versão rápida do script bloqueio para tentar assegurar que capturar o inputbuffer antes que ele limpa (embora ainda não seja nenhuma garantia) ou exibir os dados de perfil desse período para determinar quais consultas estava sendo executado o SPID.

Exibindo dados Profiler

Exibição de dados do Profiler com eficiência é extremamente valiosa na resolução de problemas de bloqueio. Mais importante é importante perceber que não é necessário que pesquisar em tudo o que você 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, removendo colunas de dados ou eventos, agrupando (classificação) por colunas de dados e aplicação de filtros. Você pode pesquisar o rastreamento de 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 a 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.

Tenha cuidado que você executar a filtragem apenas 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 foi capturados desde que o rastreamento começou. Salvar um rastreamento ativo em um arquivo ou de tabela pela primeira vez (no menu arquivo, clique em <a2>Salvar como</a2>) e reabri-lo (no menu arquivo, clique em Open) antes de prosseguir. Ao trabalhar em um arquivo de rastreamento salvos, a filtragem não remove permanentemente os dados que estão sendo filtrados, simplesmente não exibe todos os dados. Você pode adicionar e remover eventos e colunas de dados conforme necessário para focalizar as pesquisas.

o que procurar:
  • Quais comandos tem o SPID no topo de uma cadeia de bloqueio executado dentro da transação atual?
    Filtrar os dados de rastreamento para um SPID específico que está no cabeçalho de uma cadeia de bloqueio (no menu arquivo, clique em Propriedades; em seguida, na guia filtros, especifique o valor SPID). Você pode então examinar os comandos que foi executado antes para o tempo que ele estava bloqueando outros SPIDs. Se você incluir os eventos de transações, eles podem identificar facilmente quando uma transação foi iniciada. Caso contrário, você pode pesquisar a coluna de texto BEGIN, operações SAVE, COMMIT ou ROLLBACK TRANSACTION. Use o valor open_tran da tabela sysprocesses para garantir que você atualize todos os eventos de transação. Sabendo que os comandos executados e o contexto de transação permitirá que você determinar por que um SPID está mantendo bloqueios.

    Lembre-se de que você pode remover colunas de eventos e dados. Em vez de examinar ambos Iniciando e eventos concluídos, escolha um. Se os SPIDs bloqueio não são procedimentos armazenados, remova o SP: Iniciando ou SP: Completed eventos; os eventos RPC e SQLBatch mostrará a chamada de procedimento. Exiba apenas os eventos de SP quando você precisa ver esse nível de detalhe.
  • O que é a duração das consultas de SPIDs no topo do bloqueio cadeias?
    Se você incluir os eventos concluídos acima, a Duration coluna mostrará o tempo de execução da consulta. Isso pode ajudar você identificar consultas demoradas que estão causando o bloqueio. Para determinar por que a consulta está realizando lentamente, exiba a CPU, leitura e gravações de colunas, bem como o evento de Plano de execução.

Categorização de cenários comuns de bloqueio

A tabela a seguir mapeia sintomas comuns para as 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 a seguir. As colunas WaittypeOpen_Tran e status consultem informações sysprocesses. O resolve? coluna indica se ou não o bloqueio será resolvido por conta própria.

Recolher esta tabelaExpandir esta tabela
CenárioWaittypeOpen_TranStatusResolve?Outras sintomas
1Diferente de zero>= 0executávelSim, quando termina de consulta.Colunas Physical_IO, CPU e/ou Memusage aumentará ao longo do tempo. Duração da consulta será alta quando concluído.
20 x 0000>0em repousoNão, mas pode ser eliminado SPID.Um sinal de atenção pode ser visto no Profiler rastreamento para essa SPID, indicando um tempo limite de consulta ou cancelar ocorreu.
30 x 0000>= 0executávelNão. Não resolverá até que o cliente busca todas as linhas ou fecha a conexão. SPID pode ser eliminado, mas ele pode demorar até 30 segundos.Se open_tran = 0 e o SPID mantém bloqueios enquanto o nível de isolamento de transação é o padrão (READ COMMMITTED), isso é uma provável causa.
4Varia>= 0executávelNão. Não resolverá até que o cliente cancela consultas ou fecha conexões. SPIDs podem ser eliminados, mas podem demorar até 30 segundos.Coluna hostname na sysprocesses para o SPID no topo de uma cadeia de bloqueio será o mesmo como um SPID está bloqueando.
50 x 0000>0reversãoSim.Um sinal de atenção pode ser visto no rastreamento do Profiler para essa SPID, indicando um tempo limite de consulta ou cancelar ocorreu, ou simplesmente uma instrução de reversão foi emitida.
60 x 0000>0em repousoEventualmente. Quando Windows NT determina a sessão é sem mais ativo, o SQL Server conexão será quebrada.O valor last_batchsysprocesses é muito anterior à hora atual.

Bloqueio situações comuns e resoluções

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

    Resolução:
    A solução para esse tipo de problema de bloqueio é procurar maneiras de otimizar a consulta. Na verdade, essa classe de problema de bloqueio pode apenas ser um problema de desempenho e exigir a buscá-lo como tal. Para obter informações sobre como solucionar problemas de uma consulta de execução lenta específica, consulte o seguinte artigo da Base de dados de Conhecimento da Microsoft:
    243589Como solucionar problemas de consultas de execução lenta no SQL Server 7.0 ou versões posteriores
    Aplicativo geral desempenho solução de problemas, consulte o seguinte artigo do Knowledge Base:
    224587COMO: Solucionar problemas de desempenho do aplicativo com o SQL Server
    Para obter mais informações, consulte o tópico de manuais online do SQL Server 2008 monitoramento de desempenho e ajuste tópicos "como" no seguinte site da MSDN:
    http://msdn.microsoft.com/en-us/library/ms187830.aspx
    Se você tiver uma consulta de execução demorada que está bloqueando outros usuários e não pode ser otimizada, considere a possibilidade de movê-la de um OLTP ambiente para um sistema de apoio à decisão.
  2. Bloqueio causados por um SPID bela que foi perdido controle da transação nível de aninhamento do

    Esse tipo de bloqueio geralmente pode ser identificado por um SPID está no modo suspenso ou aguardando um comando ainda cuja nível de aninhamento de transação (@@ TRANCOUNT, open_tran de sysprocesses) é maior que zero. Isso pode ocorrer se o aplicativo passa por um tempo limite de consulta ou emite um cancelamento sem emitir também o número necessário de ROLLBACK e/ou COMMIT instruções. Quando um SPID recebe um tempo limite de consulta ou Cancelar, ele irá finalizar a consulta atual e lote, mas não automaticamente reverter ou confirmar a transação. O aplicativo é responsável por isso, como SQL Server não pode presumir que uma transação inteira deve ser revertida simplesmente devido a uma única consulta sendo cancelada. O tempo limite de consulta ou cancelar aparecerá como um evento de sinal atenção para o SPID no rastreamento do Profiler.

    Para demonstrar isso, execute a seguinte consulta simples Query Analyzer:

    BEGIN TRAN 
    SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2
    
    -- Issue this after canceling query
    SELECT @@TRANCOUNT
    ROLLBACK TRAN
    						
    Durante a execução da consulta, clique no vermelho botão Cancelar. Após a consulta for cancelada, SELECT @@ TRANCOUNT indica que o nível de aninhamento de transação é um. Isso era um DELETE ou UPDATE uma consulta, ou HOLDLOCK tenha sido usada em SELECT, todos os bloqueios adquiridos seriam ainda ser mantido. Mesmo com a consulta acima, se outra consulta tiver adquirido e mantido bloqueios anteriormente na transação, eles seriam ainda ser retidos quando SELECT acima foi cancelada.

    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, envie um IF @@ TRANCOUNT > 0 TRAN ROLLBACK seguindo 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 poderia ter iniciado uma transação sem o conhecimento do aplicativo cliente. Observe que determinadas condições, como a consulta, o cancelamento impedem o procedimento de executar a instrução atual, então, mesmo se o procedimento tem lógica para verificar se @@ erro <>0 e anular a transação, esse código de reversão não poderã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 estão limpando após um erro. No caso de um erro em tempo de execução, esta configuração irá anular todas as transações abertas e retornar o controle para o cliente. Observe que instruções T-SQL após a instrução que causou o erro não serão executadas.
      3. Se o pool de conexão estiver sendo usado em um aplicativo que abre a conexão e executa um pequeno número de consultas antes de liberar a conexão do pool, como um aplicativo baseado na Web, desative temporariamente o pool de conexões pode ajudar a aliviar o problema até que o aplicativo cliente seja modificado para manipular os erros apropriadamente. Desativando o pool de conexão, liberando a conexão fará com que um logout físico da conexão do SQL Server, resultando em um servidor de reversão de 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éfica. Esta versão dos componentes MDAC adiciona código para o driver ODBC e provedor OLE DB para que a conexão deve ser "Redefinir" antes que ela é reutilizada. Esta chamada para sp_reset_connection anula quaisquer transações iniciadas pelo servidor (as transações do DTC iniciadas pelo aplicativo cliente não são afetadas), redefine o banco de dados padrão, opções SET e assim por diante. Observe que a conexão não é redefinida até que é reutilizada do pool de conexão, portanto, é possível que um usuário poderia abrir uma transação e, em seguida, solte a conexão com o pool de conexão, mas não poderá ser reutilizado por vários segundos, durante esse período a transação permanecerá aberta. Se a conexão não está sendo reutilizada, a transação será anulada quando a conexão expira e será removida do pool de conexão. Portanto, é ideal para o aplicativo de cliente anular a transações em sua rotina de tratamento de erros ou SET XACT_ABORT ON para evitar essa demora possível.
    • Na verdade, essa classe de bloqueio de problema pode também ser um problema de desempenho e exigem que você buscá-lo como tal. Se o tempo de execução da consulta pode ser diminuído, o tempo limite de consulta ou Cancelar não ocorrerá. É importante que o aplicativo seja capaz de lidar com o tempo limite ou cancelar cenários devem elas surgem, mas você também pode beneficiar examinar o desempenho da consulta.
  3. Bloqueio causados por um SPID cujo aplicativo de cliente correspondente não extrair todas as linhas de resultado para conclusão

    Após o envio de uma consulta ao servidor, todos os aplicativos devem buscar imediatamente todas as linhas de resultado para conclusão. Se um aplicativo não 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 SQL instruções ao servidor, o aplicativo deve buscar todas as linhas de resultado. Se não existir (e se ele não pode ser configurado para fazer isso), você pode não conseguir resolver o problema de bloqueio. Para evitar o problema, você pode restringir aplicativos mal agiu para um relatório ou um banco de dados de suporte a decisões.

    Resolução:

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

    Ao contrário de um deadlock convencional, um deadlock distribuído não é detectável usando o Gerenciador de bloqueio RDBMS. Isso se deve ao fato de que apenas um dos recursos envolvidos no deadlock é um bloqueio do SQL Server. O outro lado do deadlock é no nível do aplicativo cliente, através do qual o SQL Server não tem nenhum controle. A seguir estão dois exemplos de como isso pode acontecer e possíveis maneiras do aplicativo podem ser evitada.

    1. Cliente/servidor distribuído bloqueio com um cliente único thread
      Se o cliente tiver várias conexões abertas e um único segmento de execução, o deadlock distribuído seguinte pode ocorrer. Por questões de brevidade, o termo "dbproc" usado aqui se refere à 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 do aplicativo cliente único tem duas conexões abertas. Ele envia assincronamente uma operação de SQL em dbproc1. Isso significa que não espera na chamada para retornar antes de prosseguir. O aplicativo, em seguida, envia outra operação SQL em dbproc2 e aguarda os resultados para iniciar o processamento de dados retornados. Quando os dados começam voltem (qualquer dbproc responde primeiro--assumem é dbproc1), ele processa a conclusão de todos os dados retornados em que dbproc. Ela busca os resultados dos dbproc1 até SPID1 fica bloqueado em um bloqueio mantido por SPID2 (porque as duas consultas estão executando assincronamente no servidor). Neste ponto, dbproc1 aguardará indefinidamente mais dados. SPID2 não está bloqueado em um bloqueio, mas tentar enviar dados ao cliente, dbproc2. No entanto, dbproc2 está efetivamente bloqueado no dbproc1 na camada de aplicativo como o único thread de execução para o aplicativo estiver em uso por dbproc1. Isso resulta em um deadlock que SQL Server não pode detectar ou resolver porque somente 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 estiver executando uma instrução SELECT com a intenção de executar a linha por vez processamento e enviar cada linha por meio de um buffer para dbproc1 para um INSERT, UPDATE ou DELETE instrução na mesma tabela. Eventualmente, SPID1 (realizando a INSERT, UPDATE ou DELETE) fica bloqueado em um bloqueio mantido por SPID2 (realizando a SELECT). SPID2 grava uma linha de resultado dbproc2 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 o INSERT atual, que está bloqueado no SPID2). Neste ponto, dbproc2 é bloqueado na camada de aplicativo por dbproc1 cujo SPID (SPID1) está bloqueada no nível do banco de dados por SPID2. Novamente, isso resulta em um deadlock que SQL Server não pode detectar ou resolver porque somente um dos recursos envolvidos é um recurso do SQL Server.
    Ambos os exemplos A e B são problemas fundamentais que os desenvolvedores de aplicativos devem estar cientes. Eles devem o código de aplicativos para lidar com esses casos apropriadamente.

    Resoluções:

    Duas soluções confiáveis devem usar conexões acoplados ou um tempo limite da consulta.

    • Tempo limite da consulta
      Quando um tempo limite de consulta foi fornecido se ocorrer deadlock distribuído, ele irá ser quebrado quando, em seguida, tempo limite ocorre. Consulte a biblioteca de banco de dados ou 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 em um espaço ú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 de encadernação" nos manuais online do SQL Server 7.0.
  5. Bloqueio causados por um SPID que está em um "Golden," ou Rollback, estado

    Uma consulta de modificação de dados que é eliminada ou cancelada fora de uma transação definida pelo usuário, será revertida. Isso também pode ocorrer como efeito colateral da reinicialização do computador cliente e sua sessão de rede, desconectando-se. Da mesma forma, uma consulta selecionada como a vítima de deadlock será revertida. Uma consulta de modificação de dados geralmente não pode ser revertida qualquer mais rápido do que as alterações foram aplicadas inicialmente. Por exemplo, se uma instrução DELETE, INSERT ou UPDATE estavam sendo executados para uma hora, ele poderá ter pelo menos uma hora para reverter. Isso é o comportamento esperado, porque as alterações feitas devem ser completamente restauradas, ou comprometimento de integridade transacional e física do banco de dados. Como isso deve acontecer, SQL Server marca o SPID em um estado "ouro" ou reverter (que significa que não pode ser eliminado ou selecionadas como vítima de deadlock). Com freqüência podem ser identificado observando a saída do sp_who, que pode indicar o comando ROLLBACK. A coluna status de sys.sysprocesses indica um status ROLLBACK, 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 a Revertendo as alterações que foram feitas.

    Se o servidor está desligado no meio desta operação, o banco de dados ficará no modo de recuperação na reinicialização e estarão inacessível até que todas as abertas as transações são processadas. Inicialização recuperação leva essencialmente a mesma quantidade de tempo por transação como recuperação de tempo de execução e o banco de dados está inacessível durante esse período. Assim, forçar o servidor para baixo para corrigir um SPID em um estado de reversão geralmente será contraproducente.

    Para evitar essa situação, não execute o lote grande INSERT, UPDATE, ou DELETE operações durante o horário ocupado em sistemas OLTP. Se possível, realizar tais operações durante os períodos de pouca atividade.
  6. Bloqueio causados por uma conexão órfão

    Se as interrupções de aplicativos cliente ou estação de trabalho da cliente for reiniciada, a sessão de rede para o servidor não pode ser imediatamente cancelada sob algumas condições. Da perspectiva do servidor, o cliente parece estar presente e quaisquer bloqueios adquiridos ainda pode ser mantidos. Para obter mais informações, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
    137983Como solucionar problemas de conexões órfãos no SQL Server

    Resolução:

    Se o aplicativo cliente foi desconectado sem limpeza de seus recursos apropriadamente, você pode encerrar o SPID usando o comando KILL. O comando KILL obtém o valor SPID como entrada. Por exemplo, a kill SPID 9, basta emita o comando a seguir:

    KILL 9
    						

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

Envolvimento de aplicativo no bloqueio de problemas

Pode haver tendem a se concentrar nas questões de ajuste e a plataforma de servidor quando enfrentando um problema de bloqueio. No entanto, isso geralmente não levar a uma resolução e pode absorver o 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 que está sendo feitas, um problema de bloqueio apesar com freqüência requer a inspeção das instruções SQL exatas enviada pelo aplicativo e o comportamento exato do aplicativo com 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 permitir explícita controle sobre gerenciamento de conexão, cancelamento de consulta, tempo limite de consulta, resultado de busca e assim por diante, problemas de bloqueio podem não ser resolvível. Esse potencial deve ser examinado atentamente antes de selecionar uma ferramenta de desenvolvimento de aplicativo para SQL Server, especialmente para ambientes OLTP essenciais aos negócios.

É vital que muita atenção ser exercidos durante a fase de projeto e construção de aplicativos e bancos de dados. Em particular, o consumo de recursos, nível de isolamento e comprimento do caminho de transação devem ser avaliadas para cada consulta. Cada consulta e a transação devem ser tão simples possível. Deve ser exercida disciplina de gerenciamento de boa conexão. Se não for feito, é possível que o aplicativo pode parecer ter um desempenho aceitável com baixo número de usuários, mas o desempenho pode cair significativamente como o número de usuários pode ser expandido para cima.

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

Propriedades

ID do artigo: 224453 - Última revisão: sexta-feira, 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 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
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 traduzido ou revisto por pessoas. A Microsoft possui artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais, com o objetivo de oferecer em português a totalidade dos artigos existentes na base de dados de suporte. No entanto, a tradução automática não é sempre perfeita, podendo conter erros de vocabulário, sintaxe ou gramática. A Microsoft não é responsável por incoerências, erros ou prejuízos ocorridos em decorrência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza atualizações freqüentes 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