Você conhece o comando LOCK_TIMEOUT?

Fala Pessoal,

No post de hoje vou falar sobre um comando bem simples chamado “LOCK_TIMEOUT” e citar alguns cenários onde ele pode ser útil.

Confira também esse outro post com uma procedure para identificar locks no banco de dados e sua causa raiz:

https://luizlima.net/processo-bloqueado-use-a-procedure-stplock_raiz/

YouTube – Vídeo:

Segue abaixo um vídeo que gravei no YouTube mostrando na prática a execução dos scripts desse post:

https://www.youtube.com/watch?v=cJMZZPlhN7k

LOCK_TIMEOUT:

Especifica o número de milissegundos que uma sessão espera no máximo para um bloqueio ser liberado. Quando esse valor for atingido, a sessão bloqueada será cancelada automaticamente e uma mensagem de erro será retornada.

O SQL Server utiliza por padrão o valor -1 que indica nenhum tempo limite (ou seja, irá esperar indefinidamente até que o lock termine). Já o valor 0 indica que não vai esperar o lock, ou seja, quando acontecer irá falhar naquele exato momento.

Essa configuração irá alterar apenas a sessão atual, ou seja, não irá impactar as demais sessões do banco de dados.

SINTAXE:

SET LOCK_TIMEOUT timeout_period

1	SET LOCK_TIMEOUT timeout_period

O comando abaixo retorna a configuração do tempo limite de bloqueio em milissegundos da sessão atual.

SELECT @@LOCK_TIMEOUT

1	SELECT @@LOCK_TIMEOUT

Exemplo:

Vamos ver como funciona na prática. Primeiro vamos criar uma tabela e inserir um registro qualquer.

USE Traces

CREATE TABLE TESTE_LOCK (
	ID INT
)

INSERT INTO TESTE_LOCK VALUES (1)

USE Traces

CREATE TABLE TESTE_LOCK (

ID INT

)

INSERT INTO TESTE_LOCK VALUES (1)

Agora vamos gerar um LOCK e utilizar o LOCK_TIMEOUT com o parâmetro 60000 milissegundos (1 minuto).

OBS: Lembre de finalizar as sessões após a realização dos testes para não deixar nenhuma transação aberta.

Execute o comando abaixo na Sessão 1:

-- SESSAO 1
BEGIN TRAN
	UPDATE TESTE_LOCK
	SET ID = 1
-- COMMIT

-- SESSAO 1

BEGIN TRAN

UPDATE TESTE_LOCK

SET ID = 1

-- COMMIT

Execute o comando abaixo na Sessão 2:

-- SESSAO 2
SET LOCK_TIMEOUT 60000

BEGIN TRAN
	UPDATE TESTE_LOCK
	SET ID = 1

-- ROLLBACK

-- SESSAO 2

SET LOCK_TIMEOUT 60000

BEGIN TRAN

UPDATE TESTE_LOCK

SET ID = 1

-- ROLLBACK

Quando o tempo do Lock da sessão bloqueada atingir 1 minuto, ela irá falhar e exibir o seguinte erro:

Msg 1222, Level 16, State 45, Line 5

Lock request time out period exceeded.

The statement has been terminated.

Repare no tempo de execução da query na imagem abaixo:

“OK Luiz, já entendi como o LOCK_TIMEOUT funciona. Mas onde posso utilizá-lo na prática?”

Imagine os seguintes cenários que executam uma rotina durante a madrugada:

Uma rotina que processa muitos registros, que utiliza um LOOP para processar um por um.
Uma rotina de REBUILD de Índices, que utiliza um LOOP para executar em cada índice.
Uma rotina de Atualização de Estatísticas, que utiliza um LOOP para executar em cada estatística.

Agora, imagine a seguinte situação:

1) Uma das rotinas acima começa a processar;

2) Em algum momento ela fica bloqueada por alguma outra sessão que irá executar por várias horas seguidas para depois liberar o lock.

3) Quando o lock é liberado, a rotina volta a executar. Porém, como ela ficou várias horas bloqueada, ela vai voltar a executar no horário de produção, sendo que deveria ter executado somente durante a madrugada.

“Mas Luiz, qual o problema da rotina continuar executando quando o Lock for liberado?“

O problema é que essa rotina é mais pesada e pode gerar impactos na performance do ambiente causando lentidão, gerando outros locks, etc.

“Hum, entendi. Mas o que podemos fazer para evitar esse tipo de situação?”

Como vimos no exemplo acima, basta utilizar o comando “LOCK_TIMEOUT”. Nesse caso, quando o tempo máximo do Lock for atingido, a sessão irá falhar. Você pode definir o tempo que achar melhor. Por exemplo: 5 ou 10 minutos.

“OK Luiz, utilizei o LOCK_TIMEOUT e a minha rotina falhou. Agora como consigo identificar a query que gerou o Lock?”

Sugiro que crie um log de histórico da procedure “sp_whoisactive”. Dessa forma, você terá uma “foto” do banco de dados a cada minuto.

Segue abaixo um exemplo do resultado desse log para um lock que aconteceu no banco de dados. Dessa forma, conseguimos várias informações para identificar a sessão que causou o Lock. Com isso, podemos investigar e tentar evitar que aconteça novamente.

Referência: https://www.fabriciolima.net/blog/2016/03/17/queries-do-dia-a-dia-criando-um-log-de-historico-da-sp_whoisactive/

Agora imagine um outro cenário onde você tem uma rotina que não tem LOOP, mas que também é pesada e demorada. Suponha que em algum momento ela fique bloqueada por algumas horas. Vai acontecer a mesma situação dos exemplos anteriores, podendo voltar a executar somente no horário de produção e gerar impactos no ambiente.

Mais uma vez o LOCK_TIMEOUT poderia ser útil. Claro que cada caso é um caso e nem todas as rotinas pesadas podem simplesmente falhar por causa do Lock. Contudo, se for uma rotina que faz alguma tarefa de forma cumulativa, não vejo problemas em utilizar, pois no próximo dia irá executar para o que ficou pendente.

Vou terminando por aqui, fica aí a dica para revisar as suas rotinas mais pesadas e evitar transtornos durante o horário de produção.

Referências:

Segue abaixo alguns links como referência:

https://docs.microsoft.com/pt-br/sql/t-sql/statements/set-lock-timeout-transact-sql

https://docs.microsoft.com/pt-br/sql/t-sql/functions/lock-timeout-transact-sql

https://blog.sqlauthority.com/2013/01/28/sql-server-basic-explanation-of-set-lock_timeout-how-to-not-wait-on-locked-query/

Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

YouTube – Vídeo:

LOCK_TIMEOUT:

SINTAXE:

Exemplo:

Referências:

Compartilhe isso:

Deixe uma respostaCancelar resposta