Comments - Deadlock with foreign key constraints
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
Hi,
Firstly, lets run above transactions on single node InnoDB without any Galera. First insert-statement will take row locks to avoid phantom inserts to dl1. This means that second insert-statement to dl2 has to wait for that lock. This is because that insert is dependent on parent row in dl1. Thus, on single node InnoDB above transactions fail on second insert with lock wait timeout. Remember ACID rules, transactions are run on isolation, so they do not know each other.
Now lets consider Galera case. When node1 issues commit, the insert will be executed also on node2. This is problematic as transaction on node2 is still open (yes we did roll back the statement containing the insert but not whole transaction yet). This also means that all row locks are maintained. New select statement requires also shared locks to maintain repeatable read isolation level so it will compete with insert statement from node1. But, insert from node1 should commit and may not wait for locks so select in node2 is aborted. Now naturally the error is kind of misleading, but better than nothing.