Transaction Isolation Level

você está usando certo?

Fala galera, os Níveis de Isolamento (ou Transaction Isolation Level, do Inglês) não são apenas transações criadas para validar se um processamento gerou erro ou não, talvez você não esteja usando o Transaction Isolation Level corretamente. Vejo muita gente usando somente o BEGIN TRAN e COMMIT/ROLLBACK pensando que estão usando transações de forma correta, afinal, se der algum problema durante o processamento ele pode voltar com um ROLLBACK e tudo estará tudo como antes.

Bom, por exemplo, olhar somente por essa ótica pode estar errado! Acima de tudo pelo fato do Isolation Level ter causas/efeitos mais profundos do que somente garantir que a transação que está rodando no momento dê erro ou não.

Vale lembrar que o SQL Server é um SGBD que implementa o conceito de ACID (Atomicidade, Consistência, Isolamento e Durabilidade) e os níveis de isolamento atuam diretamente neste conceito. Outra coisa que é bom lembrar é que os níveis de isolamento impactam diretamente na quantidade de LOCKs e WAITs gerados no SQL.

Por padrão, o SQL Server tem o Isolation Level configurado para READ COMMITED. Isso pode ser confirmado rodando esta linha de código abaixo, e encontrando o resultado de ISOLATION LEVEL.

DBCC USEROPTIONS

Usando o Transaction Isolation Level corretamente

Para exemplificar estas explicações, e saber se está usando o Transaction Isolation Level corretamente, abra uma conexão com o SQL Server e execute o código abaixo. Ele irá criar um database e duas tabelas com alguns dados, onde serão explicados os tipos de isolamentos e de quebra um exemplo de dead lock.

CREATE DATABASE dbNogare
GO

USE dbNogare
GO

CREATE TABLE tbIsolationLevel
(Id INT IDENTITY,
 Col1 varchar(10),
 Col2 varchar(10),
 Col3 varchar(10)
)
GO

INSERT INTO tbIsolationLevel(Col1,Col2,Col3)
VALUES ('AAAAA','BBBBB','CCCCC')
GO 5

CREATE TABLE tbDeadlock (id INT)
GO

INSERT INTO tbDeadlock(ID)
VALUES (1), (2)

GO

Quando alteramos o nivel de isolamento, ele é aplicado à conexão que o alterou. Se você abrir uma nova conexão com o SQL Server e verificar qual é o nivel de isolamento desta transação, você verá que voltou para o padrão. Read Committed.

Transaction Isolation Level – Read Commited

O READ COMMITTED nos garante uma leitura somente do que já está “comitado” no banco, isso é, garante que o dado que está sendo lido foi realmente escrito no banco e não é uma leitura suja ou fantasma, causado por alguma outra transação. Pelo fato dele só ler as informações realmente escritas no banco, se uma outra transação estiver trabalhando com a tabela que você quer ler, o SQL irá esperar a transação liberar a tabela para então fazer a leitura. Isso gera LOCK (pela outra transação) e WAIT (pela sua).

Para entender na prática esse tipo de isolamento, abra duas conexões diferentes com a tabela tbNogare. Execute o primeiro bloco de código em uma delas, contanto que o segundo bloco seja executado segunda conexão. Após iniciar a execução do primeiro, vá até a segunda conexão e execute em simultâneo, deixando ambas rodando. Você irá ver o SQL Server terminar de executar a primeira transação para então iniciar a segunda.

/*********  RODAR NA CONEXÃO 1 *********/
BEGIN TRAN
    UPDATE tbIsolationLevel set Col1 = 'DDDDD' where id = 1
    WAITFOR DELAY '00:00:10'
ROLLBACK TRAN

/*********  RODAR NA CONEXÃO 2 *********/
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM tbIsolationLevel

Transaction Isolation Level – Read Uncommited

O READ UNCOMMITTED evita que o SQL Server dê um LOCK na tabela de leitura por causa de uma transação, isso faz com que também não gere um WAIT em outra transação. Porém, este processo permite uma leitura suja dos dados, entregando a informação “errada” em alguns cenários. Quando se utiliza este nível de isolamento, é possível ler os dados de uma tabela mesmo ela sendo utilizada dentro de uma transação longa que executa vários processos (INSERTS, UPDATES e DELETES). Estes passos intermediários que são executados antes do COMMIT no final, podem ser lidos e retornados. Quando isso acontece, chamamos de leitura suja (Dirty Read, do inglês).

Veja os códigos abaixo, e execute cada bloco simultaneamente em uma conexão diferente.

/*********  RODAR NA CONEXÃO 1 *********/
BEGIN TRAN
    UPDATE tbIsolationLevel set Col1 = 'DDDDD' where id = 1
    WAITFOR DELAY '00:00:10'
ROLLBACK TRAN

No código abaixo existem duas consultas. Ambas podem ler dados sujos, a primeira alterando o isolation level e a segundo consumindo dados com o hint WITH (NOLOCK). No final, o resultado de ambas serão os mesmos.

/*********  RODAR NA CONEXÃO 2 *********/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM tbIsolationLevel

-- Usando o hint NOLOCK
SELECT * FROM tbIsolationLevel with (nolock)

Transaction Isolation Level – Repeatable Read

Já o REPEATABLE READ garante que a transação que leia uma tabela mais do que uma vez dentro desta mesma transação, possa fazer isso sem ler dados diferentes dos registros já encontrados da primeira vez. Nenhuma alteração (UPDATE) dos dados já lidos anteriormente serão modificados na leitura (são alterados na tabela), mas novos registros (INSERT) são retornados nesta segunda leitura.

Veja nos blocos de código abaixo este processo.

/********* RODAR NA CONEXÃO 1 *********/
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN
  SELECT * FROM tbIsolationLevel
  WAITFOR DELAY '00:00:10'
  SELECT * FROM tbIsolationLevel 
ROLLBACK TRAN

Veja o SQL Server gerando um WAIT para executar o UPDATE, mas não o gera quando roda o INSERT, ele simplesmente insere os dados.

/********* RODAR NA CONEXÃO 2 *********/
UPDATE tbIsolationLevel
 set Col1 = 'DDDDD'
 where id = 1

INSERT INTO tbIsolationLevel(Col1,Col2,Col3)
VALUES ('DDDDD','EEEEE','FFFFF')

Transaction Isolation Level – Serializable

Uma variação mais completa do REPEATABLE READ é o SERIALIZABLE que bloqueia qualquer modificação de dados nas colunas que são consultadas, independente da modificação ser um UPDATE ou um INSERT. Este nível de isolamento causa um LOCK na transação original e um WAIT na segunda transação tanto para o UPDATE quanto para INSERT.

Veja este processo nos códigos abaixo:

/*********  RODAR NA CONEXÃO 1 *********/
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    SELECT * FROM tbIsolationLevel
    WAITFOR DELAY '00:00:10'
    SELECT * FROM tbIsolationLevel
ROLLBACK TRAN




/*********  RODAR NA CONEXÃO 2 *********/
UPDATE tbIsolationLevel set Col1 = 'DDDDD' where id = 1

INSERT INTO tbIsolationLevel(Col1,Col2,Col3)
VALUES ('DDDDD','EEEEE','FFFFF')

Transaction Isolation Level – Snapshot

Uma alternativa para evitar LOCK e WAIT nas tabelas, e também garantir que os dados modificados sejam escritos e não tenha leitura suja é o SNAPSHOT. Ele permite tal ação porque ele copia os dados alterados para a tempdb, possibilitando ler os dados originais durante a transação, mesmo que eles sejam alterados. Quando uma transação tem um snapshot, ela coloca uma flag em todos os registros, para garantir que não foram alterados. Caso isso ocorra, ela altera a flag.

Veja na imagem abaixo a explicação deste processo.

Com o snapshot habilitado no banco, e setado na transação, ele lê os dados originais da tabela mesmo sofrendo modificações por outra transação.

Se os dados lidos não sofrerem nenhuma alteração, nada é escrito na tempDB.

Porém, se algum dado for modificado, o SQL Server continua lendo a tabela. Será lida a versão original do dado que foi modificado já que ele foi copiado para a TempDB. Na hora da leitura, o SQL se encarrega de ler a TempDB e recuperar o dado original.

Esta funcionalidade, por padrão, vem desabilitada do banco de dados, e para utilizar, é necessário habilitar fazendo uma alteração no database. No código abaixo, é possível ver essa alteração.

USE MASTER
GO

ALTER DATABASE dbNogare
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

USE dbNogare
GO

Depois de habilitar o Snapshot Isolation Level no banco de dados, é hora de ver os blocos de códigos que possibilitam este nivel de isolamento.

/*********  RODAR NA CONEXÃO 1 *********/
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
    SELECT * FROM tbIsolationLevel
    WAITFOR DELAY '00:00:10'
    SELECT * FROM tbIsolationLevel
    UPDATE tbIsolationLevel set Col1 = 'XXXXX' where id = 2
    WAITFOR DELAY '00:00:10'
COMMIT TRAN




/*********  RODAR NA CONEXÃO 1 *********/
UPDATE tbIsolationLevel set Col1 = 'NNNNNN' where id = 1
SELECT * FROM tbIsolationLevel

Problemas com Dead Lock

Por fim, o DEAD LOCK também causa um pouco de confusão com sua execução. A causa de um DEAK LOCK é quando uma transação espera uma liberação para seguir em frente. Porém, a transação que está segurando o processo (causando LOCK) está esperando a primeira transação terminar de processar alguma coisa. A grosso modo, é A esperando o B terminar e o B esperando o A terminar. Eles ficariam um esperando o outro até alguém “desistir”. Para isso, o SQL Server escolhe uma vítima de acordo com o início do processo e o grau de severidade que aquele processo pode ter. Normalmente a transação que começou por ultimo é a vítima, se desligando do processo (o SQL faz isso automaticamente) e dá um rollback nas alterações. Isso faz com que a transação que ganhou o processo termine suas atividades.

Para ver isso através de código, execute os blocos abaixo.

/*********  RODAR NA CONEXÃO 1 *********/
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRAN
    UPDATE tbDeadlock 
    SET id = 12
    WHERE id = 2
    WAITFOR DELAY '00:00:10'
    UPDATE tbDeadlock 
    SET id = 11
    WHERE id = 1
ROLLBACK TRAN




/*********  RODAR NA CONEXÃO 2 *********/
BEGIN TRAN
    UPDATE tbDeadlock 
    SET id = 11
    WHERE id = 1
    WAITFOR DELAY '00:00:10'
    UPDATE tbDeadlock 
    SET id = 12
    WHERE id = 2
ROLLBACK TRAN

Quando ocorre o dead lock, a mensagem de erro é assim:

Msg 1205, Level 13, State 45, Line 2 Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Continue os estudos

Se você deseja fazer o exame de certificação para SQL Server, é importante saber sobre esses assuntos. Veja um exemplo de roteiro de estudos neste link.

Bom galera, não existe uma formula mágica que diz qual nível de isolamento é melhor. Como visto nos exemplos, cada um deles tem seus pontos fortes para garantir integridade dos dados e fracos que geram demora nos processos. Contudo, é ideal que você entenda o que eles fazem e que possa aplicar cada um com seu cenário.