INF: Noções sobre e resolver problemas de bloqueio do SQL Server

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
Sumário
Neste artigo, o termo "ligação" refere-se a uma única iniciada sessão da base de dados. Cada ligação aparece como um ID de sessão (SPID). Cada uma destas SPID é frequentemente referida como um processo, embora não seja um contexto de processo separado no sentido habitual. Em vez disso, cada SPID consiste a recursos do servidor e as estruturas de dados necessárias para servir os pedidos de uma única ligação de um determinado cliente. Uma aplicação de cliente individual poderá ter uma ou mais ligações. Na perspectiva do SQL Server, não existe nenhuma diferença entre várias ligações 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 a outra ligação, independentemente se provêm da mesma aplicação ou aplicações separadas em dois computadores de cliente diferente.
Mais Informação
Bloquear é uma característica inevitável de qualquer sistema de gestão de bases de dados relacionais (RDBMS) com simultaneidade baseado em bloquear. No SQL Server, 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 o qual o SPID primeiro bloqueia o recurso é muito pequeno. Quando liberta o bloqueio, a segunda ligação é livre de adquirir próprio bloqueio no recurso e continuar o processamento. Este é o comportamento normal e pode ocorrer muitas vezes ao longo do curso de um dia com um efeito considerável no desempenho do sistema.

O contexto de transacções e duração de uma consulta determinar por quanto tempo respectivos bloqueios são mantidos e, desse modo, seu impacto no 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 mantidos 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 dentro de uma transacção, a duração para o qual os bloqueios sejam detidos são determinadas pelo tipo de consulta, o nível de isolamento de transacção e se é ou não bloquear 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ções, consulte os seguintes tópicos no SQL Server Books Online:
  • O bloqueio no motor de base de dados
  • Controlo de versão de linha e de bloqueio de personalização
  • Modos de bloqueio
  • Compatibilidade de bloqueio
  • Níveis de isolamento baseada no controlo de versão de linha 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 em que exista um efeito prejudicial no desempenho do sistema, é normalmente causado por uma das seguintes razões:
  • Um SPID detém bloqueios num conjunto de recursos para um extendedperiod de tempo antes de os libertar. Este tipo de bloqueio resolve o tempo de itselfover, mas pode provocar a degradação do desempenho.
  • Um SPID detém bloqueios num conjunto de recursos e nunca releasesthem. Este tipo de bloqueio não resolve propriamente dito e impede o acesso a recursos de afectado 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 causa SPID por parte do bloqueio de recursos diferentes ao longo do tempo, criando um movimento de destino. Por este motivo, estas situações podem ser difíceis de resolver problemas utilizando o SQL Server Enterprise Manager ou consultas SQL individuais. A segunda situação resulta num estado consistente que pode ser mais fácil diagnosticar.

A reunir informações de bloqueio

Para obviar 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 de como monitorar o bloqueio com scripts SQL, consulte os seguintes artigos na Microsoft Knowledge Base:
271509 Como monitorar o bloqueio no SQL Server 2005 e no SQL Server 2000
Os scripts neste artigo irão executar as tarefas abaixo. Sempre que possível, é dado o método para obter esta informação do SQL Server Management Studio.
  1. Identifica o SPID (ID da sessão) no topo da cadeia de bloqueio e a instrução SQL.
    Além de utilizar os scripts no artigo da Knowledge Base anteriormente mencionado, youcan identificar a cabeça 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:
    • Botão direito do rato no objecto de servidor, expanda relatórios, expanda Relatórios padrãoe, 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 mostrará as transacções que são bloqueadas pela transacção cabeça. Este relatório também mostrará a "bloqueio de instrução de SQL" e "Bloqueado instrução SQL."
    • Utilizar DBCC INPUTBUFFER (<spid>) para localizar a última declaração que apresentou um SPID.</spid>
  2. Encontre o nível de aninhamento de transacção e o estado do processo do SPID bloqueio.
    O nível de aninhamento de transacção de um SPID está disponível na variável global de the@@TRANCOUNT. No entanto, pode ser determinado da theSPID exterior 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 de @@TRANCOUNT para o SPID. Esta transacção de showsthe nível de aninhamento para o bloqueio SPID, que por sua vez, pode explainwhy-lo possui bloqueios. Por exemplo, se o valor for maior que zero, é theSPID ao meio de uma transacção (nesse caso é expectável que itretains determinadas bloqueie tiver adquirido, consoante o isolationlevel de transacção).

    Também pode verificar para ver se algum a longo prazo abrir transactionexists na base de dados utilizando DBCC OPENTRANnome_da_base_de_dados.

Recolher informações de rastreio do SQL Server Profiler

Para além das informações acima referidas, é frequentemente necessário capturar um rastreio Profiler das actividades no servidor para examinar exaustivamente um problema de bloqueio no SQL Server. Se um SPID executa várias instruções dentro de uma transacção, 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 utilizados. Um rastreio Profiler permite-lhe ver todos os comandos executados por um SPID na transacção actual. Os passos seguintes ajudam-na configurar o SQL Server Profiler para capturar um rastreio.
  1. Abra o SQL Server Profiler.
  2. No menu ficheiro , aponte para Novoe, em seguida, clique em Rastrear.
  3. No separador Geral , especifique um nome de rastreio e um nome de ficheiro para capturar os dados.

    Importante O ficheiro de rastreio deve ser escrito para um disco local ou partilhado rápida. Evite o rastreio para uma unidade de disco ou rede lenta. Além disso, certifique-se de que o servidor processa o rastreio de dados estão seleccionados.
  4. No separador Eventos de selecção , clique para seleccionar caixas de verificação Mostrar todas as colunas e Mostrar todos os eventos .
  5. No separador Selecção de eventos , adicione os tipos de eventos que estão listados 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 resolver a maior parte dos problemas de bloqueio. Incluindo os eventos adicionais no quadro 2 pode facilitar para rapidamente determinar a origem de um problema (ou estes eventos podem ser necessários para identificar a instrução culprit num procedimento com várias instruções). No entanto, incluindo eventos no quadro 2 irá também aumentar a carga no sistema e aumentar o tamanho de saída de rastreio.
Quadro 1: Tipos de eventos
TítuloEvento
Erros e avisosExcepção
Erros e avisosAtenção
Auditoria de segurançaInício de sessão de auditoria
Auditoria de segurançaTerminar sessão de auditoria
SessõesLigação existente
Procedimentos armazenadosRPC: início
TSQLSQL:BatchStarting

Quadro 2: Tipos de eventos adicionais
TítuloEvento
TransacçõesTransacções DTC
TransacçõesSQLTransaction
Procedimentos armazenadosRPC: concluída
TSQLSQL:BatchCompleted
Procedimentos armazenadosEventos SP: StmtStarting
Procedimentos armazenadosStmtCompleted

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

Identificação e resolução de cenários comuns de bloqueio

Ao examinar as informações acima referidas, pode determinar a causa da maior parte 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 utilizou os scripts de bloqueio no artigo 271509 (com referência anteriormente) para capturar informações sobre o SPID por parte de bloqueio e efectuou um rastreio Profiler com os eventos descritos acima.

Ver o resultado do Script bloqueio

Examine os resultados de sys.sysprocesses para determinar os chefes das cadeias de bloqueio
Se não especificou um modo rápido para os scripts de bloqueio, existirá uma secção intitulada "Os SPIDs no topo das cadeias de bloqueio" thatlists os SPID que estão a impedir outros SPID no resultado do script.
SPIDs at the head of blocking chains
Se tiver especificado a opção rápida, pode ainda determinar theblocking cabeças consultando o resultado de sys.sysprocesses e após a hierarquia do SPID é indicada na coluna bloqueada.
Examine os resultados de sys.sysprocesses para obter informações sobre os SPID no topo da cadeia de bloqueio.
É importante avaliar os seguintes campos de 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 enviar outra consulta ou batch. Um Estado de executáveis, Executarou sos_scheduler_yield indica que o SPID está actualmente a processar uma consulta. A tabela seguinte fornece explicações resumidas sobre os vários valores de estado.
EstadoSignificado
FundoO SPID está a executar uma tarefa de segundo plano, como a detecção de impasse.
No modo de suspensãoO SPID não está a executar actualmente. Isto normalmente indica que o SPID aguarda um comando da aplicação.
Em execuçãoO SPID está actualmente em execução um programador.
ExecutáveisO SPID é na fila de executáveis de um programador e a aguardar a obtenção de hora de programador.
Sos_scheduler_yieldO SPID estava em execução, mas este tiver produzido voluntariamente respectivo ciclo de tempo em que o Programador de tarefas 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 é na Anulação de uma transacção.
DefwakeupIndica que o SPID está à espera de um recurso que está em processo 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 é dentro de uma transacção aberta e pode manter os bloqueios adquiridos por qualquer declaração na transacção.

Lastwaittype, TipoEspera e TempoEspera

O campo lastwaittype é uma representação em cadeia do campo TipoEspera , que é uma coluna binária interna reservada. Se o TipoEspera 0x0000, o SPID não está actualmente a aguardar para qualquer documento e o valor de lastwaittype indica o último TipoEspera que tinha o SPID. Se o TipoEspera não for zero, o valor de lastwaittype indica o actual TipoEspera do SPID.

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

O valor de TempoEspera pode ser utilizado para determinar se o SPID está a fazer progresso. Quando uma consulta a tabela sys.sysprocesses devolve um valor na coluna TempoEspera que é inferior ao valor TempoEspera de uma consulta anterior de sys.sysprocesses, isto indica que o bloqueio anterior foi adquirido e libertado e está a aguardar um bloqueio de novo (assumindo TempoEspera diferente de zero). Isto pode ser verificado, comparando o waitresource entre a produção de sys.sysprocesses .

Waitresource

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

Para identificar o id de objecto que pertence a página, utilize o comando DBCC página (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, é de 5 de ID da base de dados Pubs, Hobt_ID 72057594044284928 corresponde a index_id não agrupado 2 para o id de objecto 261575970 (tabela detítulos ). Utilize a vista do catálogo de sys.partitions para associar o hobt_id para um id de índice específica e o id de objecto. Não existe nenhuma forma de unhash o hash de chave do índice para um valor de chave do índice específico.
LinhaDatabaseID:FileID:PageID:Slot(row)RID: 5:1:104:3

Neste caso, é de 5 de ID da base de dados pubs, ficheiro ID 1 é o ficheiro de dados primário, página 104 é uma página que pertencem à tabela de títulos e ranhura 3 indica a posição à linha na página.
CompilaçãoDatabaseID:ObjectID [[compilação]]SEPARADOR: 5:834102012 [[compilação]] não é um bloqueio de tabela, mas em vez disso, uma compilação bloquear um procedimento armazenado. ID da base de dados 5 é pubs, 834102012 de ID de objecto é usp_myprocedure procedimento armazenado. Consulte a Knowledge Base artigo 263889 para mais informações sobre o bloqueio causados por bloqueios de compilação.
Outras colunas

As restantes colunas de sys.sysprocesses podem fornecer pistas 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 a última secção apresentou um SPID foi (last_batch), e assim sucessivamente.
Examine os resultados de DBCC INPUTBUFFER.
Para qualquer SPID no topo de uma cadeia de bloqueio ou com TipoEspera anon de zero, o script bloqueio será executado o DBCC INPUTBUFFER todetermine consulta actual para esse SPID.

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

Nota Uma vez que o script de bloqueio é constituída por vários passos, ispossible que um SPID poderá aparecer na primeira secção como chefe de um blockingchain, mas na altura em que a consulta de DBCC INPUTBUFFER for executada, é não longerblocking e o INPUTBUFFER não for capturada. Isto indica que o blockingis resolver-se de que o SPID pelo que pode ou não ser um problema. A thispoint, pode utilizar a versão do script bloqueio rápida para tentar prestará capturar a inputbuffer antes de que limpa (embora ainda seja noguarantee), ou visualizar o Profiler dados a partir desse período de tempo para determinar a whatqueries o SPID estava em execução.

Visualizar os dados do Profiler

A visualização de dados do Profiler eficientemente é extremamente valiosa na resolução de problemas de bloqueio. O mais importante que compreenda é que não é necessário observar tudo capturadas; seja selectivo. Profiler fornece capacidades para ajudá-lo eficazmente visualizarem dados capturados. Na caixa de diálogo Propriedades (no menu ficheiro , clique em Propriedades), Profiler permite-lhe limitar os dados apresentados removendo colunas de dados ou acontecimentos, agrupamento (ordenação) por colunas de dados e aplicação de 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 do Profiler a uma tabela de SQL Server (no menu ficheiro , aponte para Guardar como e, em seguida, clique em tabela) e executar consultas SQL contra ele.

Certifique-se 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 primeiro (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, basta não apresentar todos os dados. Pode adicionar e remover eventos e de colunas de dados conforme necessário para ajudar a focar as procuras.

O que procurar:
  • Quais os comandos tem o SPID liderado um bloqueio chainexecuted na transacção actual?
    Filtrar os dados de rastreio para aparticular SPID que se encontra à cabeça de uma cadeia de bloqueio (no menu ficheiro , clique em Propriedades; em seguida, no separador filtros , especifique o valor SPID). Em seguida, pode examinar o prévio de maneira executados comandos até que estava a bloquear outros SPID. Se incluir eventos de theTransaction, facilmente identificável quando foi iniciada uma transacção.Caso contrário, pode procurar a coluna de texto de início, guardar, consolidação ou reversão TRANSACTIONoperations. Utilize o valor de open_tran da tabela ' sysprocesses ' para garantir que captura todos os eventos de transacção.Conhecer os comandos executados e o contexto de transacção permitirá todetermine por que motivo um SPID possui bloqueios.

    Lembre-se de que é possível colunas removeevents e dados. Em vez de olhar para iniciar e completedevents, escolha um. Se o SPID bloqueio não procedimentos armazenados, retirar oSP: Iniciar ou SP: concluída eventos; os eventos SQLBatch e o RPC irão mostrar a chamada de procedimento. Ver só a whenyou de eventos SP necessário para ver esse nível de detalhe.
  • O que é a duração das consultas para os SPIDs em headof a cadeias de bloqueio?
    Se incluir os eventos concluídos acima, a coluna duração mostrará o tempo de execução de consulta. Isto pode ajudar a consultas de execução longa de youidentify que estão a causar o bloqueio. Para determinar por que razão thequery ficar lento, ver a CPU, leiturae escreve colunas, bem como o evento de Plano de execução .

Categorizar cenários comuns de bloqueio

A tabela abaixo mapeia sintomas comuns das suas causas prováveis. O número indicado na coluna cenário corresponde ao número na secção "Comum bloqueio cenários e soluções" deste artigo abaixo. As colunas TipoEspera, Open_Trane Estado Consulte informações de sysprocesses . O resolve? coluna indica se é ou não o bloqueio resolverá sozinho.

CenárioTipoEsperaOpen_TranEstadoResolve?Outros sintomas
1De zero> = 0executáveisSim, quando concluir a consulta.Colunas de Physical_IO, da CPU e/ou Memusage irão aumentar ao longo do tempo. Duração para a consulta será elevada quando concluída.
20x0000 por> 0no modo de suspensãoNão, mas que possam ser abatido SPID.Um sinal de atenção poderão ser visualizado no rastreio Profiler para este SPID, indicando um tempo limite de consulta ou Cancelar ocorreu.
30x0000 por> = 0executáveisN. º Não irá resolver até que o cliente obtém todas as linhas ou fecha a ligação. SPID possam 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ção é a predefinição (COMMMITTED de leitura), esta é uma causa provável.
4Varia> = 0executáveisN. º Não resolverá o cliente cancela consultas até ou até fecha ligações. SPID possam ser abatidos, mas podem demorar até 30 segundos.A coluna de nome de anfitrião na sysprocesses para o SPID no topo de uma cadeia de bloqueio deve ser a mesma como um do SPID que está a bloquear.
50x0000 por> 0anulaçãoSim.Um sinal de atenção poderão ser visualizado no rastreio Profiler para este SPID, indicando um tempo limite de consulta ou Cancelar ocorreu ou simplesmente uma declaração de anulação foi emitida.
60x0000 por> 0no modo de suspensãoAcabará por. Quando o Windows NT determina que a sessão já não está activa, a ligação do SQL Server será quebrada.O valor de last_batch na sysprocesses é muito anterior à hora actual.

Cenários de bloqueio e resoluções comuns

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

    Resolução:
    A solução para este tipo de problema de bloqueio é a procura forways para optimizar a consulta. Na realidade, este tipo de problema de bloqueio pode justbe um problema de desempenho e requerem a exercer, como tal. Para informações relativas a uma consulta específica de atrasar a execução de resolução de problemas, consulte o seguinte artigo na Microsoft Knowledge Base:
    243589 Como resolver problemas de atrasar a execução consultas SQL Server 7.0 ou versões posteriores
    Para performancetroubleshooting de aplicação geral, consulte o seguinte artigo da Knowledge Base:
    224587 COMO: Resolver problemas de desempenho de aplicações com o SQL Server
    Para mais informações, consulte o tópico de SQL Server 2008 Books Online de monitorização de desempenho e optimização tópicos de procedimentos no seguinte Web site da MSDN: Se tiver uma consulta de execução demorada que é blockingother utilizadores e não pode ser optimizada, considere movê-lo a partir de um OLTPenvironment para um sistema de apoio da decisão.
  2. Bloquear causado por um SPID dormir que tenha perdido a controlar o nível de aninhamento de transacção

    Este tipo de bloqueio pode frequentemente ser identificado por um SPIDthat está no modo de suspensão ou aguarda um comando, mas cujo nível de aninhamento de transacção (@@TRANCOUNT, open_tran de sysprocesses) for maior que zero. Isto pode ocorrer se a applicationexperiences um tempo limite de consulta, ou emite um cancelamento sem emitir também o número de therequired das demonstrações de anulação e/ou consolidação. Quando recebe um SPID aquery tempo de espera ou em Cancelar, irá terminar a consulta actual e a secção, butdoes automaticamente recuperar nem consolidar a transacção. O isresponsible de aplicação para que isto, tal como o SQL Server não é possível partem do princípio de que um transactionmust todo ser revertido simplesmente devido a uma única consulta a ser cancelado. O querytimeout ou Cancelar aparecerá como um evento de sinal de atenção para o SPID num rastreio do theProfiler.

    Para demonstrar isto, 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á em execução, clique no botão Cancelar . Depois da consulta é cancelada, seleccione @@TRANCOUNT indicatesthat o nível de aninhamento de transacção é um. Isto foi uma eliminação ou de um UPDATEquery ou HOLDLOCK tivesse sido utilizada no, seleccione, realizar-se todos os wouldstill os bloqueios adquiridos. Mesmo com a consulta acima, se outra consulta tinha adquirido andheld bloqueios anteriormente na transacção, se ainda serão realizadas quando o aboveSELECT foi cancelada.

    Resoluções:

    • Aplicações tem de gerir correctamente os níveis de aninhamento de transacção, ou que possam causar um problema de bloqueio após o cancelamento da consulta desta forma. Pode fazê-lo de várias formas:
      1. O processador de erros da aplicação cliente, apresentar um se @@TRANCOUNT > 0 SR Silva anulação seguinte qualquer erro, mesmo que a aplicação cliente não acredita uma transacção é abrir. Isto é necessário, uma vez que um procedimento armazenado chamado durante o processo poderia ter iniciado uma transacção sem o conhecimento da aplicação cliente. Nota que determinadas condições, como a cancelar a consulta, impede o procedimento de execução passados a instrução actual, por isso, mesmo se o procedimento tem lógica para verificar se @@ERROR <> 0 e interromper a transacção, este código de anulação não será executado em tais casos.
      2. Utilize definir ON XACT_ABORT para a ligação, ou em quaisquer procedimentos armazenados que começar a transacções e não são limpar sequência de um erro. Caso ocorra um erro de tempo de execução, esta definição irá abortar quaisquer transacções abertas e devolver o controlo para o cliente. Tenha em atenção que as demonstrações de T-SQL após a instrução que causou o erro não serão executadas.
      3. Se o agrupamento de ligações está a ser utilizado numa aplicação que abre a ligação e é executado um pequeno número de consultas antes de libertar a ligação para o conjunto, por exemplo, 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 para tratamento de erros de forma adequada. Ao desactivar o agrupamento de ligações, libertar a ligação fará com que um logout física da ligação do SQL Server, resultando no servidor a anular qualquer transacções abertas.
      4. Se o pooling de conexões estiver activado e o servidor de destino é o SQL Server 2000, poderá ser benéfico actualizar o computador de cliente para o MDAC 2.6 ou posterior. Esta versão dos componentes do MDAC adiciona código para o controlador ODBC e o fornecedor de OLE DB para que a ligação ser "Repor" antes de ser reutilizado. Esta chamada para sp_reset_connection interrompe qualquer transacções iniciadas por servidor (transacções DTC iniciadas por aplicação de cliente não são afectadas), repõe a base de dados predefinida, definir opções e por aí em diante. Tenha em atenção que a ligação só será reposta depois de ser reutilizado do conjunto de ligação, por isso, é possível que um utilizador poderia abrir uma transacção e em seguida, liberte a ligação para o pool de conexões, mas não poderia ser reutilizado por vários segundos, durante os quais a transacção continuaria a ser aberta. Se a ligação não for reutilizada, a transacção será cancelada quando a ligação ultrapassou o tempo limite 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 utilize definir 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 requerem 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 seja capaz de lidar com o tempo de espera ou Cancelar cenários possam ocorrer, mas podem também beneficiar examinar o desempenho da consulta.
  3. Bloquear causado por um SPID cujo pedido de cliente correspondente não Fetch todas as linhas de resultados para conclusão

    Depois de enviar uma consulta para o servidor, todos os applicationsmust fetch imediatamente todas as linhas de resultados para conclusão. Se uma aplicação fetch todas as linhas de resultados, podem ser deixados bloqueios nas tabelas, bloquear outros utilizadores. Se estiver a utilizar uma aplicação que submete transparente SQLstatements para o servidor, a aplicação tem de obter todas as linhas de resultados. Se itdoes não (e se não pode ser configurado para o fazer), é possível que não é possível toresolve o problema de bloqueio. Para evitar o problema, pode restrictpoorly-funcionou aplicações para uma comunicação ou uma decisão-supportdatabase.

    Resolução:

    A aplicação deve ser redigida novamente para obter todas as linhas do resultado até à conclusão.
  4. Bloquear causado por um impasse do cliente/servidor distribuído

    Ao contrário de um impasse convencional, um deadlockis distribuídas não detectáveis utilizando o RDBMS bloquear o gestor. Isto deve-se a thatonly de facto, um dos recursos envolvido no impasse é um bloqueio de SQL Server. Lado de Theother do impasse está ao nível da aplicação cliente, através da qual SQLServer não tem qualquer controlo. Seguem-se dois exemplos de como isto pode acontecer e formas possíveis a aplicação podem evitá-la.

    1. Cliente/servidor distribuído impasse com um Thread único cliente
      Se o cliente tiver várias ligações abertas e um único thread de execução, poderão ocorrer o seguinte impasse distribuído. Brevity, o termo "dbproc" utilizado aqui refere-se para 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 acima indicado, um thread de aplicação de cliente único tem duas ligações abertas. Apresente uma operação de SQL num dbproc1 modo assíncrono. Isto significa que não espera na chamada para devolver antes de continuar. A aplicação, em seguida, submete outra operação de SQL no dbproc2 e aguarda os resultados para começar a processar os dados devolvidos. Quando a dados é iniciado regressem (qualquer que seja dbproc pela primeira vez responde – assumir que este é dbproc1), processa até à conclusão de todos os dados devolvidos numa guia que dbproc. Obtém os resultados de dbproc1 até SPID1 é bloqueado num bloqueio mantido por SPID2 (porque as duas consultas estiverem em execução modo assíncrono no servidor). Nesta altura, 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 como o único thread de execução para a aplicação está a ser utilizado por dbproc1. Isto resulta num impasse do SQL Server não é possível detectar ou resolver porque apenas um dos recursos envolvidos é um recurso de SQL Server.
    2. Cliente/servidor distribuído impasse com um Thread por ligação

      Mesmo se existe um thread separado para cada ligação no cliente, uma variação desta impasse distribuído pode continuar a ocorrer, a seguir demonstrado.

      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, mas dbproc2 e SPID2 estiverem a executar uma instrução SELECT com a intenção de efectuar linha-a-uma processamento e entrega de cada linha através de uma memória intermédia para dbproc1 para INSERT, UPDATE, ou eliminar a declaração na mesma tabela. Eventualmente, SPID1 (efectuando a INSERT, UPDATE ou DELETE) torna-se bloqueado um bloqueio mantido por SPID2 (efectuando a seleccionar). SPID2 escreve uma linha de resultado para o dbproc2 de cliente. Dbproc2, em seguida, tenta passar a linha numa memória intermédia para dbproc1, mas dbproc1 localiza está ocupado (está bloqueado à espera de SPID1 para concluir a inserção actual, que está bloqueada em SPID2). Neste momento, o dbproc2 está bloqueado na camada de aplicação pela dbproc1 cuja SPID (SPID1) está bloqueada ao nível da base de dados por SPID2. Novamente, isto resulta num impasse do SQL Server não é possível detectar ou resolver porque apenas um dos recursos envolvidos é um recurso de SQL Server.
    Ambos os exemplos A e B são questões fundamentais aos programadores de thatapplication devem ter em consideração. Se tem código aplicações para casos de handlethese correctamente.

    Resoluções:

    Duas soluções fiáveis estão a utilizar um querytimeout ou ligações dependentes.

    • Tempo limite de consulta
      Quando um limite de tempo de consulta foi fornecida, se ocorrer o impasse distribuído, será interrompida quando, em seguida, acontece o tempo limite. Consulte a biblioteca de base de dados ou a documentação de ODBC para obter mais informações sobre como utilizar um tempo limite de consulta.
    • Dependente de ligações
      Esta funcionalidade permite que um cliente ter várias ligações ligá-los para o espaço de uma única transacção, para que as ligações não bloqueie entre si. Para mais informações, consulte o tópico "Utilizando ligações vinculado" no SQL Server 7.0 Books Online.
  5. Bloquear causado por um SPID que está a ser um "ouro" ou anulação de alterações, estado

    Uma consulta de modificação de dados que é KILLed ou canceledoutside de uma transacção definida pelo utilizador, será anulada. Também pode occuras um efeito secundário o reinício do computador de cliente e o seu sessiondisconnecting de rede. Do mesmo modo, uma consulta seleccionada como vítima do impasse será rolledback. Uma consulta de modificação de dados frequentemente não pode ser revertida mais depressa do que thechanges inicialmente foram aplicadas. Por exemplo, se uma eliminação, inserção ou UPDATEstatement estavam em execução durante uma hora, poderá demorar, pelo menos, uma hora para a anulação. Este comportamento está previsto, porque as alterações efectuadas tem de ser parte de trás do completelyrolled ou integridade física e não transaccionais na base de dados seria becompromised. Uma vez que isso deve acontecer, SQL Server assinala o SPID num estado "ouro" ou de anulação (que significa não pode ser abatido ou seleccionado como um deadlockvictim). Isto pode frequentemente ser identificado pela observação a saída de sp_who, que poderá indicar o comando de anulação. Coluna Estado do sys.sysprocesses irá indicar um Estado de anulação, também será apresentada na saída de sp_who ou no Monitor de actividade do SQL Server Management Studio.
    Resolução:

    Tem de aguardar o SPID concluir a reverter a thechanges que foram efectuadas.

    Se o servidor for encerrado no meio correspondendo operação, a base de dados vai ser no modo de recuperação após o reinício, e itwill ser inacessível até que todas as transacções abertas são processadas. Startuprecovery demora essencialmente o mesmo período de tempo por transacção como timerecovery de execução e a base de dados está inacessível durante este período. Deste modo, o servidor forcingthe para correcção de um SPID num Estado de anulação será frequentemente becounterproductive.

    Para evitar esta situação, não efectuar largebatch INSERT, UPDATE, ou eliminar 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 causado por uma ligação de órfão

    Se as armadilhas de aplicação de cliente ou o clientworkstation é reiniciado, a sessão de rede para o servidor pode não beimmediately cancelada em determinadas condições. Perspectiva do servidor, sobre no cliente parece ainda estar presente e quaisquer bloqueios adquiridos poderão ainda beretained. Para mais informações, clique no número de artigo seguinte para visualizar o artigo na Microsoft Knowledge Base:
    137983 Como resolver problemas de ligações órfãos no SQL Server

    Resolução:

    Se a aplicação cliente terminou withoutappropriately limpar aos respectivos recursos, pode terminar o SPID pelo comando KILL usingthe. O comando KILL toa 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.

Envolvimento de aplicação em problemas de bloqueio

Poderá existir uma tendência para focar problemas de optimização e de plataforma do lado do servidor quando opostas de um problema de bloqueio. No entanto, isto normalmente não conduzir a uma resolução e capazes de absorver tempo e energia que melhor direccionada examinar a aplicação cliente e as consultas apresente. Independentemente de qual o nível de visibilidade a aplicação expõe sobre as chamadas de base de dados efectuadas, um problema de bloqueio, não obstante, frequentemente requer tanto a inspecção de instruções SQL exactas apresentado pela aplicação e o comportamento de exacta da aplicação relativas à anulação de consulta, gestão de ligações, a obtenção de todas as linhas de resultar e assim sucessivamente. Se a ferramenta de desenvolvimento não permitir 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 não podem ser resolvidos. Este potencial deve ser examinado estreitamente antes de seleccionar uma ferramenta de desenvolvimento de aplicações para o SQL Server, especialmente para ambientes de OLTP críticos.

É vital que precauções ser exercido 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 caminho de transacção devem ser avaliados para cada consulta. Cada consulta e a transacção devem ser o mais simples possível. Deve ser exercida disciplina de gestão de ligação em boas condições. Se isso não for efectuado, é possível que a aplicação poderá parecer ter um desempenho aceitável em número reduzido de utilizadores, mas o desempenho pode diminuir significativamente como o número de escalas de utilizadores para cima.

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

Aviso: Este artigo foi traduzido automaticamente

Propriedades

ID do Artigo: 224453 - Última Revisão: 03/15/2015 07:46:00 - Revisão: 6.0

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

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