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.