Sim, é um deadlock

Se você prefere ler o artigo em PDF, baixe aqui.


Revisando os posts da última semana eu encontrei um post interessante do nosso amigo Alexandre Lopes, que mostra um exemplo bem legal de deadlock. O post na íntegra está aqui: http://sqlserverday.com.br/alopes/?p=924 e aconselho lê-lo antes de continuar com
este post. Leu tudo? Então vamos a uma análise mais detalhada.

Monitorando com o profiler os eventos lock:acquired e lock:released, execute no banco de dados AdventureWorks a consulta “SELECT * FROM Sales.CurrencyRate” e veremos (Figura 01) que o lock manager optou por pegar um bloqueio de página, então temos um IS no objeto e, durante a leitura dos registros, o SQL Server vai pegando e liberando os bloqueios compartilhados na medida em que os registros das páginas são lidos.

(Figura 01)

Agora vamos ao deadlock, com base no script abaixo:

(Script 01)
USE AdventureWorks
go
select @@TRANCOUNT

-- Conexão 01
BEGIN TRAN
UPDATE Sales.CurrencyRate SET AverageRate = 2 WHERE CurrencyRateID =1
SELECT * FROM Sales.CurrencyRate

-- Script 02
BEGIN TRAN
UPDATE Sales.CurrencyRate SET AverageRate = 2 WHERE CurrencyRateID = 1

Quando iniciamos a transação da conexão 01 e executamos o primeiro update, veremos o SQL Server pegando um lock exclusivo no registro e IX na página e tabela (Figura 02).

(Figura 02)

Se a partir de outra conexão executarmos o mesmo comando de update, o que é de se esperar? Que o SQL Server requisite os mesmos recursos e a transação fique bloqueada pelo registro exclusivo colocado sobre a chave. Vejamos a saída do SP_LOCK para análise na figura 03.

(Figura 03)

Vemos claramente que a conexão de SPID 55 ficou bloqueada (WAIT) esperando que a conexão 52 libere o bloqueio exclusivo sobre o registro que está sendo atualizado. Outro ponto que eu quero chamar a atenção é para o bloqueio IX da página 1040, que foi concedido para a transação de SPID 55.

Se buscarmos no BOL o tópico “Lock Compatibility” veremos que IX são compatíveis com IX e até existe uma nota sobre isso: “An intent exclusive (IX) lock is compatible with an IX lock mode because IX means the intention is to update only some of the rows rather than all of them. Other transactions that attempt to read or update some of the rows are also permitted as long as they are not the same rows being updated by other transactions”.

Porém se analisarmos a tabela de compatibilidade, veremos que o bloqueio compartilhado (S) não é compatível com o IX, pois eu não poderia ler uma página em que algum registro estivesse sendo alterado, seria uma leitura suja não compatível com o nível de isolamento read committed. Acho que vocês já devem ter percebido onde eu quero chegar...

Se nesse momento nós voltarmos para a primeira transação que está com o bloqueio exclusivo no registro e executarmos o SELECT para buscar todos os registros, o que veremos? Sim senhor, um deadlock. E os recursos envolvidos são detalhados na figura 04.

(Figura 04)

Analisando a saída acima, vemos as sessões 52 e 55 se bloqueando, então estamos em uma situação de deadlock, onde o SQL Server deve escolher uma vítima. Os recursos envolvidos são o registro bloqueado pelo lock exclusivo e a página 1040, onde a transação do SPID 52 fica bloqueada com o status CNVT (Convert). Pela documentação do SQL Server: “CNVRT: The lock is being converted from another mode, but the conversion is blocked by another process holding a lock with a conflicting mode”.

Então o deadlock aconteceu porque estamos trabalhando em níveis de granularidade diferentes, um na página e outro no registro! Se o que eu acabei de afirmar for verdade, esse tipo de deadlock pode ser resolvido com uma hint no SELECT, onde indicamos que o SQL Server deve pegar bloqueios de registros, e não de página: “SELECT * FROM Sales.CurrencyRate (ROWLOCK)”. Faça o teste e verá que realmente dessa forma o deadlock não irá acontecer.

Conclusão

O comportamento do SQL Server está correto e é realmente um deadlock, então vou discordar do Alexandre em relação à qualidade do produto e que não existe um deadlock, mas sim tentar pensar como um program manager do produto. Qual é o maior ganho para o SQL Server? Permitir bloqueios de registro e utilizar o IX para indicar que existe algum recurso com menor granularidade sendo bloqueado (acelerando a análise de compatibilidade dos bloqueios) ou evitar esse comportamento impedindo bloqueios de registro ou análises custosas de compatibilidade de bloqueios em diferentes níveis (imagine bilhões de registros, um bloqueio exclusivo em um registro e outra transação pedindo um bloqueio compartilhado na tabela). Eu também ficaria com o row lock.

No fim eu gostei bastante de fazer essa pequena análise, pois é um excelente exemplo (e não muito comum) de um deadlock que o Alexandre trouxe para nós, possibilitando mostrar para vocês um pouco mais do SQL Server e seu funcionamento.

Abraços e até um próximo artigo.

[]s
Luciano Caixeta Moreira - {Luti} luciano.moreira@srnimbus.com.br www.twitter.com/luticm www.srnimbus.com.br