Arquivo mensal: junho 2009

Conhecendo deadlocks – Parte III


Quando uma instância do Microsoft SQL Server escolhe uma transação como uma vítima de deadlock, ele encerra o lote atual, reverte a transação e retorna a mensagem de erro 1205 ao aplicativo.
 
Como qualquer aplicativo que envia consultas Transact-SQL pode ser escolhido como vítima de deadlock, os aplicativos deverão ter um manipulador de erros que possa interceptar a mensagem de erro 1205. Se um aplicativo não interceptar o erro, o aplicativo poderá prosseguir sem saber que sua transação foi revertida e que erros podem ocorrer.

A implementação de um manipulador de erros que intercepte a mensagem de erro 1205 permite que um aplicativo manipule a situação de deadlock e tome uma ação paliativa (por exemplo, enviar automaticamente de novo a consulta que estava envolvida no deadlock). Ao enviar a consulta novamente de forma automática, o usuário não precisa saber que ocorreu um deadlock.

O aplicativo deveria pausar brevemente antes de enviar novamente a consulta. Isso dá a outra transação envolvida no deadlock uma chance para completar e liberar seus bloqueios que formaram parte do ciclo de deadlock. Diminuindo a probabilidade do deadlock ocorrer novamente quando a consulta reenviada solicitar seus bloqueios.

Minimizando deadlocks

Embora deadlocks não possam ser completamente evitados, seguir certas convenções de codificação pode minimizar a chance de gerar um deadlock. Minimizar deadlocks pode aumentar a taxa de transferência da transação e pode reduzir a sobrecarga do sistema pois poucas transações são:

  • Revertidas, desfazendo todo o trabalho executado pela transação;
  • Reenviadas por aplicativos pois elas foram revertidas quando bloqueadas.

Para ajudar a minimizar deadlocks:

  • Acesse objetos na mesma ordem;
  • Evite a interação de usuário durante as transações;
  • Mantenha as transações curtas e em um lote
  • Use um nível de isolamento inferior;
  • Use um nível de isolamento com base em controle de versão de linha:
    • Configure a opção de banco de dados READ_COMMITTED_SNAPSHOT em ON, para habilitar que as transações confirmadas por leitura utilizem controle de versão de linha;
    • Use transação de isolamento;
    • Use associações de saída.

Continuaremos…

Anúncios

Conhecendo Deadlock – Parte II


Detectando e encerrando deadlocks

Um deadlock acontece quando duas ou mais tarefas bloqueiam uma à outra permanentemente, sendo que cada uma tem o bloqueio de um recurso que a outra tarefa está tentando bloquear. O seguinte gráfico apresenta uma exibição de alto nível de um estado de deadlock em que:

  • A tarefa T1 tem um bloqueio no recurso R1 (indicado pela seta de R1 para T1) e solicitou um bloqueio no recurso R2 (indicado pela seta de T1 para R2).

  • A tarefa T2 tem um bloqueio no recurso R2 (indicado pela seta de R2 a T2) e solicitou um bloqueio no recurso R1 (indicado pela seta de T2 a R1).

  • Como nenhuma tarefa pode continuar até que um recurso esteja disponível e nenhum recurso pode ser liberado até que uma tarefa continue, ocorre um estado de deadlock.

O Mecanismo de Banco de Dados do SQL Server detecta ciclos de deadlock automaticamente dentro do SQL Server. O Mecanismo de Banco de Dados escolhe uma das sessões como vítima de deadlock e a transação atual é encerrada com um erro para quebrar o deadlock.

Recursos que podem acarretar deadlock

Cada sessão de usuário pode ter uma ou mais tarefas sendo executadas em seu nome, sendo que cada tarefa pode adquirir ou aguardar para adquirir uma variedade de recursos. Os tipos de recursos a seguir podem causar bloqueio que pode resultar em um deadlock.

  • Bloqueios. A espera para adquirir bloqueios em recursos, como objetos, páginas, linhas, metadados e aplicativos pode causar deadlock. Por exemplo, a transação T1 tem um bloqueio compartilhado (S) na linha r1 e está esperando para obter um bloqueio exclusivo (X) em r2. A transação T2 tem um bloqueio compartilhado (S) na linha r1 e está esperando para obter um bloqueio exclusivo (X) em r1. Isso resulta em um ciclo de bloqueio no qual T1 e T2 esperam que uma libere os recursos bloqueados da outra.
  • Threads de trabalho Uma tarefa em fila aguardando um thread de trabalho pode causar um deadlock. Se a tarefa em fila possuir recursos que estão bloqueando todos os threads de trabalhado, haverá um deadlock. Por exemplo, a sessão S1 inicia uma transação e adquire um bloqueio compartilhado (S) na linha r1 e, depois, fica suspenso. As sessões ativas em execução em todos os threads de trabalhado disponíveis estão tentando adquirir bloqueios exclusivos (X) na linha r1. Como a sessão S1 não pode adquirir um thread de trabalho, ela não pode confirmar a transação e liberar o bloqueio na linha r1. Isso resulta em um deadlock.
  • Memória. Quando solicitações simultâneas estão esperando por concessões de memória que não podem ser satisfeitas com a memória disponível, pode ocorrer um deadlock. Por exemplo, duas consultas simultâneas, Q1 e Q2, são executadas como funções definidas pelo usuário que adquirem 10MB e 20MB de memória, respectivamente. Se cada consulta precisar de 30MB e a memória disponível total for de 20MB, Q1 e Q2 deverão esperar uma pela outra para liberar memória. Isso resulta em um deadlock.
  • Recursos relacionados à execução de consultas paralelas Threads de coordenação, produção ou consumo associados a uma porta de troca podem bloquear um ao outro causando um deadlock, normalmente ao incluir pelo menos um outro processo que não faz parte da consulta paralela. Além disso, quando uma consulta paralela começa a ser executada, o SQL Server determina o grau de paralelismo, ou o número de threads de trabalho, com base na carga de trabalho atual. Se a carga de trabalho do sistema for alterada inesperadamente, por exemplo, quando novas consultas forem executadas no servidor ou o sistema ficar sem threads de trabalho, poderá ocorrer um deadlock.
  • Recursos de vários conjuntos de resultados ativos (MARS). Esses recursos são usados para controlar a intercalação de várias solicitações ativas em MARS (consulte Ambiente de execução em lote e MARS).
    • Recurso do usuário. Quando um thread está esperando por um recurso que é potencialmente controlado por um aplicativo de usuário, o recurso é considerado como externo ou recurso de usuário e é tratado como um bloqueio.
    • Mutex de sessão. As tarefas que estão sendo executadas em uma sessão são intercaladas, ou seja, apenas uma tarefa pode ser executada na sessão em um determinado momento. Antes de a tarefa ser executada, deve ter acesso exclusivo ao mutex de sessão.
    • Mutex de transação. Todas as tarefas que estão sendo executadas em uma transação são intercaladas, ou seja, somente uma tarefa pode ser executada na transação em um determinado momento. Antes da tarefa ser executada, deve ter acesso exclusivo ao mutex de transação.

    Para que uma tarefa seja executada em MARS, ela deve adquirir o mutex da sessão. Se a tarefa estiver sendo executada em uma transação, deverá adquirir o mutex de transação. Isso garante que apenas uma tarefa esteja ativa em um determinado momento, sessão e transação. Quando os mutexes solicitados forem adquiridos, a tarefa poderá ser executada. Quando a tarefa termina, ou está no meio da solicitação, primeiro ela libera o mutex de transação e, depois, o mutex de sessão em ordem reversa de aquisição. Porém, podem ocorrer deadlocks com esses recursos. No exemplo de código a seguir, duas tarefas, solicitação de usuário U1 e solicitação de usuário U2, estão sendo executadas na mesma sessão.

    U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");
    U2:    Rs2=Command2.Execute("select colA from sometable");

    O procedimento armazenado que está sendo executado na solicitação U1 adquiriu o mutex de sessão. Se o procedimento armazenado levar muito tempo para ser executado, o Mecanismo de Banco de Dados presumirá que o procedimento armazenado está esperando uma entrada do usuário. A solicitação de usuário U2 está esperando pelo mutex de sessão, enquanto o usuário está esperando pelo conjunto de resultados de U2, e U1 está esperando por um recurso de usuário. Esse estado de deadlock é logicamente ilustrado como:

      

Continuaremos..

Conhecendo o Deadlock – Parte I


Um deadlock acontece quando duas ou mais tarefas bloqueiam uma à outra permanentemente, sendo que cada uma tem o bloqueio de um recurso, que a outra tarefa está tentando bloquear. Por exemplo:

  • A transação A adquire um bloqueio compartilhado da linha 1.
  • A transação B adquire um bloqueio compartilhado da linha 2.
  • A transação A agora solicita um bloqueio exclusivo na linha 2 e é bloqueado até que a transação B termine e libere o bloqueio compartilhado que tem na linha 2.
  • A transação B agora solicita um bloqueio exclusivo na linha 1 e é bloqueado até que a transação A termine e libere o bloqueio compartilhado que tem na linha 1.

A transação A não pode terminar até que a transação B termine, mas a transação B está bloqueada pela transação A. Essa condição é também chamada de dependência cíclica: a transação A tem uma dependência da transação B, e a transação B fecha o círculo tendo uma dependência da transação A.

Ambas as transações em um deadlock esperarão indefinidamente, a menos que o deadlock seja quebrado por um processo externo. O monitor de deadlock do Microsoft Mecanismo de Banco de Dados do SQL Server verifica periodicamente as tarefas que estão em um deadlock. Se o monitor detectar uma dependência cíclica, ele escolhe uma das tarefas como vítima e termina sua transação com um erro. Isso permite que a outra tarefa complete sua transação. O aplicativo com a transação que terminou com um erro pode repetir a transação, a qual normalmente é concluída depois que a outra transação em deadlock é encerrada.

Usando certas convenções de codificação em aplicativos reduz a chance de que os aplicativos causarão deadlocks. Para obter mais informações, consulte Minimizando deadlocks.

O deadlock é freqüentemente confundido com bloqueio normal. Quando uma transação solicita um bloqueio em um recurso bloqueado por outra transação, a transação solicitante espera até que o bloqueio seja liberado. Por padrão, as transações SQL Server não têm tempo limite, a menos que LOCK_TIMEOUT seja configurado. A transação solicitante é bloqueada, não em deadlock, por que ela não fez nada para bloquear a transação que deve o bloqueio. Finalmente, a transação proprietária vai terminar e liberar o bloqueio e a transação solicitante terá o bloqueio atribuído e processado.

Os deadlocks às vezes são chamados de abraço mortal.

Deadlock é uma condição que pode ocorrer em qualquer sistema com vários threads, não só em sistemas de gerenciamento de banco de dados relacional, e pode ocorrer para outros recursos, além de bloqueios de objetos em bancos de dados. Por exemplo, um thread em um sistema operacional de vários threads pode adquirir um ou mais recursos, como bloqueios de memória. Se o recurso sendo adquirido é atualmente propriedade de outro thread, o primeiro thread pode ter que esperar o thread proprietário liberar o recurso alvo. O thread em espera tem uma dependência do thread proprietário para aquele recurso em particular. Em uma instância do Mecanismo de Banco de Dados, sessões podem fazer um deadlock ao adquirir recursos que não são de banco de dados, como memória ou threads.

 

Na ilustração, a transação T1 tem uma dependência da transação T2 para o recurso de bloqueio de tabela Part. Da mesma forma, a transação T2 tem uma dependência da transação T1 para o recurso de bloqueio de tabela Supplier. Devido a essas dependências formarem um ciclo, há um deadlock entre as transações T1 e T2.

Os deadlocks também podem ocorrer quando uma tabela é particionada e a configuração LOCK_ESCALATION do ALTER TABLE é configurada para AUTO. Quando a LOCK_ESCALATION é configurada para AUTO, a simultaneidade aumenta ao permitir que o Mecanismo de Banco de Dados bloqueie partições de tabela no nível de HoBT em vez de no nível de TABLE. Entretanto, quando transações separadas mantêm bloqueios de partição em uma tabela e querem um bloqueio em algum lugar de outra partição de transações, isso causa um deadlock. Esse tipo de deadlock pode ser evitado configurando LOCK_ESCALATION para TABLE; embora essa configuração irá reduzir a simultaneidade forçando as atualizações extensas em uma partição a esperarem por um bloqueio de tabela.

Continuaremos…

Reorganizando e recriando índices no Microsoft SQL Server – Final


Exemplos de recriação e reorganização de índices:

 

A. Recriando um índice

O exemplo a seguir recria um índice único.

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

B. Recriando todos os índices em uma tabela e especificando opções

O exemplo a seguir especifica a palavra-chave ALL. Isso recria todos os índices associados à tabela. Três opções são especificadas.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

C. Reorganizando um índice com compactação LOB

O exemplo a seguir reorganiza um índice clusterizado único. Como o índice contém um tipo de dados LOB no nível folha, a instrução também compacta todas as páginas que contêm dados de objeto grande. Observe que não é preciso especificar a opção WITH (LOB_Compaction), uma vez que o valor padrão é ON.

USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

Reorganizando e recriando índices no Microsoft SQL Server – Parte IV


Desabilitando índices não clusterizados para conservar espaço em disco durante operações de recriação
 

Quando um índice não clusterizado é desabilitado, as linhas de dados do índice são excluídas, mas a definição do índice permanece nos metadados. O índice é habilitado durante a sua recriação. Quando o índice não clusterizado não é desabilitado, a operação de recriação exige espaço em disco temporário suficiente para armazenar o índice antigo e o novo. Porém, ao desabilitar e recriar um índice não-clusterizado em transações separadas, o espaço em disco disponível pela desabilitação do índice poderá ser reutilizado pela recriação subseqüente ou qualquer outra operação. Nenhum espaço adicional é exigido, exceto o espaço em disco temporário para classificação que, em geral, tem 20 por cento do tamanho do índice. Se o índice não clusterizado estiver na chave primária, qualquer índice ativo, que faça referência a restrições FOREIGN KEY, será desabilitado automaticamente. Essas restrições precisam ser habilitadas manualmente após a recriação do índice.

Recriando índices grandes

 

Índices com mais de 128 extensões são recriados em duas fases separadas: lógica e física. Na fase lógica, as unidades de alocação existentes usadas pelo índice são marcadas para desalocação, as linhas de dados são copiadas, ordenadas e, depois, movidas para novas unidades de alocação criadas para armazenar o índice recriado. Na fase física, as unidades de alocação previamente marcadas para desalocação são fisicamente canceladas em transações curtas que ocorrem em segundo plano e que não exigem muitos bloqueios.

Configurando opções de índice

 

As opções de índice não podem ser especificadas durante a reorganização de um índice. No entanto, as seguintes opções de índice podem ser definidas quando você recria um índice usando ALTER INDEX REBUILD ou CREATE INDEX WITH DROP_EXISTING:

PAD_INDEX

DROP_EXISTING (somente CREATE INDEX)

FILLFACTOR

ONLINE

SORT_IN_TEMPDB

ALLOW_ROW_LOCKS

IGNORE_DUP_KEY

ALLOW_PAGE_LOCKS

STATISTICS_NORECOMPUTE

MAXDOP

Observação:
Se uma operação de classificação não for necessária, ou se a classificação puder ser executada na memória, a opção SORT_IN_TEMPDB será ignorada.

Além disso, a cláusula SET da instrução ALTER INDEX permite definir as seguintes opções de índice sem recriar o índice:

ALLOW_PAGE_LOCKS

IGNORE_DUP_KEY

ALLOW_ROW_LOCKS

STATISTICS_NORECOMPUTE

 

Continuaremos…

Reorganizando e recriando índices no Microsoft SQL Server – Parte III


A recriação de um índice cancela o índice e cria outro. Quando isso é feito, a fragmentação é removida, o espaço em disco é recuperado pela compactação das páginas que usam a configuração do fator de preenchimento especificado ou existente e as linhas do índice são reordenadas em páginas contíguas (alocando novas páginas, se necessário). Isso pode melhorar o desempenho do disco reduzindo o número de leituras de página necessário à obtenção dos dados solicitados.

Os seguintes métodos podem ser usados para recriar índices clusterizados e não clusterizados:

  • ALTER INDEX com a cláusula REBUILD. Essa instrução substitui a instrução DBCC DBREINDEX.
  • CREATE INDEX com a cláusula DROP_EXISTING.

Todos os métodos realizam a mesma função; porém, há vantagens e desvantagens a serem consideradas, conforme mostrado na tabela a seguir.

Funcionalidade ALTER INDEX REBUILD CREATE INDEX WITH DROP_EXISTING

A definição de índice pode ser alterada pela adição ou remoção de colunas de chave, pela alteração da ordem das colunas ou pela alteração da ordem de classificação da coluna.*

Não

Sim**

As opções de índice podem ser definidas ou modificadas.

Sim

Sim

Mais de um índice pode ser recriado em uma única transação.

Sim

Não

A maioria dos tipos de índices pode ser recriada online sem que haja bloqueio de consultas ou atualizações em execução.

Sim

Sim

Um índice particionado pode ser reparticionado.

Não

Sim

Um índice pode ser movido para outro grupo de arquivos.

Não

Sim

É necessário espaço adicional temporário em disco.

Sim

Sim

A recriação de um índice clusterizado recria os índices não clusterizados associados.

Não

Exceto se a palavra-chave ALL for especificada.

Não

Exceto se houver alteração da definição do índice.

Os índices podem ser recriados pela imposição das restrições PRIMARY KEY e UNIQUE, sem cancelar e recriar as restrições.

Sim

Sim

Uma partição única de índice pode ser recriada.

Sim

Não

* Um índice não clusterizado pode ser convertido em um tipo de índice clusterizado pela especificação de CLUSTERED na definição do índice. Essa operação precisa ser executada com a opção ONLINE definida como OFF. A conversão de clusterizado em não clusterizado não tem suporte, independentemente da configuração de ONLINE.

** Se o índice for recriado com o mesmo nome, colunas e ordem de classificação, a operação de classificação poderá ser omitida. A operação de recriação verifica se as linhas estão ordenadas durante a criação do índice.

Você também pode recriar um índice cancelando-o, em primeiro lugar, com a instrução DROP INDEX e recriando-o com uma instrução CREATE INDEX separada. A realização dessas operações como instruções separadas resulta em várias desvantagens e não é recomendada.

Continuaremos…

Reorganizando e recriando índices no Microsoft SQL Server – Parte II


 

Para reorganizar um ou mais índices, use a instrução ALTER INDEX com a cláusula REORGANIZE. Essa instrução substitui a instrução DBCC INDEXDEFRAG. Para reorganizar uma única partição de um índice particionado, use a cláusula PARTITION de ALTER INDEX.

A reorganização de um índice desfragmenta o nível folha de índices clusterizados e não clusterizados em tabelas e exibições, reordenando fisicamente as páginas de nível folha para que correspondam à ordem lógica dos nós folha (da esquerda para a direita). Ter as páginas em ordem aprimora o desempenho do exame do índice. O índice é reorganizado dentro das páginas existentes alocadas; nenhuma página nova é alocada. Se o índice se estender por mais de um arquivo, os arquivos serão reorganizados um por vez. As páginas não migram entre arquivos.

A reorganização também compacta as páginas de índice. Todas as páginas vazias criadas por essa compactação são removidas, o que fornece espaço adicional disponível em disco. A compactação é baseada no valor do fator de preenchimento da exibição do catálogo sys.indexes.

O processo de reorganização utiliza recursos mínimos de sistema. Além disso, a reorganização é executada automaticamente online. O processo não mantém bloqueios de longo prazo; por isso, não bloqueará as atualizações nem as consultas em execução.

Reorganize um índice quando ele não estiver excessivamente fragmentado. Consulte a tabela anterior para obter as diretrizes de fragmentação. Contudo, se o índice estiver excessivamente fragmentado, você obterá resultados melhores recriando-o.

Compactação de tipo de dados de objeto grande

Além de reorganizar um ou mais índices, os tipos de dados LOB (Objetos Grandes) contidos no índice clusterizado ou na tabela subjacente são compactados por padrão quando o índice é reorganizado. Os tipos de dados image, text, ntext, varchar(max), nvarchar(max), varbinary(max) e xml são tipos de dados de objetos grandes. A compactação desses dados pode resultar em um melhor uso do espaço em disco:

  • Reorganizar um índice clusterizado especificado compactará todas as colunas LOB contidas no nível folha (linhas de dados) do índice clusterizado.
  • Reorganizar um índice não clusterizado compactará todas as colunas LOB que são colunas não-chave (incluídas) no índice.
  • Quando ALL é especificado, todos os índices associados à tabela especificada ou exibição são reorganizados e todas as colunas LOB associadas ao índice clusterizado, à tabela subjacente ou ao índice não clusterizado com colunas incluídas são compactadas.
  • A cláusula LOB_COMPACTION é ignorada quando não há colunas LOB.

Continuaremos…