Início / dba / Perguntas / 346163
Accepted
cccllll
Asked: 2025-04-15 02:44:49 +0800 CST

bloqueio de linha mysql/postgresql em cenários de alta simultaneidade

  • 772

eu tenho uma transação simples com nível de isolamento READ COMMITTED A configuração da tabela é simples

create table example.parent_tbl(
id int auto_increment primary key
);

create table example.child_tbl(
id int auto_increment primary key,
parent_id int not null,
update_dt timestamp default current_timestamp ON UPDATE current_timestamp,

foreign key (parent_id) references parent_tbl(id),
unique (parent_id)
);

select 
p.*
from example.parent_tbl p
where p.id not in (select parent_id from example.child_tbl)
limit 1
for update
skip locked;

depois disso, inserirei um novo registro com parent_tbl.idinto child_tbl.

A coluna ID child_tblé única. Tenho vários processos em execução em paralelo e, ocasionalmente, um deles gera uma exceção de integridade, pois o mesmo ID child_tbljá existe.

PERGUNTA

por que uma transação pode selecionar um ID que aparece em child_tbl?

A lógica parece trivial.

Ou a transação A mantém o bloqueio de linha parent_tblantes da confirmação, então a transação B irá ignorá-lo.

OU

A transação A foi confirmada e a cláusula where da transação B filtrará a linha e a ignorará.

Mas a verdade é que não é bem assim!!!

O log de consulta geral mostra o comportamento contraintuitivo deste mecanismo de bloqueio

log do lado do servidor

Há dois processos em execução em paralelo, fazendo a mesma coisa. Neste caso, eles são os threads 60 e 61, respectivamente.

Dois resultados são observados.

  1. A thread 61 inseriu o ID 113 na tabela filha e liberou o bloqueio de linha. A thread 60 executa a consulta, mas não consegue detectar a linha com o ID 113 recém-inserida pela thread 61.

  2. o thread 61 inseriu o id 113 na child_tbl e logo antes de confirmar e liberar o bloqueio de linha, outro thread 60 executa a consulta e adquire o mesmo bloqueio de linha da parent_tbl .

Adicional

Fiz os mesmos testes com o PostgreSQL e também encontrei uma exceção.

mysql

1 respostas

  1. Best Answer

    Se você observar a EXPLAINsaída da sua instrução, provavelmente verá a subconsulta child_tblem um InitPlan, enquanto o Lock Rows está no topo. Isso pode lhe dar uma pista de que a subconsulta é executada antes do bloqueio das linhas.

    Agora, pode acontecer que uma transação mantenha um bloqueio em uma parent_tbllinha e insira uma nova linha em child_tbl, enquanto outra transação em uma sessão diferente executa a subconsulta. Nossa primeira transação ainda não foi confirmada, então a segunda transação child_tblainda não pode ver essas linhas. Ela tenta bloquear a mesma parent_tbllinha e obtém sucesso, porque a primeira transação já foi confirmada no momento em que a segunda transação tenta bloquear a linha. Uma inserção subsequente em child_tblpode então acionar uma violação de restrição.

    A propósito: é melhor usar NOT EXISTS (...)instead of NOT IN (...). Primeiro, se child_tblcontiver um parent_idthat IS NULL, a consulta nunca retornará um resultado, o que provavelmente não é o que você pretende. Segundo, NOT EXISTSpode ser executado como um anti-join com melhor desempenho.

    • 2

relate perguntas