mysql/postgresql row lock in high concurrency scenarios

i have a simple transaction with isolation level READ COMMITTED The table setting is simple

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)
);

sql
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;

after that, i will insert a new record with parent_tbl.id into child_tbl. The ID column in child_tbl is unique. I have multiple processes running in parallel and occasionally one of them will hit integrity exception as the same ID exists in child_tbl already. # QUESTION why a transaction can select a ID that appears in child_tbl? The logic seems trivial. Either transaction A holds row lock of parent_tbl before commit, then transaction B will skip it. OR Transaction A has committed and transaction B where clause will filter out the row and will skip it. But the fact tells me this is not the case!!! -------------------- The general query log shows the counterintuitive behaviour of this locking mechanism There are two process running in parallel, doing the same thing. in this case, they are thread 60 & 61 respectively. Two outcomes are observed. 1. thread 61 inserted the id 113 into the **child_tbl** and release the row lock. Thread 60 runs the query but it fails to detect the row with id 113 just inserted by thread 61. 2. thread 61 inserted the id 113 into the **child_tbl** and right before it commit and release the row lock, another thread 60 runs the query and acquire the same row lock of the **parent_tbl**. ------------- # Additional I have do the same tests with PostgreSQL and I hit exception as well.