Bloqueios no MySQL Explicados: Verifique o Status dos Bloqueios, Libere-os com Segurança e Previna Deadlocks (5.6–8.0)

1. Introdução

MySQL é amplamente usado como sistema de gerenciamento de banco de dados, mas quando múltiplas consultas tentam acessar os mesmos dados, um mecanismo de bloqueio é acionado. Os bloqueios são essenciais para manter a consistência dos dados; porém, o gerenciamento inadequado pode levar a deadlocks e degradação de desempenho.

Neste artigo, explicaremos os conceitos fundamentais de bloqueio no MySQL e forneceremos orientações detalhadas sobre como verificar o status dos bloqueios, como liberar bloqueios e como prevenir deadlocks.

O que você aprenderá neste artigo

• Os tipos de bloqueios do MySQL e seu impacto
• Como verificar bloqueios por versão do MySQL
• Procedimentos seguros para liberar bloqueios
• Conselhos práticos para prevenir deadlocks

Vamos começar explicando os conceitos básicos de bloqueio no MySQL.

2. Conceitos Básicos de Bloqueio no MySQL

Em um banco de dados, um “bloqueio” é um mecanismo que restringe o acesso para manter a integridade dos dados quando múltiplas transações tentam modificar os dados simultaneamente. Se os bloqueios não forem gerenciados corretamente, a degradação de desempenho e deadlocks podem ocorrer.

2.1 Principais Tipos de Bloqueios

No MySQL, existem vários tipos de bloqueios dependendo do nível de proteção de dados necessário.

Bloqueio de Linha

• Bloqueia apenas linhas específicas, minimizando o impacto em outras transações.
• Suportado apenas pelo mecanismo de armazenamento InnoDB.
• É acionado ao usar SELECT ... FOR UPDATE ou SELECT ... LOCK IN SHARE MODE .

Bloqueio de Tabela

• Bloqueia a tabela inteira, impedindo que múltiplas consultas sejam executadas simultaneamente.
• Usado comumente com o mecanismo de armazenamento MyISAM.
• É acionado ao usar a instrução LOCK TABLES .

Bloqueio de Intenção

• Um bloqueio usado para coordenar bloqueios de linha e de tabela a fim de evitar conflitos.
• Usado apenas no InnoDB e gerenciado automaticamente.

Deadlock

• Um estado em que múltiplas transações aguardam pelos bloqueios umas das outras.
• Se as transações não forem projetadas corretamente, o processamento pode parar completamente.

2.2 Exemplos de Ocorrência de Bloqueio

Vamos analisar consultas SQL específicas para entender como os bloqueios ocorrem.

Exemplo de Bloqueio de Linha

Executando o SQL a seguir, uma linha específica será bloqueada.

BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 100;
-- Other sessions cannot update this row until this transaction is COMMIT or ROLLBACK

Se outra sessão tentar atualizar a mesma linha, ela entrará em estado de espera de bloqueio (conteúdo de bloqueio).

Exemplo de Bloqueio de Tabela

Para bloquear uma tabela inteira, use o comando a seguir.

LOCK TABLES products WRITE;
-- Other sessions cannot modify the products table until all read/write operations are complete

Até que esse bloqueio seja liberado, outros usuários não poderão modificar os dados na tabela products.

Exemplo de Deadlock

O seguinte mostra um cenário típico de deadlock.

-- Session 1
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
-- Waiting for Session 2...

-- Session 2
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
-- Waiting for Session 1...

-- Session 1 (executes another statement)
UPDATE customers SET last_order = NOW() WHERE customer_id = 10; -- Deadlock occurs here

Nessa situação, cada transação está aguardando que a outra libere seu bloqueio, resultando em um deadlock.

3. Como Verificar o Status de Bloqueios no MySQL (Por Versão)

Para determinar se bloqueios estão ocorrendo, você precisa executar comandos adequados à sua versão do MySQL.

3.1 Como Verificar Bloqueios no MySQL 5.6 e Anteriores

No MySQL 5.6 e anteriores, você pode verificar informações de bloqueio usando SHOW ENGINE INNODB STATUS\G;.

SHOW ENGINE INNODB STATUS\G;

Executar este comando exibe informações detalhadas sobre os bloqueios atualmente ativos.

3.2 Como Verificar Bloqueios no MySQL 5.7

No MySQL 5.7 e posteriores, o método mais simples é usar a tabela sys.innodb_lock_waits.

SELECT * FROM sys.innodb_lock_waits;

Ao consultar esta tabela, você pode identificar quais transações estão aguardando locks.

3.3 Como Verificar Locks no MySQL 8.0 e Posteriores

No MySQL 8.0 e posteriores, você pode obter informações mais detalhadas sobre locks usando performance_schema.data_locks.

SELECT * FROM performance_schema.data_locks;

Para identificar a sessão que está segurando o lock, use o seguinte SQL:

SELECT * FROM performance_schema.threads WHERE PROCESSLIST_ID = <process_id>;

Isso permite que você identifique precisamente o processo responsável pelo lock.

4. Como Liberar Locks do MySQL (Riscos Explicados)

Se um lock ocorrer no MySQL e não for tratado adequadamente, o processamento pode parar e o desempenho do banco de dados pode degradar.
Nesta seção, explicaremos como liberar locks e os riscos envolvidos.

4.1 Identificando a Sessão que Está Segurando o Lock

Antes de liberar um lock, você deve identificar qual sessão está segurando-o. Use o seguinte SQL para verificar sessões que estão aguardando locks:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE='Waiting for table metadata lock';

Esta consulta lista sessões que estão aguardando um lock.

No MySQL 8.0 e posteriores, você pode obter informações detalhadas sobre locks usando:

SELECT * FROM performance_schema.data_locks;

4.2 Liberando Locks Usando o Comando KILL

Uma vez que você identifique a sessão segurando o lock, você pode liberá-lo terminando forçosamente o processo.

1. Verifique o processo segurando o lock

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST;

2. Termine a sessão usando o comando KILL

KILL <process_id>;

Por exemplo, para terminar um processo com ID=12345, execute:

KILL 12345;

⚠️ Riscos do Comando KILL

• Transações terminadas forçosamente são revertidas
• Por exemplo, alterações feitas por uma instrução UPDATE interrompida podem ser descartadas.
• Pode causar erros na aplicação
• Se você precisar frequentemente usar KILL, você deve revisar o design da sua aplicação.

4.3 Liberando Locks com ROLLBACK (Método Mais Seguro)

Antes de usar o comando KILL, se possível, tente encerrar manualmente a transação causando o lock.

1. Primeiro, verifique as transações atuais

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. Se você encontrar a transação problemática, execute ROLLBACK

ROLLBACK;

Este método permite que você libere o lock mantendo a consistência dos dados.

4.4 Automatizando o Tratamento de Locks com SET innodb_lock_wait_timeout

Em vez de liberar locks manualmente, você pode configurar um tempo limite de espera para lock de modo que a transação expire automaticamente se o lock não for liberado dentro de um tempo especificado.

SET innodb_lock_wait_timeout = 10;

Com esta configuração, se o lock não for liberado em 10 segundos, o MySQL retorna um erro e termina automaticamente a transação.

5. Notas Importantes e Melhores Práticas para Locks do MySQL

O gerenciamento adequado de locks ajuda a reduzir o risco de deadlocks e degradação de desempenho. Abaixo estão as melhores práticas para minimizar locks e gerenciá-los eficientemente.

5.1 Como Prevenir Deadlocks

Para prevenir deadlocks, mantenha os seguintes pontos em mente:

1. Padronize a Ordem de Execução das Transações

• Por exemplo, ao atualizar múltiplas tabelas, sempre atualize-as na mesma ordem .
• Exemplo:

  -- OK: Always update in the order orders → customers
  BEGIN;
  UPDATE orders SET status = 'shipped' WHERE order_id = 1;
  UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
  COMMIT;
  

× NG: Ordens de execução diferentes podem causar deadlocks

-- Session 1
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
COMMIT;

-- Sessão 2 (deadlock pode ocorrer se executado na ordem inversa)
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

2. Keep Transactions Short

• Commit or roll back as quickly as possible
• Avoid long-running transactions, as they can negatively impact other processes.

3. Set Appropriate Indexes

• Creating proper indexes helps avoid unnecessary locks .
• Example: Adding an index on customer_id in the orders table ensures that only specific rows are locked .

  CREATE INDEX idx_customer_id ON orders (customer_id);
  

6. Summary

• MySQL locks include row locks, table locks, and intention locks . Improper management can lead to deadlocks and performance issues.
• The method for checking lock status varies depending on the MySQL version , so choose the appropriate approach for your environment.
• Be cautious when releasing locks!
• Try ROLLBACK before using the KILL command.
• Use SET innodb_lock_wait_timeout to automatically handle lock timeouts.
• To prevent deadlocks, standardize transaction execution order and keep transactions short .

7. FAQ (Frequently Asked Questions)

Q1. What is the easiest command to check MySQL lock status?

• A1. In MySQL 8.0 and later, use SELECT * FROM performance_schema.data_locks; to easily check lock status.

Q2. What should I do if a deadlock occurs?

• A2. First, run SHOW ENGINE INNODB STATUS\G; to identify the cause of the deadlock. Then review and standardize the transaction execution order to prevent recurrence.

Q3. Can using the KILL command corrupt data?

• A3. When forcibly terminating a session, unfinished transactions are rolled back, which may affect data consistency. Use it with caution.

Q4. How can I prevent deadlocks?

• A4. The following methods are effective:
• Standardize transaction execution order
• Keep transactions short
• Set appropriate indexes

Q5. How can I reduce locks and improve MySQL performance?

• A5.
• Design proper indexes to reduce unnecessary locks
• Keep transactions short to minimize lock duration
• Avoid full table locks (LOCK TABLES)
• Use read replicas to distribute read workloads