Entendendo Transaction Isolation Level no SQL Server

Fala Galera, tudo na paz? Bora escovar mais um pouco de Bit no SQL Server…

O nível de isolamento (ou Isolation Level em inglês) no SQL Server controla o comportamento dos bloqueios e controle de versão das linhas e instruções.

O SQL Server é um SGBD que implementa as propriedades ACID, afim de garantir Atomicidade, Consistência, Isolamento e Durabilidade de cada transação efetuada e o nível de isolamento está intimamente ligado a isso.

Existem quatro níveis de isolamento, READ UNCOMMITED, READ COMMITED, REPEATABLE READ e SERIALIZABLE. Neste artigo vamos falar um pouco sobre cada e simular um ambiente para observar os resultados e comportamentos de cada nível.

READ UNCOMMITED é o nível mais baixo de isolamento do SQL Server, também conhecido como concorrência otimista.  Quando este nível de isolamento é configurado, as transações conseguem ler registros que estão sendo alterado por outra transação, mesmo que ainda não foi executado um COMMIT\ROLLBACK.  Por um lado essa configuração é boa, pois é como se não existisse bloqueios sobre os registros e com isso causando menos LOCKS, por outro lado, esse processo permite leitura suja dos dados, isso porque a transação de leitura lê dados ainda não confirmados.

O código abaixo simula um ambiente com READ UNCOMMITED configurado.

Abra uma sessão, e execute o trecho de código abaixo:

— Transação 1
SET Transaction Isolation Level Read Uncommitted

BEGIN TRANSACTION
SELECT SalesQuota FROM sales.SalesPerson
WHERE BusinessEntityID = 275
GO

Update Sales.SalesPerson Set SalesQuota = 100.00
Where BusinessEntityID = 275
GO

SELECT SalesQuota FROM sales.SalesPerson
WHERE BusinessEntityID = 275

— Rollback

Neste trecho de código (transação 1) o nível de isolamento é configurado para READ UNCOMMITED e em seguida é aberta uma transação para realizar o UPDATE no campo SalesQuota.

Repare que antes do UPDATE o valor era de R$ 300000,00 e em seguida foi alterado para R$ 100,00, porem a transação ainda não foi confirmada (COMMIT) ou abortada (ROLLBACK), com isso a alteração ainda está em memoria e não persistida em disco.

Para simular a leitura de dados “sujos”, abra uma nova sessão e execute o código abaixo:

— Transação 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT BusinessEntityID, TerritoryID, SalesQuota FROM sales.SalesPerson
WHERE BusinessEntityID = 275
GO

Repare que o select irá retornar o valor de R$ 100,00. Aqui mora um dos grandes problemas em habilitar READ UNCOMMITED. Imagine uma situação que por algum motivo fosse executado ROLLBACK no UPDATE (transação 1). Isso faria o valor do campo SalesQuota voltar para R$ 300000,00 e o select da transação 2 já retornou para o usuário o valor de R$ 100,00. Fazendo com que a transação retornasse informações erradas, pois na verdade o valor do campo é R$ 300000,00 e não R$ 100,00.

READ COMMITED é o modo padrão do SQL Server, e garante que as leituras sejam somente de informações já confirmadas (COMMIT), ou seja, os dados retornados já estão em disco, isso elimina o problema de leituras sujas mencionadas no exemplo a cima de READ UNCOMMITED.

Para exemplificar, abra uma nova conexão e execute o código a seguir:

— Transação 1

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRANSACTION
SELECT SalesQuota FROM sales.SalesPerson
WHERE BusinessEntityID = 275
GO

UPDATE Sales.SalesPerson SET SalesQuota = 100.00
WHERE BusinessEntityID = 275
GO

SELECT SalesQuota FROM sales.SalesPerson
WHERE BusinessEntityID = 275

— Rollback

Abra outra conexão(New Query) e execute o código abaixo:

— Transação 2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT BusinessEntityID, TerritoryID, SalesQuota FROM sales.SalesPerson
WHERE BusinessEntityID = 275
Repare que o select (transação 2) não retornou nada e está em execução. Isso ocorre porque o UPDATE (transação 1) gerou  bloqueio exclusivo sobre este registro, e com isso a transação 2 não consegue realizar o select, pois o UPDATE ainda não foi confirmado (COMMIT) ou abortado (ROLLBACK).

A view de sistema sys.dm_tran_locks exibe um registro para cada bloqueio atualmente em execução. Abra uma nova query e execute a view, observe que ira existir um registro onde o campo Request_Status será WAIT. Isso Significa que uma transação está esperando por outra para concluir suas operações.  READ COMMITED diferente de READ UNCOMMITED não permite leitura de dados sujos, por outro lado aumenta a concorrência nos registros por causas dos LOCKS.


REPEATABLE READ, este nível de isolamento garante que um registro que está sendo lido por uma transação não sofra alteração até que a leitura termine, impedindo que ocorra Dirty Reads e Non-Repeatble Read.
REPEATABLE READ mantém LOCKS de leituras até o final da transação.

Abra uma sessão e execute o código a seguir:

— Transação 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION
SELECT BusinessEntityID, Bonus FORM Sales.SalesPerson
WHERE BusinessEntityID = 275

WAITFOR DELAY00:00:10

ROLLBACK

Abra outra sessão e execute o trecho de código abaixo:

— Transação 2
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
UPDATE Sales.SalesPerson SET Bonus = 2500.00
WHERE BusinessEntityID = 275

— Rollback

O select executado pela primeira transação gerou um LOCK sobre esse registro, e quando executado o UPDATE (transação 2) ficou bloqueado gerando um WAIT para esse registro.  Observe que após o termino da transação 1 (estipulado pelo WATFOR DELAY) o comando ROLLBACK foi executado, com isso retirando o LOCK sobre o registro e consequentemente liberou o UPDATE. Antes de habilitar este nível de isolamento, verifique se é realmente necessário.

SERIALIZABLE, é o nível de isolamento mais restritivo, bloqueia todas as modificações nos dados, não se importando se parte de um UPDATE, INSERT ,DELETE. Quando uma transação com essas operações é executada, é gerado um LOCK sobre o recurso e se outra transação tentar efetuar alguma operação consequentemente será gerado um WAIT até que a primeira transação termine. SERIALIZABLE é mais restritivo que REPEATABLE READ, pois causa LOCKS não só em transações de UPDATE , mas também em transações de INSERT.

Para exemplificar, abra uma nova query e execute o código:

— Transação 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION
SELECT BusinessEntityID, TerritoryID, SalesQuota, Bonus FROM sales.SalesPerson
WHERE Bonus between 1 and 1000
And Bonus is not null

— Rollback

Abra uma nova query e execute a segunda transação

— Transação 2

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION
INSERT Sales.SalesPerson (BusinessEntityID, TerritoryID, SalesQuota, Bonus, CommissionPct, SalesYTD, SalesLastYear, rowguid, ModifiedDate)
VALUES (271, null, null, 999, 5000.00, 0.012, 3763178, NEWID(), GETDATE())

— Rollback

Faça um select na view sys.dm_tran_locks e observe os bloqueios adquiridos com essas operações.

Para finalizar, não existe uma formula pra dizer qual é o melhor nível de isolamento, isso muda de ambiente para ambiente e entre vários fatores. O ideal é que conheça cada um deles analisando os pontos positivos e negativos e também seu ambiente, para assim, aplicar o nível de isolamento que mais se enquadra no seu ambiente.

 

Obs: O Banco de Dados utilizado neste é artigo foi o AdventureWorks2008R2, está disponível para download no link: http://msftdbprodsamples.codeplex.com/releases/view/93587

Grande Abraço.

Luiz Henrique Garetti

Anúncios

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