Bloqueio de Tabela: O InnoDB suporta bloqueio de linha, mas não exclui bloqueio de tabela

Bloqueios S e X de tabela: Similar ao MyISAM, mas não recomendado no InnoDB.

-- Verificar tabelas com bloqueio atual
SELECT * FROM information_schema.tables WHERE table_lock_count > 0;

-- Adicionar bloqueio S à tabela (todas as transações podem ler, nenhuma pode escrever)
LOCK TABLE tabela_clientes READ;

-- Adicionar bloqueio X à tabela (a transação detentora pode ler e escrever, outras transações não podem acessar)
LOCK TABLE tabela_clientes WRITE;

-- Liberar bloqueio
UNLOCK TABLES;

Bloqueio de Intenção: Otimiza operações de bloqueio de tabela.

• Se T1 adiciona um bloqueio X a uma linha, T2 tenta bloquear a tabela: deve esperar T1 finalizar.
• Para evitar varredura de todas as linhas, bloqueios de intenção são adicionados automaticamente quando há bloqueios de linha.
• Facilita a verificação de bloqueios existentes sem varrer cada registro.

Bloqueio de Auto-incremento: Gerencia chaves primárias auto-incrementáveis.

Bloqueio de Metadados (MDL): Um bloqueio exclusivo para operações de ALTER TABLE.

• Garante consistência durante consultas e modificações na estrutura da tabela.
• Durante consultas, bloqueios de intenção são adicionados; operações de ALTER TABLE são bloqueadas até a liberação.

Bloqueio de Registro (Record Lock)

Implementado na camada do armazenamento, presente apenas no InnoDB.

• Oferece alta concorrência, mas maior custo de manutenção; pode causar deadlocks.
• Inclui bloqueios S e X; se baseado em colunas não indexadas, pode ser escalado para bloqueio de tabela.
• Operações INSERT, UPDATE e DELETE adicionam bloqueio X automaticamente; SELECT adiciona bloqueio S (pode ser modificado para X).
• Bloqueio S em uma linha permite outros bloqueios S, mas bloqueia X.
• Bloqueio X em uma linha bloqueia tanto S quanto X por outras transações.
• Bloqueios são liberados após COMMIT.

Bloqueio de Gap (Gap Lock)

Um tipo especial de bloqueio exclusivo (X) para intervalos.

• Usado para evitar leituras fantasma (phantom reads) em operações INSERT em registros inexistentes.
• Bloqueia um intervalo aberto, não os limites.

-- Dados existentes: id_cliente = 1, id_cliente = 3, id_cliente = 8
-- Bloquear o intervalo (3, 8) para registro inexistente id_cliente = 4
BEGIN;
SELECT * FROM clientes WHERE id_cliente = 4 FOR UPDATE;

-- Outra transação, inserção no intervalo é bloqueada
BEGIN;
INSERT INTO clientes (id_cliente, nome) VALUES (5, 'Ana');

-- Intervalo (8, infinito) para id_cliente = 10
BEGIN;
SELECT * FROM clientes WHERE id_cliente = 10 FOR UPDATE;

Bloqueio Next-Key

Combina bloqueio de registro e bloqueio de gap, incluindo os limites do entervalo.

-- Dados existentes: id_cliente = 1, id_cliente = 3, id_cliente = 8
-- Bloquear intervalo (3, 8] com bloqueio X
BEGIN;
SELECT * FROM clientes WHERE id_cliente > 3 AND id_cliente <= 8 FOR UPDATE;

-- Inserção no intervalo é bloqueada
BEGIN;
INSERT INTO clientes (id_cliente, nome) VALUES (5, 'Carlos');

Bloqueio Otimista e Pessimista

Bloqueio Pessimista: Implementado no banco de dados para segurança em escritas.

• Usa bloqueios X em consultas e atualizações para garantir concorrência.
• Condições WHERE devem usar índices para localizar registros específicos; sem índicos, varredura completa pode bloquear todas as linhas.
• Ideal para cenários com muitas operações de escrita.

Bloqueio Otimista: Implementado na aplicação, focado em leituras.

• Mecanismo de versão: Adiciona uma coluna versão, incrementada a cada atualização.
  • Consulta a versão atual antes de modificar.
  • Atualiza com condição WHERE versão = valor_atual.
  • Se falhar, repete o processo (CAS) para evitar conflitos.
• Mecanismo de timestamp: Similar à versão, usando timestamps.
• Ideal para cenários com muitas operações de leitura.

Deadlocks

Exemplos de deadlock:

Na mesma tabela (bloqueio de registro):

Em tabelas diferentes:

Com bloqueio de gap (dados: id_cliente=1, 3, 9):

Soluções para deadlocks:

• Aguardar timeout, configurado pelo parâmetro innodb_lock_wait_timeout (padrão 50s).
• Rollback da transação com menor operação de undo, identificada por ciclos de espera.

Prevenção de deadlocks:

• Otimizar SQL usando índices em condições WHERE para reduzir varreduras.
• Evitar transações longas; dividi-las em transações menores.
• Utilizar bloqueio otimista.
• Reduzir nível de isolamento para aumentar granularidade de bloqueio.