Deadlock no banco de dados: como investigar locks no MySQL, PostgreSQL e SQL Server

Erro de deadlock ou consultas travadas esperando lock? Veja como descobrir quem bloqueia quem no MySQL, PostgreSQL e SQL Server e como evitar.

Para que serve

Quando duas transações esperam uma pela outra, o banco detecta o deadlock, escolhe uma vítima e cancela essa transação com erro. Já um lock comum (bloqueio) faz uma consulta esperar até a outra terminar, e a aplicação parece travada. Os dois têm causa parecida: transações longas, acesso às mesmas linhas em ordem diferente e falta de índices. Este artigo mostra como descobrir quem está bloqueando quem e como evitar a repetição no MySQL/MariaDB, no PostgreSQL e no SQL Server.

Pré-requisitos

• Usuário administrativo no banco (para ver as sessões de todos).
• A mensagem de erro exata da aplicação e o horário em que aconteceu.

Como identificar o tipo de problema

Sintoma / erro	Banco	O que é
ERROR 1213: Deadlock found when trying to get lock	MySQL/MariaDB	Deadlock; a transação foi desfeita
ERROR 1205: Lock wait timeout exceeded	MySQL/MariaDB	Esperou mais que innodb_lock_wait_timeout (padrão 50 s)
Estado Waiting for table metadata lock	MySQL/MariaDB	Um ALTER espera uma transação aberta e bloqueia todos atrás dele
ERROR: deadlock detected (SQLSTATE 40P01)	PostgreSQL	Deadlock
Erro 1205 "chosen as the deadlock victim"	SQL Server	Deadlock

MySQL e MariaDB: investigar deadlock e locks

Último deadlock

SHOW ENGINE INNODB STATUS\G

Procure a seção LATEST DETECTED DEADLOCK. Ela mostra as duas transações, a consulta de cada uma, o lock que cada uma tinha (HOLDS THE LOCK(S)), o que esperava (WAITING FOR THIS LOCK), o índice envolvido e qual foi desfeita. Como só o último fica guardado, ative o registro de todos no log de erros:

SET PERSIST innodb_print_all_deadlocks = ON;   -- MySQL 8
-- MariaDB: SET GLOBAL innodb_print_all_deadlocks = ON; e grave no .cnf

Quem bloqueia agora

SELECT waiting_pid, waiting_query, blocking_pid, blocking_query,
       wait_age, sql_kill_blocking_connection
FROM sys.innodb_lock_waits;

No MySQL 8 a visão usa performance_schema.data_locks e data_lock_waits. No MariaDB, sem o schema sys habilitado, consulte information_schema.INNODB_TRX e INNODB_LOCK_WAITS. Muitas vezes o bloqueador aparece com blocking_query vazio: é uma sessão que abriu transação e ficou parada (ex.: aplicação sem commit). Veja há quanto tempo em INNODB_TRX.trx_started.

Metadata lock

SELECT * FROM sys.schema_table_lock_waits\G

Mostra quem segura o lock de metadados da tabela. Finalize a sessão culpada com KILL <id>; somente depois de entender o que ela fazia (um KILL desfaz a transação inteira, o que em transações grandes também leva tempo).

PostgreSQL: investigar deadlock e locks

Quem bloqueia agora

SELECT a.pid, a.usename, a.state, now() - a.xact_start AS em_transacao,
       pg_blocking_pids(a.pid) AS bloqueado_por, left(a.query, 80) AS consulta
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0;

Depois, veja o que o PID bloqueador está fazendo: SELECT pid, state, xact_start, query FROM pg_stat_activity WHERE pid = <pid>;. Estado idle in transaction é o caso clássico de transação esquecida.

Para resolver: SELECT pg_cancel_backend(pid); cancela só a consulta; SELECT pg_terminate_backend(pid); encerra a sessão inteira.

Registrar esperas e deadlocks no log

ALTER SYSTEM SET log_lock_waits = on;      -- registra esperas maiores que deadlock_timeout (1 s)
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
SELECT pg_reload_conf();

Os deadlocks já são registrados por padrão no log (/var/log/postgresql/), com os PIDs, o tipo de lock e as consultas envolvidas.

Evite que DDL trave a produção

Um ALTER TABLE esperando lock bloqueia todas as consultas que chegam depois dele. Antes de rodar DDL em produção, defina um limite de espera:

SET lock_timeout = '5s';
ALTER TABLE pedidos ADD COLUMN observacao text;

SQL Server: investigar deadlock e bloqueios

Deadlocks já ocorridos

A sessão de eventos estendidos system_health, ativa por padrão, grava os relatórios de deadlock. Pelo SSMS: Management → Extended Events → Sessions → system_health → package0.event_file, filtre por xml_deadlock_report e abra a aba Deadlock para ver o gráfico. Por T-SQL:

SELECT CAST(event_data AS xml).value('(event/@timestamp)[1]','datetime2') AS quando,
       CAST(event_data AS xml).query('(event/data/value/deadlock)[1]') AS grafo
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'
ORDER BY quando DESC;

Quem bloqueia agora

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time,
       t.text AS consulta
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

Se muitos bloqueios forem entre leitura e escrita, considere ativar o Read Committed Snapshot no banco (as leituras passam a usar versões e deixam de esperar escritas). Teste antes, porque aumenta o uso do tempdb:

ALTER DATABASE MeuBanco SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Como evitar deadlocks

1. Mesma ordem de acesso: se todo código atualiza primeiro estoque e depois pedidos, duas transações nunca ficam em ciclo.
2. Transações curtas: nada de chamada a API externa, envio de e-mail ou espera por usuário com transação aberta.
3. Índices nos filtros de UPDATE/DELETE: no InnoDB, um UPDATE sem índice bloqueia todas as linhas que varre. Veja o artigo sobre índices.
4. Operações em lote menores: atualize 5 mil linhas por vez em vez de 2 milhões.
5. Repetição automática: deadlock ocasional é normal em sistemas concorridos. A aplicação deve repetir a transação ao receber 1213 (MySQL), 40P01 (PostgreSQL) ou 1205 (SQL Server).

Como saber se funcionou

• O log deixa de registrar deadlocks, ou registra com frequência muito menor.
• As consultas de "quem bloqueia agora" retornam vazias na maior parte do tempo.
• No MySQL, SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%'; mostra tempo de espera estável; no PostgreSQL, a coluna deadlocks de pg_stat_database para de crescer.

Perguntas frequentes

Deadlock corrompe dados?

Não. O banco desfaz a transação vítima por completo. O risco é a aplicação não tratar o erro e perder a operação.

Devo aumentar o innodb_lock_wait_timeout?

Raramente ajuda. Esperar mais só esconde a transação longa. Ache e corrija o bloqueador.

Qual a diferença entre lock e deadlock?

Lock é uma espera que termina quando a outra transação acaba. Deadlock é um ciclo de esperas que nunca terminaria sozinho, por isso o banco cancela uma das partes.

Leitura complementar

• MySQL 8.4: Deadlocks in InnoDB
• PostgreSQL Wiki: Lock Monitoring
• Microsoft Learn: Deadlocks guide

Precisa de ajuda?

Se os bloqueios coincidirem com lentidão de disco (I/O alto, latência elevada), abra um ticket com o horário dos eventos e a saída de iostat -x 5 3 para verificarmos o hardware.