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