at the moment I'm sitting on a problem that turns out to be a deadlock within our backend. In order to be able to possibly reproduce the problem, I have had the "LATEST DETECTED DEADLOCK" output via the command "SHOW ENGINE INNODB STATUS;". Unfortunately I am not very experienced in such problems and hoped that you can help me.
*** (1) TRANSACTION:
TRANSACTION 81120, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 3810, OS thread handle 1740, query id 47722 localhost 127.0.0.1 root update
INSERT IGNORE INTO TableA (a, b, c, d, e) VALUES (null, \'strA\', null, \'strB\', 2)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 446 page no 4 n bits 80 index uniqueIndexName of table `DatabaseName`.`TableA` trx id 81120 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 10; hex 323032302d30312d3031; asc strC;;
2: len 8; hex 8000000000000003; asc ;;
*** (2) TRANSACTION:
TRANSACTION 81121, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 3809, OS thread handle 14824, query id 47723 localhost 127.0.0.1 root update
INSERT IGNORE INTO TableA (a, b, c, d, e) VALUES (null, \'strA\', null, \'strB\', 2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 446 page no 4 n bits 80 index uniqueIndexName of table DatabaseName.TableA trx id 81121 lock mode S
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 10; hex 323032302d30312d3031; asc strC;;
2: len 8; hex 8000000000000003; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 446 page no 4 n bits 80 index uniqueIndexName of table DatabaseName.TableA trx id 81121 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 10; hex 323032302d30312d3031; asc strC;;
2: len 8; hex 8000000000000003; asc ;;
As you can see, there are two transactions: Transaction 1 waits during an Insert Ignore for one lock which is hold by Transaction 2. But Transaction 2 holds and waits for the same lock to be granted...
Both transactions want to insert the same row with the same uniqueIndex. Only one should succeed while the other one should ignore the insertion.
I hope I provided enough information for you. Thanks in advance for your help :)
Greetings, Cloudy
