Entendendo Transaction Isolation Level no SQL Server

Entendendo Transaction Isolation Level no SQL Server

12:00 29 October in Gestão de Infraestrutura
0 Comments

Fala Galera, tudo na paz? Vamos escovar mais um pouco de bits 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$ 300.000,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 existirá um registro em que 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 eNon-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 DELAY ‘00: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 término da transação 1 (estipulado pelo WATFOR DELAY) o comando ROLLBACK foi executado, com isso retirando o LOCK sobre o registro e consequentemente liberando o UPDATE. Antes de habilitar este nível de isolamento, verifique se é realmente necessário.

SERIALIZABLE é o nível de isolamento mais restritivo, bloqueando todas as modificações nos dados, não se importando se parte de um UPDATE, INSERT ou DELETE. Quando uma transação com essas operações é executada, é gerado um LOCK sobre o recurso. 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 fórmula para 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, e está disponível para download no link: http://msftdbprodsamples.codeplex.com/releases/view/93587

Grande Abraço!

 

**Este texto é uma produção independente e, portanto, de inteira responsabilidade do autor, não refletindo a opinião da Infobase.

Luiz Henrique Garetti Rosário

luizh.rosario@gmail.com

Graduado em Ciência da Computação pela Universidade Paulista e Pós-graduando em Business Intelligence pelo instituto Brasileiro de Tecnologia Avançada (IBTA). Especialista SQL Server atuando como DBA/Consultor em projetos de diversos portes, focando em arquiteturas de Alta Disponibilidade utilizando ambientes em Failover Cluster, Database Mirror, AlwaysOn e Administração de Banco de Dados em geral. Contribui para a comunidade com artigos técnicos publicados em grandes eventos na área de Banco de Dados com foco em Alta Disponibilidade e distribuição geográfica dos dados.