# Deadlock with foreign key constraints

**URL:** <https://forums.percona.com/t/deadlock-with-foreign-key-constraints/4870>\
**Category:** Other MySQL® Questions\
**Created:** [May 4, 2016, 6:32pm UTC](https://forums.percona.com/t/deadlock-with-foreign-key-constraints/4870 "2016-05-04T18:32:07Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![sjangra](https://avatars.discourse-cdn.com/v4/letter/s/77aa72/32.png) [@sjangra](https://forums.percona.com/u/sjangra)\
**Post date:** [May 4, 2016, 6:32pm UTC](https://forums.percona.com/t/deadlock-with-foreign-key-constraints/4870/1 "2016-05-04T18:32:07Z")

</div>

We are using MariaDB 10.1 and facing some weird deadlock issues. While investigating we ran a sample program to test the foreign keys and found a deadlock error. Can someone please explain this in bit detail so we understand how locking works in galera.

We used a 2 node cluster for this testing. [TABLE=“border: 0, cellpadding: 0, cellspacing: 0, height: 1”]  
[TR]  
[TD]  
[/TD]  
[/TR]  
[TR]  
[TD]  
[/TR]  
[TR]  
[TD]  
[/TR]  
[TR]  
[TD] [/TD]  
[/TR]  
[TR]  
[TD]  
[/TR]  
[TR]  
[TD]  
[/TR]  
[TR]  
[TD] [/TD]  
[/TR]  
[TR]  
[TD]  
[/TD]  
[/TR]  
[TR]  
[TD]  
[/TR]  
[TR]  
[TD]  
[/TR]  
[TR]  
[TD]  
[/TR]  
[TR]  
[TD] [/TD]  
[/TR]  
[TR]  
[TD]  
[/TR]  
[TR]  
[TD]  
[/TR]  
[TR]  
[TD] [/TD]  
[/TR]  
[TR]  
[TD]  
[/TD]  
[/TR]  
[TR]  
[TD]  
[/TR]  
[TR]  
[TD] [/TD]  
[/TR]  
[TR]  
[TD]  
[/TD]  
[/TR]  
[TR]  
[TD]  
[/TR]  
[TR]  
[TD]  
[/TR]  
[/TABLE]

## Once I got this error I ran the ‘SHOW ENGINE INNODB STATUS\G’ on node2 and here is the truncated output.

## LATEST FOREIGN KEY ERROR

## 2016-05-04 17:46:34 7ff98ead8b00 Transaction: TRANSACTION 3852, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 3 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 1 --------------------\> (LOOK HERE) MySQL thread id 2507, OS thread handle 0x7ff98ead8b00, query id 73514 localhost root update insert into dl2(pk, pk1, data) values(10, 1, ‘a0’) Foreign key constraint fails for table `test`.`dl2`: , CONSTRAINT `dl2_fk` FOREIGN KEY (`pk1`) REFERENCES `dl1` (`pk`) Trying to add in child table, in index `dl2_fk` tuple: DATA TUPLE: 2 fields; 0: len 4; hex 80000001; asc ;; 1: len 4; hex 8000000a; asc ;; But in parent table `test`.`dl1`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n\_fields 1; compact format; info bits 0 0: len 8; hex 696e66696d756d00; asc infimum ;;

## ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue  
0 read views open inside InnoDB  
1 RW transactions active inside InnoDB ------------------\> (LOOK HERE)  
0 RO transactions active inside InnoDB  
1 out of 1000 descriptors used  
Main thread process no. 1242, id 140706194630400, state: sleeping  
Number of rows inserted 0, updated 0, deleted 0, read 0  
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s  
Number of system rows inserted 0, updated 0, deleted 0, read 0  
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s