Lição 3: GerenciandoTransações (Parte 2)


 

Para evitar acessos múltiplos ao mesmo recurso o banco de dados utiliza um recurso chamando LOCK. O controle de concorrência do Sql Server é classificado como otimista ou pessimista.

Pessimista – Permite acesso para leitura e atualização do mesmo dado em concorrência, bloqueios são utilizados para prevenir problemas devido aos múltiplos acessos ao dado.

Otimista – Não há bloqueio de recurso durante a leitura, no caso de atualização, o sistema verifica o dado foi alterado antes de lido.

 

Entendendo bloqueios (locks)

O SQL Server pode bloquear diferentes tipos de recursos ou granularidades, esses tipos podem ser RID (Row Identifier), página, índices, objetos e banco de dados.

Modos de bloqueios:

Shared (S) – Bloqueio utilizado em consultas, compatível com outros bloqueios compartilhados.

Update (U) – Bloqueio utilizado em recursos que podem ser atualizados, apenas uma transação por vez pode obter o Update lock no recurso.

Exclusive (X) – Bloqueio utilizado para modificação de dados, ex: INSERT, UPDATE, ou DELETE, incompatível com outros bloqueios

Intent (IS, IX, SIX) – Executa um bloqueio de objeto de alto nível (ex:table) antes de realizar um bloqueio exclusive (X) ou shared (S) no baixo nível (ex: página).

Schema (Sch-M, Sch-S) – Bloqueio utilizado quando uma operação dependente do esquema de uma tabela está em execução. Há dois bloqueios, o schema modification (Sch-M) e o schema stability (Sch-S), os dois são incompatíveis entre si.

Bulk Update (BU) – Bloqueio utilizado ao copiar massa de dados dentro de uma tabela e quando TABLOCK é especificado.

Key-Range – Bloqueio utilizado para evitar inserções/exclusões fantasmas ou exclusões em um conjunto de registros acessados ​​por uma transação. Esse bloqueio é utilizando transações que operam no nível de isolamento serializável.

 

Entendendo cenários de Deadlock e Blocking

As transações podem bloquear uma a outra, isso pode ocorrer de acordo com o nível de isolamento utilizado.

Uma situação de Deadlock ocorre quando uma transação “A” aguarda a liberação de um recurso bloqueado pela transação “B” que por sua vez aguarda uma liberação de um recurso bloqueado pela transação “A”. Para resolver esse impasse o Sql Server baseado no custo estimado de Rollback “derruba” uma das transações, essa transação é chamada de vítima.

Melhores práticas para evitar Deadlock:

– Faça transações curtas

– Obtenha e verifique os dados de entrada antes de iniciar uma transação.

– Mantenha as transações em um único batch

– Acesse a quantidade mínima possível de dados em uma transação.

 

Entendendo relatórios sobre o estado do bloqueio

Há diversas opções para visualizar o status de um bloqueio em um servidor de Sql Server. São eles Sql Profiler, System Monitor (perfom), Dmv’s e Activity Monitor.

 

Sql Server Profiler

Captura informações de locks e blocks. O Profiler disponibiliza um template já apropriado para detecção de bloqueios.

clip_image002

 

Activity Monitor

Utilize o Activity Monitor para visualizar dados de bloqueios, usuários, conexões e etc.

clip_image004

 

DMV’s

As DMV’s provém informações detalhadas sobre todos os bloqueios ativos.

Segue alguns exemplos.

 

SELECT *

FROM sys.dm_tran_active_transactions

 

clip_image006

 

SELECT Resource_type,Request_mode, Request_status,request_owner_type

FROM sys.dm_tran_locks

 

clip_image008

 

Tipos de recursos

 

RID (Row Identifier) – Linha física em um Heap.

KEY – Linha em um índice.

PAGE – Página em um arquivo de dados.

EXTENT – Uma extensão de arquivo de dados. Uma extensão é um grupo de oito páginas contíguas.

HoBT – Bloqueio em um Heap de uma página de dados ou na estrutura B-tree de um índice.

TABLE – Uma tabela.

FILE – Um arquivo de banco de dados.

APPLICATION – Um recurso de aplicativo especificado.

METADATA – Informações de metadados.

ALLOCATION_UNIT – Uma unidade de alocação.

DATABASE – Um banco de dados

 

Utilizando Sql Server Extended Events

Esse recurso está incluso no Sql Server 2008, assim como FindBlocker and lock_count que podem ser utilizados em conjunto com o Windows Event Logs, Sql Profiler ou System Monitor.

 

Usando DBCC LOG

Comando não documentado o qual retorna informações sobre o transaction log.

Sintaxe:

DBCC LOG (<databasename>,<output identifier>)

 

Níveis de output identifier

0: Retorno apenas o mínimo de informações para cada operação – incluindo operation, context, transactID, e log block generation.

1: Retorna toda as informações do nível acima, incluindo flags e record length information

2: Retorna toda as informações do nível acima, incluindo Object name, index name, page ID e slot ID.

3: Retorna todas as informações sobre a operação.

4: Retorna todas as informações sobre a operação, incluindo o hex dump da linha atual de log de transações.

 

DBCC LOG (’70_433′,1)

clip_image010

 

Definindo Níveis de Isolamento de Transações

Os seguintes níveis podem ser definidos através do comando SET TRANSACTIONS ISOLATION LEVEL:

 

READ UNCOMMITTED Nível mais baixo de isolamento, permite leitura de transações não confirmadas (leitura suja)

READ COMMITTED – Nível de baixo de isolamento, porém só permite leitura de transações confirmadas.

REPEATABLE READ- Não permite leitura de transações não confirmadas e realiza um bloqueio compartilhado em um recurso de dados.

SNAPSHOT – Requer ALLOW_SNAPSHOT_ISOLATION setada com ON Não permite leitura de transações não confirmadas, quando a transação é iniciada o banco de dados obtém um snapshot dos dados confirmados.

SERIALIZABLE – Esse é o mais restritivo dos níveis de isolamento, pois ele bloqueia intervalos de chaves inteiros até que a transação seja concluída.

 

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com

Anúncios

One Response to Lição 3: GerenciandoTransações (Parte 2)

  1. Olá,
    Uma dúvida – tenho uma aplicação que está em modo PESSIMISTA… há como mudar todas as tabelas do sql para OTIMISTA? sem haver a necessidade de incluir o comando NLOCK em todas as queries?
    Obrigado.

    Leonardo

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: