Controlando o comportamento, bloqueios e versionamento de linhas com o Set Transaction Isolation Level

Dica – Controlando o comportamento, bloqueios e versionamento de linhas com o Set Transaction Isolation Level

Anúncios

Pessoal, boa tarde.

A dica de hoje tem o objetivo de apresentar como podemos realizar e controlar do comportamento, bloqueio e versionamento de linhas no SQL Server através da instrução Transact-SQL SET TRANSACTION ISOLATION LEVEL.

A instrução SET TRANSACTION ISOLATION LEVEL, esta presente no SQL Server a muito tempo, desde de versões como SQL Server 7 ou 2000 esta instrução vem passando por atualizações e aperfeiçoamentos, com a finalidade de proporcionar cada vez mais uma forma mais simples, eficiente e eficaz nas atividades de Controle, Comportamento, e Bloqueios de dados e objetos, como também o Versionamento de Linhas existentes em nossas tabelas.

Pois bem, como o SET TRANSACTION ISOLATION LEVEL, consegui realizar este tipo de funcionalidade. Para isso existe um dos conceitos mais importantes no SQL Server, chamado de Transact Isolation Level ou Níveis de Isolamento de Transações, responsáveis em proporcionar ao SQL Server algumas formas de comportamento durante o acesso do usuário as dados, tabelas e bancos, bem como, estabelecer formas de garantir a validação e integridade dos dados.

Os níveis de isolamento de transaçãi são descritos em termos de quais efeitos colaterais de simultaneidade são permitidos, como leituras sujas ou leituras fantasma.

Níveis de isolamento da transação controlam:

  • Se são feitos bloqueios quando os dados são lidos, e que tipo de bloqueio é solicitado;
  • Por quanto tempo os bloqueios de leitura são mantidos;
  • Se uma linha de referência de operação de leitura foi modificada por outra transação:
    • Bloqueia até que o bloqueio exclusivo na linha seja liberado;
    • Recupera a versão confirmada da linha existente no momento em que a instrução ou transação foi iniciada; e
    • Lê a modificação de dados não confirmados.

A seguir apresento uma breve descrição de cada Nível de Isolamento de Transação existente no SQL Server 2008:

READ UNCOMMITTED –  Leitura Não Confirmada: Especifica que as instruções podem ler linhas que foram modificadas por outras transações, mas que ainda não foram confirmadas.

Transações em execução em nível READ UNCOMMITTED não emitem bloqueios compartilhados para impedir que outras transações modifiquem os dados lidos pela transação atual.

As transações em READ UNCOMMITTED também não são bloqueadas por bloqueios exclusivos que impediriam a transação atual de ler linhas que foram modificadas, mas não confirmadas, por outras transações.

Quando essa opção está definida, é possível ler modificações não confirmadas, chamadas de leituras sujas. Os valores nos dados podem ser alterados e linhas podem aparecer ou desaparecer do conjunto de dados antes do término da transação. Essa opção tem o mesmo efeito de definir NOLOCK em todas as tabelas em todas as instruções SELECT em uma transação. Esse é o menos restritivo dos níveis de isolamento.

No SQL Server, você também pode minimizar a contenção de bloqueios e, ao mesmo tempo, proteger as transações contra leituras sujas de modificações de dados não confirmadas, usando:

  • O nível de isolamento READ COMMITTED com a opção de banco de dados READ_COMMITTED_SNAPSHOT definida como ON.
  • O nível de isolamento SNAPSHOT.

READ COMMITTED – Leitura Confirmada: Especifica que as instruções não podem ler dados que foram modificados, mas que ainda não foram confirmados por outras transações. Isso impede leituras sujas.

Os dados podem ser alterados por outras transações entre instruções individuais dentro da transação atual, resultando em leituras não repetíveis ou dados fantasmas. Essa é a opção padrão do SQL Server.

O comportamento de READ COMMITTED depende da configuração da opção de banco de dados READ_COMMITTED_SNAPSHOT:

  • Se READ_COMMITTED_SNAPSHOT estiver definido como OFF (o padrão), o Mecanismo de Banco de Dados usará bloqueios compartilhados para impedir que outras transações modifiquem linhas enquanto a transação atual estiver executando uma operação de leitura.

  • Os bloqueios compartilhados também bloqueiam a instrução de ler linhas modificadas por outras transações até que a outra transação seja concluída. O tipo de bloqueio compartilhado determina quando ele será liberado. Os bloqueios de linha são liberados antes que a próxima linha seja processada. Os bloqueios de página são liberados quando a próxima página é lida e bloqueios de tabela são liberados quando a instrução é finalizada.

  • Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT for ON, você poderá usar a dica de tabela READCOMMITTEDLOCK para solicitar bloqueio compartilhado, em vez do controle de versão de linhas, para instruções individuais em transações em execução no nível de isolamento READ COMMITTED.

REPEATABLE READ – Leitura Repetível: Especifica que as instruções não podem ler dados que foram modificados, mas que ainda não foram confirmados por outras transações e que nenhuma outra transação pode modificar dados que foram lidos pela transação atual até que a transação atual seja concluída.

  • Os bloqueios compartilhados são colocados em todos os dados lidos por cada instrução na transação, sendo mantidos até que a transação seja concluída. Isso impede que outras transações modifiquem qualquer linha que tenha sido lida pela transação atual.
  • Outras transações podem inserir novas linhas que correspondam às condições de pesquisa das instruções emitidas pela transação atual.
  • Então, se a transação atual tentar a instrução novamente, ela recuperará as novas linhas, o que resultará em leituras fantasmas. Como os bloqueios compartilhados são mantidos até o término da transação, em vez de serem liberados ao final de cada instrução, a simultaneidade é menor que o nível de isolamento READ COMMITTED padrão. Use essa opção apenas quando necessário.

SNAPSHOT – Instantâneo: Especifica que os dados lidos por qualquer instrução em uma transação serão a versão transacionalmente consistente que existia no início da transação. A transação pode reconhecer apenas modificações de dados que estavam confirmadas antes do início da transação.

  • Modificações de dados efetuadas por outras transações após o início da transação atual não são visíveis para as instruções em execução na transação atual. O efeito será como se as instruções em uma transação obtivessem um instantâneo dos dados confirmados conforme existiam no início da transação.
  • Exceto quando um banco de dados está sendo recuperado, as transações SNAPSHOT não requerem bloqueios ao ler dados.
  • Transações SNAPSHOT que leem dados não bloqueiam outras transações de gravar dados. Transações que gravam dados não bloqueiam transações SNAPSHOT de ler dados.

Durante a fase de reversão de uma recuperação de banco de dados, as transações SNAPSHOT solicitarão um bloqueio se houver uma tentativa de ler dados que se encontram bloqueados por outra transação que está sendo revertida. A transação SNAPSHOT será bloqueada até que aquela transação seja revertida. O bloqueio será liberado tão logo seja concedido.

  • A opção de banco de dados ALLOW_SNAPSHOT_ISOLATION deve ser definida como ON para que uma transação que usa o nível de isolamento SNAPSHOT seja iniciada. Se uma transação que usa o nível de isolamento SNAPSHOT acessar dados em vários bancos de dados, ALLOW_SNAPSHOT_ISOLATION deve ser definida como ON em cada banco de dados.

Uma transação iniciada com outro nível de isolamento não pode ser definida com o nível de isolamento SNAPSHOT; isso causaria a anulação da transação. Se uma transação for iniciada no nível de isolamento SNAPSHOT, você poderá alterar seu nível de isolamento e retorná-la para o SNAPSHOT. Uma transação é iniciada na primeira vez em que ela acessa dados.

Uma transação em execução sob o nível de isolamento SNAPSHOT pode exibir as alterações feitas por essa transação. Por exemplo, se a transação executar um UPDATE em uma tabela e, em seguida, emitir uma instrução SELECT na mesma tabela, os dados modificados serão incluídos no conjunto de resultados.

SERIALIZABLE – Serializável: Especifica o seguinte: As instruções não podem ler dados que foram modificados, mas que ainda não foram confirmados por outras transações.

  • Nenhuma outra transação pode modificar dados lidos pela transação atual até que a transação atual seja concluída.
  • Outras transações não podem inserir linhas novas com valores chave que estejam no intervalo de chaves lido por qualquer instrução da transação atual até que esta seja concluída.

Bloqueios de intervalo são colocados no intervalo de valores chave que corresponde às condições de pesquisa de cada instrução executada em uma transação. Isso bloqueia que outras transações atualizem ou insiram qualquer linha que seja qualificada para qualquer uma das instruções executadas pela transação atual.

Isto significa que, se qualquer uma das instruções de uma transação for executada uma segunda vez, ela lerá o mesmo conjunto de linhas. Os bloqueios de intervalo são mantidos até que a transação seja concluída.

Esse é o mais restritivo dos níveis de isolamento, pois ele bloqueia intervalos de chaves inteiros até que a transação seja concluída. Como a simultaneidade é menor, use essa opção apenas quando necessário. Essa opção tem o mesmo efeito de definir HOLDLOCK em todas as tabelas em todas as instruções SELECT de uma transação.

Observação: A escolha de um Nível de Isolamento deve ser feita com muito cuidado e levando-se em consideração suas regras de comportamento e póssiveis impactos que esta alteração pode proporcionar.

Normalmente toda e qualquer transação especifica um nível de isolamento que define o grau em que uma transação deve ser isolada de modificações de recurso ou de dados feitas por outras transações.

Escolhendo um Nível de Isolamento de Transação

Escolher um nível de isolamento da transação não afeta os bloqueios obtidos para proteger as modificações de dados. Uma transação sempre obtém um bloqueio exclusivo em quaisquer dados que modifica e mantém tal bloqueio até que a transação seja concluída, sem considerar o conjunto de níveis de isolamento para a transação em questão.

Vale ressaltar que para operações de leitura, níveis de isolamento da transação definem principalmente o nível de proteção dos efeitos das modificações feitas por outras transações.

Outro detalhe importante, esta relacionado a mudança do  nível de isolamento para um nível inferior, o que pode proporcional para o SQL Server um aumento na capacidade e possibilidade de muitos usuários acessarem dados ao mesmo tempo, além de um possível aumento no número de efeitos de simultaneidade (como leituras sujas ou atualizações perdidas) que os usuários podem encontrar.

Inversamente, um nível de isolamento mais alto reduz os tipos de efeito de simultaneidade que os usuários podem encontrar, mas requer mais recursos do sistema e aumenta as chances de uma transação bloquear outra. Escolher o nível de isolamento apropriado depende de equilibrar os requisitos de integridade de dados do aplicativo em relação à sobrecarga de cada nível de isolamento.

Os chamdos níveis de isolamento mais altos, como o serializável, garante que uma transação recuperará exatamente os mesmos dados toda vez que repetir uma operação de leitura, mas faz isto executando um nível de bloqueio que provavelmente causará impacto em outros usuários em sistemas multiusuários.

O mais baixo nível de isolamento, leitura de dados não confirmados, pode recuperar dados que foram modificados mas não foram confirmados por outras transações. Todos os efeitos colaterais de simultaneidade podem acontecer em leitura não confirmada, mas não há nenhum bloqueio de leitura ou controle de versão, assim a sobrecarga é minimizada.

A Tabela 1 a seguir mostra os efeitos colaterais de simultaneidade habilitados por níveis de isolamento diferentes:

Nível de isolamento Leitura suja Leitura não repetível Fantasma
Leitura não confirmada Sim Sim Sim
Leitura confirmada Não Sim Sim
Leitura repetível Não Não Sim
Instantâneo Não Não Não
Serializável Não Não Não

Tabela 1 – Efeitos colaterais de simultaneidade aplicados a cada Nível de Isolamento de Transações.

Agora que já conhecemos um pouco dos Níveis de Isolamento de Transações, seus impactos, efeitos colaterais e possíves situações de uso, podemos utilizar a SET TRANSACTION ISOLATION LEVEL.

Utilizando a instrução SET TRANSACTION ISOLATION LEVEL:

Para proporcionar uma forma fácil de realizar o Controle, Comportamento, Blqueios e Versionamento de dados a Linguagem Transact-SQL possui em seu conjunto de comandos, a instrução SET TRANSACTION ISOLATION LEVEL.

A partir do momento em que esta instrução repassa para o SQL Server qual Nível de Isolamento de Transação deve ser utilizado, todas as mudanças em sua forma de comportamento são aplicadas de forma online, sem necessitar de qualquer tipo de processo de reinicialização dos serviços utilizados pelo servidor ou instância SQL Server.

Sintaxe:

 
SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]

Apenas uma única opção de nível de isolamento pode ser definida por vez, permanecendo definida para aquela conexão até que seja explicitamente alterada. Todas as operações de leitura executadas na transação operam sob as regras do nível de isolamento especificado, a menos que uma dica de tabela na cláusula FROM de uma instrução especifique comportamento de bloqueio ou controle de versão diferente para uma tabela.

Ao alterat uma transação de um nível de isolamento para outro, os recursos lidos após a alteração são protegidos de acordo com as regras do novo nível. Recursos lidos antes da alteração continuam sendo protegidos de acordo com as regras do nível anterior.

Por exemplo: Ao mudar uma transação de READ COMMITTED para SERIALIZABLE, os bloqueios compartilhados adquiridos após a alteração serão, nesse caso, mantidos até o término da transação.

Se você emitir SET TRANSACTION ISOLATION LEVEL em uma Stored Procedure ou Trigger, quando o controle retornar para o objeto retornar, o nível de isolamento será redefinido como o nível que estava em vigor quando o objeto foi invocado.

Por exemplo: Ao definir REPEATABLE READ em um lote e o lote chamar um procedimento armazenado que define o nível de isolamento como SERIALIZABLE, a configuração do nível de isolamento será revertida para REPEATABLE READ quando o procedimento armazenado retornar o controle para o lote.

Para ilustrar como podemos utilizar a SET TRANSACTION ISOLATION LEVEL, apresento a seguir alguns exemplos:

1 – Definindo o SET TRANSACTION ISOLATION LEVEL para uma Sessão:

USE BANCO1;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *  FROM PRODUTOS;
GO

SELECT * FROM FORNECEDORES;
GO

COMMIT TRANSACTION;
GO

Observação: Neste exemplo, cada instrução Transact-SQL processada, o SQL Server irá manter todos os bloqueios compartilhados até o término da transação.

2 – Definindo o Nível de Isolamento de Transações Serializable:

USE AdventureWorks2008R2;
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
SELECT BusinessEntityID FROM HumanResources.Employee;
GO

Observação: Para este exemplo o SQL Server vai realizar a sequência de instruções em série, respeitando uma definição interna da ordem correta de execução até que a última instrução seja executada.

3 – Para determinar o nível de isolamento da transação definido atualmente:

USE AdventureWorks2008R2;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

DBCC USEROPTIONS;
GO

Observação: Use a instrução DBCC USEROPTIONS, como mostrado no exemplo a seguir. O conjunto de resultados pode ser diferente do conjunto de resultados em seu sistema.

 

Bom pessoal chegamos ao final de mais uma dica, espero que vocês tenham gostado e este material possa ser útil no seu dia-a-dia.

 

Mais uma vez obrigado por sua visita, nos encontramos em breve com mais algumas dicas.

 

Até mais.

 

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes, bem como, Professor Titular na Fatec São Roque. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

6 comentários em “Controlando o comportamento, bloqueios e versionamento de linhas com o Set Transaction Isolation Level”

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