I am getting a deadlock error randomly on my server.
Details from show engine innodb status below.
Can someone help understanding why this is happening and how to avoid it.
This is urgent.
------------------------
LATEST DETECTED DEADLOCK
------------------------
171205 16:35:54
*** (1) TRANSACTION:
TRANSACTION 644AE84B8, ACTIVE 3 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 7 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 654886691, OS thread handle 0x7f0832042700, query id 39940109291
INSERT INTO <table> SET field1=x, field2='y', field3='z'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6285 page no 10493 n bits 856 index `table_ibfk_1` of table `db`.`table` trx id 644AE84B8 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 513 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 6; hex 545435313638; asc TT5168;;
1: len 4; hex 0022eb55; asc " U;;
*** (2) TRANSACTION:
TRANSACTION 644AE882B, ACTIVE 1 sec inserting
mysql tables in use 2, locked 2
7 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 654886777, OS thread handle 0x7f0833965700, query id 39940112983
INSERT INTO table SET field1=x1, field2='y1', field3='z1'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6285 page no 10493 n bits 856 index `table_ibfk_1` of table `db`.`table` trx id 644AE882B lock mode S locks gap before rec
Record lock, heap no 513 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 6; hex 545435313638; asc TT5168;;
1: len 4; hex 0022eb55; asc " U;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6285 page no 10493 n bits 856 index `table_ibfk_1` of table `db`.`table` trx id 644AE882B lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 513 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 6; hex 545435313638; asc TT5168;;
1: len 4; hex 0022eb55; asc " U;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS