When deleting/updating from a child table in a FK relationship, if a parent table has referenced row(s) locked, the operation on a child table will get locked and fail as soon as the parent table is unlocked. For UPDATE case, it's important to touch a column, which is a part of a constraint.
The timing of the actions doesn't matter (only the order), as don't matter the settings for lock timeouts.
The issue does not reproduce on a regular Percona Server 5.7.29.
There are two problems here:
- DELETE on a child table gets locked, which doesn't happen on a regular PS. Maybe there's a reason for that.
- DELETE/UPDATE locked on a child table will always get ERROR 1205. Effectively, once the operation is locked in this case, it'll never finish without an error.
Could be introduced by this commit https://github.com/percona/percona-xtradb-cluster/commit/fd8b7c0f151b97980de3378f7f52c2bfe0236867
Reproduces on PXC 5.7.28 and PXC 8.0.18. Doesn't reproduce on PXC 5.7.14, where the DELETE doesn't block at all.