Isolation Level no SQL Server

Olá pessoal, tudo certo?

Depois de um tempo sem postar nada técnico estamos de volta. No post de hoje quero tentar explicar um pouco sobre níveis de isolamento do SQL Server.

O comportamento entre duas transações simultâneas depende da configuração do nível de isolamento configurado. No SQL Server temos 5 níveis de isolamento, READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT e SERIALIZABLE.

READ UNCOMMITTED

É o nível de isolamento mais brando pois permite que ocorra leitura de dados que foram modificados por outras transações, mas ainda não foram confirmados, ou seja, você consegue ler dados que ainda não foram comitados no banco. Esse nível de isolamento reduz a quantidade de locks no banco.

É bastante comum ver desenvolvedores utilizando o hint (NOLOCK), que na prática faz o mesmo papel do nível de isolamento READ UNCOMMITTED.

Exemplo:

Vamos abrir uma transação e executar um update.

BEGIN TRAN
UPDATE A
SET A.Title = 'DR.' 
FROM AdventureWorks.Person.Person A
WHERE a.BusinessEntityID = 16113

 Agora em outra sessão vamos executar um select simples na tabela AdventureWorks.Person.Person

SELECT * 
FROM AdventureWorks.Person.Person 

Na imagem a baixo podemos verificar que a consulta retornou dados até chegar na página que foi bloqueada pela transação 1.

A sessão 53, que executa o select está sendo bloqueada pela transação 1 (sessão 51) que está com a transação aberta.

Agora vamos ver o que ocorre quando o nível de isolamento é alterado para READ UNCOMMITTED.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * 
FROM AdventureWorks.Person.Person 
WHERE BusinessEntityID =16113

Como podemos verificar na imagem abaixo, o select retornou o registro, sem gerar nenhum lock, porém a sessão 54 fez uma leitura suja do dado, uma vez que a transação 51 ainda está aberta e não realizou o COMMIT.

Vamos simular que o usuário da sessão 51, percebeu que estava alterando um dado do cadastro errado e cancela aquela alteração (realiza um ROLLBACK).

O dado que o usuário da sessão 54 leu anteriormente agora não está de acordo com o que está no banco de dados. Embora o exemplo utilizado seja bastante simples e não geraria impacto, outros cenários poderiam trazer grandes impactos e prejuízos.

READ COMMITTED

É o modo padrão do SQL Server, ele garante que somente seja realizada leitura de dados confirmados (COMMIT), isso impede o risco de leituras sujas ao contrário do READ UNCOMMITTED. Em compensação aumenta o número de bloqueios (locks) nas transações.

No caso abaixo, vamos fazer um update na transação 01 e na transação 02 vamos fazer uma consulta dos dados.

--Transação 01
BEGIN TRAN 
UPDATE A
SET A.Title = 'DR.'
FROM AdventureWorks.Person.Person A
WHERE a.BusinessEntityID = 16113

--Transação 02
SELECT *
FROM AdventureWorks.Person.Person A 
WHERE a.BusinessEntityID = 16113

A transação 02 ficou bloqueada, pois o registro que está sendo consultado estava sendo atualizado pela transação 01.

O comportamento do READ COMMITTED pode ser alterado. Caso seja habilitado a opção READ_COMMITTED_SNAPSHOT como ON, o SQL Server vai começar a criar um versionamento dos dados, sendo possível fazer a leitura dos dados sem sofrer bloqueio de outra transação que esteja fazendo uma alteração. Quando você efetuar a leitura será retornado o dado que estava comitado no banco.

Para utilizar o READ_COMMITTED_SNAPSHOT, devemos alterar a propriedade no banco e não na sessão como os níveis anteriores.

ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON

A diferença entre o READ_COMMITTED_SNAPSHOT e o READ UNCOMMITTED é que o READ_COMMITTED_SNAPSHOT você não faz leitura dos dados não comitados. Nesse caso, a leitura dos dados que estão sendo atualizados é realizada na versão que está no Tempdb. Devemos ficar atentos pois quando utilizamos a propriedade READ_COMMITTED_SNAPSHOT o SQL Server vai começar a fazer o versionamento utilizando o Tempdb, o que pode ocasionar contenção.

BEGIN TRAN

UPDATE A
SET A.Title = 'DR.'
FROM AdventureWorks.Person.Person A

No exemplo abaixo, repare no tamanho utilizado pelo “Version Store”.

--Transação 02

SELECT *
FROM AdventureWorks.Person.Person A

REPEATABLE READ

Este nível de isolamento é mais restritivo que READ COMMITTED, pois ele faz o bloqueio de um registro que está sendo lido, não permitindo que outras sessões consigam ler, inserir ou alterar dados que estejam sendo bloqueados, este nível impede a leitura suja e registros fantasmas. Este nível deve ser utilizado somente quando necessário, pois pode gerar uma grande quantidade de locks no banco.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Agora na transação 01, vamos fazer uma consulta alterando o nível de isolamento da sessão para REPEATABLE READ.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Begin tran
SELECT *
FROM AdventureWorks.Person.Person A

Na transação 02 vamos efetuar um update no registro com BusinessEntityID = 16113, vamos verificar que a transação 02 vai ficar bloqueada.

--Transação 02

UPDATE A
SET A.Title = 'DR.'
FROM AdventureWorks.Person.Person A
WHERE a.BusinessEntityID = 16113

SNAPSHOT

 Este nível de isolamento também é uma alternativa para evitar locks nas tabelas e evita leitura de dados sujos, pois ele cria um versionamento dos dados alterados no tempdb.

Quando o banco e a sessão estão com o SNAPSHOT habilitado, quando ele vai fazer uma leitura de dados que por ventura esteja sendo alterado por outra sessão ele busca esse dado na versão que está armazenada no tempdb. Este nível de isolamento pode gerar um grande gargalo no tempdb.

Para melhor entendimento, vou utilizar a ilustração que o Diego Nogare fez em seu post (http://www.diegonogare.net/2013/01/transaction-isolation-level-voc-est-usando-certo/)

Para habilitar o nível de isolamento SNAPSHOT no banco basta executar o comando abaixo:

ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON

No exemplo abaixo é demonstrado o que acontece quando utilizamos nível de isolamento SNAPSHOT:

Podemos verificar que o Tempdb está 8 MB.

Agora vamos executar um update sem where na tabela Person.Person

--Transação 01
BEGIN TRAN

UPDATE A
SET A.Title = 'DR.'
FROM AdventureWorks.Person.Person A

O Tempdb aumentou o seu tamanho para 73 MB e criou uma versão da tabela, se executarmos um select na tabela Person.Person, vamos conseguir fazer a leitura dos dados, mesmo com a transação 01 aberta, porem a leitura dos dados que não foram modificados é realizada através da versão dos dados que estão no Tempdb, como no exemplo realizamos um update na tabela toda, então toda leitura foi realizada no Tempdb.

--Transação 02
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT * FROM Person.Person 

Após o COMMIT ou ROLLBACK da transação 01 os dados são liberados e voltam a ser lidos através da tabela.

Quando for utilizar o SNAPSHOT use com cuidado pois você pode se livrar de um problema e entrar em outro, como podemos ver a sua utilização pode gerar uma carga maior no Tempdb.

SERIALIZABLE

 É o nível de isolamento mais restritivo.

Uma transação não consegue ler dados, que estão sendo modificado no momento e não foram confirmados (leitura suja), também não é possível inserir dados em um intervalo de dados que tenha sido lido por outra transação (leitura fantasma).

Como podemos verificar abaixo, quando definimos o isolamento SERIALIZABLE e efetuamos uma consulta, ela gera um lock impedindo que a transação 02, realize um update.

--Transação 01

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
SELECT *
FROM AdventureWorks.Person.Person A 

--Transação 02

BEGIN TRAN

UPDATE A
SET A.Title = 'DR.'
FROM AdventureWorks.Person.Person A
WHERE a.BusinessEntityID = 16113

Para finalizar, não existe uma fórmula mágica para dizer qual é o melhor nível de isolamento. Isso varia de acordo com o seu ambiente. Você como DBA deve ter a habilidade de entender qual é o nível de isolamento mais adequado para aplicar no seu ambiente e ter em mente que “não existe almoço grátis… “, como o Edvaldo Castro diz em seu post (http://edvaldocastro.com/rcsi-2/).

O ideal é que você conheça cada um deles analisando os pontos positivos e negativos e também o seu ambiente, para assim, aplicar o nível de isolamento que mais se enquadra na sua situação.

Espero ter ajudado a entender os níveis de isolamento.

Referencias:

Pro SQL Server Internals – Segunda edição – Dmitri Korotkevitch
Microsoft SQL Server 2012 Internals – Kalen Delaney

https://msdn.microsoft.com/pt-br/library/ms173763.aspx
https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/
https://www.simple-talk.com/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/
https://www.mssqltips.com/sqlservertip/2977/demonstrations-of-transaction-isolation-levels-in-sql-server/
http://edvaldocastro.com/rcsi-2/
http://www.diegonogare.net/2013/01/transaction-isolation-level-voc-est-usando-certo/

Abraços,

Tiago Neves