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 um e simular um ambiente para observar os resultados e comportamentos de cada nível.

O 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 um COMMIT\ROLLBACK ainda não tenha sido executado.  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$ 300 mil e em seguida foi alterado para R$ 100; porém, 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

Repare que o select irá retornar o valor de R$ 100. 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$ 300 mil e o select da transação 2 já retornou para o usuário o valor de R$ 100. Fazendo com que a transação retornasse informações erradas, pois na verdade o valor do campo é R$ 300 mil e não R$ 100.

O 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 acima 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 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 irá 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. O 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.

O 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. O 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 From Sales.SalesPerson
Where BusinessEntityID = 275
WAITFOR DELAY '00:00:10'
Rollback

Abra outra sessão e execute o techo 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 retirou o LOCK sobre o registro e consequentemente liberando o UPDATE. Antes de habilitar este nível de isolamento, verifique se é realmente necessário.

O 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. O 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 fórmula 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 neste link.

Grande abraço!