maria-discuss team mailing list archive

[AskMonty KB <noreply@xxxxxxxxxxxx>]

Hello,

A new question has been asked in "MariaDB Community" by sjangra. Please answer it at http://mariadb.com/kb/en/deadlock-with-foreign-key-constraints/ as the person asking the question may not be subscribed to the mailing list.

--------------------------------
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.

Session 1 (Node1):
------------------------

create table dl1(pk int primary key, data varchar(100));
create table dl2(pk int primary key, pk1 int not null, constraint dl2_fk foreign key(pk1) references dl1(pk), data varchar(100));
set autocommit=off;
insert into dl1 values(1, 'a');

Session 2 (Node2):
------------------------

set autocommit=off;

insert into dl2(pk, pk1, data) values(10, 1, 'a0');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`demoApp`.`dl2`, CONSTRAINT `dl2_fk` FOREIGN KEY (`pk1`) REFERENCES `dl1` (`pk`)) -- as expected

select * from dl1;

Empty set (0.00 sec) -- as expected

Session 1 (Node1):
------------------------

commit;

Session 2 (Node2):
------------------------

select * from dl1;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction --- strange

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

To view or answer this question please visit: http://mariadb.com/kb/en/deadlock-with-foreign-key-constraints/