Trabalhando com Isolation Level e Hints

Neste artigo vamos tratar de lock´s e como trabalhar com eles no SQL Server.

por Thiago Pastorello Gervazoni

Esta semana vamos falar de lock´s e como trabalhar com eles.

O Sql server trabalha com 2 tipos de isolation level (nível de isolamento), Otimista e Pessimista.

O otimista tem como premissa a alta concorrência entre as conexões, ou seja, todos podem ler, inserir, atualizar e apagar sem nenhuma restrição a outras conexões. É possível ter este tipo de isolação pelo comando:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED

Este tipo de isolamento compara-se ao hint Nolock.

Toda a sessão seguirá o tipo de isolação até que termine, ou que se mude.
Os níveis de isolamento permitem ou não inconsistências como:

• Dirty read
  É quando minha conexão está lendo informações que ainda não foram comitadas, ou seja, a informação que li pode não existir ou mesmo ter sido modificada.
• Nonrepeatable read
  Ocorre quando eu tenho uma transação e nesta transação eu leio a informação mais de uma vez, na primeira leitura veio uma informação e na segunda e demias vieram outras informações, ou seja, não garanto a consistência da informação dentro da mesma transação
• Phanton read
  Este tipo de inconsistência ocorre quando eu busco uma informação dentro de uma transação por exemplo por um determinado cep, o sistema me retorna 100 registros, quando dou update apenas 99 são atingidos, entre a leitura e o update 1 registro foi modificado e saiu da clausula WHERE ou foi apagado, poderia também ao invés de ser 99 registros atingidos serem 101, 102. Entre o select e o update usuários podem ter inserido registros que foram cobertos pela clausula WHERE. Por isto é chamado de phanton ou fantasma, aparecem e somem registros na mesma transação.

No tipo de isolamento Pessimista existe a concorrência de acessos, e dependendo do tipo de isolamento o grau de concorrência será muito baixo, o que pode provocar muitos deadlock´s e block´s.

READ COMMITED
Este é o isolation level default usado pelo SQL no Read Commited os registros são bloqueados quando se está fazendo a leitura do mesmo, por isto o tipo de inconsistência Dirty Read não existe, leio somente informações comitadas. Ao dar o update também o registro é bloqueado para a conexão até o commit ou rollback (implícito ou explicito).

REPEATABLE READ
Este tipo de isolamento segura os registros para a conexão na leitura até o fim da transação, nenhum usuário pode alterar ou apagar estes registros até que a conexão encontre um commit ou rolback (implícito ou explicito), Os Dirty´s Read´s e NonRepeatable Read´s não ocorrem, porém podem ocorrer os phantons. Só utilizar este tipo de isolamento quando estritamente necessário.

SERIAZABLE
Este é o tipo de isolamento mais restrito, este não permite a leitura (select), atualização (update), inserção (insert) ou remoção (delete) de nenhum registro que está sendo lido, até que encontre o commit ou rollback (implícito ou explicito). Os Dirty Read´s, NonRepeatable Reads e Phanton não ocorrem. Só utilizar este tipo de isolamento quando estritamente necessário.

Você pode ver seu isolation level pelo DBCC USEROPTIONS.

Segue tabela de tipos de isolamento e inconsistências

	Dirty Read	NonRepeatable Read	Phanton
READ UNCOMMITED	Sim	Sim	Sim
READ COMMITED	Não	Sim	Sim
REPEATABLE READ	Não	Não	Sim
SERIALIZABLE	Não	Não	Não

Por default o SQL faz todo este trabalho de locking por nós sem precisarmos nos preocupar, apenas em tarefas mais avançadas existe a necessidade de se mudar o ISOLATION level ou usar algum hint.

HINTS
Os hints nos permitem modificar o comportamento default do SQL sem precisarmos mudar o isolation Level, que mudaria todos os comandos na sessão corrente. São utilizados nos próprios comandos DML, seguem alguns. O sql pode bloquear se necessário um campo, um registro, um conjunto de registros, uma tabela, uma página de 8k, um extended que são 8 páginas de 8k ou o banco de dados inteiro, tudo isto depende de como você utiliza as queries.
Para verificar os locks e tipos de locks utilize a SP_LOCK.

Exemplo.

SELECT * FROM Northwind.dbo.Customers

Neste comando eu iria estar dando Block em toda a tabela, porque não especifiquei campos e nem WHERE até o sql me trazer todos os registros.

Exemplo.

UPDATE Northwind.dbo.Customers SET ContactName=NULL

Neste comando como o isolation level está default, o sql irá bloquear como exclusivo todos os meus registros até encontrar um rollback ou commit.

O Sql identifica o melhor modo de bloquear de acordo com seu nível de isolamento, utilizando os modos abaixo:

Modo de bloqueio

IS	Pretende compartilhar
S	Compartilhado
U	Dando update
IX	Pretende deixar exclusivo
SIX	Compartilhado com intenção de exclusivo
X	Exclusivo

Tipos de Hints

HOLDLOCK	Bloqueia os registros até o fim da operação
NOLOCK	Le todos os registros mesmo os com locking
ROWLOCK	Bloqueia um registro
PAGLOCK	Bloqueia toda a página
TABLOCK	Bloqueia toda a tabela
READPAST	Le apenas os registros comitados e pula os bloquedos
UPDLOCK	Atualiza registros bloqueados
SERIALIZABLE	O mesmo comportamento do isolation level descrito
REPEATABLEREAD	O mesmo comportamento do isolation level descrito
READCOMMITED	O mesmo comportamento do isolation level descrito
READCOMMITED	O mesmo comportamento do isolation level descrito

Exemplo especificando hints.
Iniciei uma transação pela conexão 51, estou alterando o nome de uma compania no Northwind, iniciei o Begin Tran, mas não tem rollback ou Commit.

Sem resposta, para outras conexões, porque o nível de isolamento default do SQL (Read Commited) bloqueia o regstro na leitura e na atualização.

Agora colocando um hint (nolock) é possível ler os registros, como Read uncommited.

Definindo-se o isolation level automaticamente o SQL assume o tipo e lock necessário para a transação, se você precisa de um comportamento diferente em apenas alguns comandos, pode utilizar os Hint´s, mas apenas quando necessário, é indicado também que se tome cuidado ao se dar comandos como insert e select porque podem influenciar nos blocks e deadlocks, como também acessar e atualizar as tabelas sempre na mesma ordem, como guidline procure dividir os comandos muitos extensos de select e update em pequenos blocos de comandos se possível.

Hints são muito utilizados em sites de grande concorrência como compra de ticket´s aéreos, bolsa de valores etc.

Até mais.