Niet the Dark Absol Niet the Dark Absol - 5 months ago 9
SQL Question

Impossible MySQL error‽

This has me baffled, here's hoping someone can help.

Query:

insert into `shelter_pages` (`userid`,`relid`)
select :userid, `id` from `shelter` where `stage`='egg' order by rand() limit 30


Simple, right? Take 30 random rows meeting a condition, and save them in the "pages" table along with the user id.

The error:


SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_name`.`shelter_pages`, CONSTRAINT `shelter_pages_ibfk_2` FOREIGN KEY (`relid`) REFERENCES `shelter` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)


Well how can that be? It's literally taking these
`shelter`.`id`
values in the
SELECT
query and
INSERT
ing them, how can the foreign key check possibly fail?

If it's of any significance, the table in question is fairly "busy" - it is part of a gameplay element where any player can "adopt" from the shelter, thus deleting the row. Is this as simple as a race condition in what I thought would be an atomic operation?

Answer

This is likely happening because you have your transaction isolation level set to "dirty read" (i.e. READ UNCOMMITTED). This means that the SELECT could be reading uncommitted data that would fail the foreign key constraints during the INSERT.

Comments