I have very simple table:
CREATE TABLE `d` (
`id` int(11) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
without records:
select * from d;
Empty set (0,01 sec)
Then I try to open two transactions in different sessions:
Session #1:
begin;
Query OK, 0 rows affected (0,00 sec)
select * from d where id = 100 for update;
Empty set (0,00 sec)
Session #2:
begin;
Query OK, 0 rows affected (0,00 sec)
select * from d where id = 700 for update;
Empty set (0,00 sec)
Now I try to insert new record in Session #2 and session "freezes":
insert into d values (700);
And when I try to do the same (with another id field) in Session #1 it crashes:
insert into d values (100); --> ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction in Session #1
insert into d values (700); --> Query OK, 1 row affected (4,08 sec) in Session #2
How can I to fix the deadlock? InnoDB status is:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-07-06 15:59:25 0x70000350d000
*** (1) TRANSACTION:
TRANSACTION 43567, ACTIVE 15 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 123145358217216, query id 89 localhost root update
insert into d values (700)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43567 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 43568, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 123145357938688, query id 90 localhost root update
insert into d values (100)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43568 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43568 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)
Best Answer
This deadlock error is a bug in the MySQL InnoDB engine that has not been fixed for 12 years. (Bug #25847: https://bugs.mysql.com/bug.php?id=25847, workaround: How do I lock on an InnoDB row that doesn't exist yet?) It is not related to Unique Key. Running this query will also result in the same Deadlock error.
InnoDB Status is the same: