title: Solution for Insert Intention Locks in MySQL
body: <p>I have very simple table:</p>
  
  <pre><code>CREATE TABLE `d` (
      `id` int(11) DEFAULT NULL,
      UNIQUE KEY `id` (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>without records:</p>
  
  <pre><code>select * from d;
  Empty set (0,01 sec)
  </code></pre>
  
  <p>Then I try to open two transactions in different sessions:</p>
  
  <p><strong>Session #1:</strong></p>
  
  <pre><code>begin;
  Query OK, 0 rows affected (0,00 sec)
  
  select * from d where id = 100 for update;
  Empty set (0,00 sec)
  </code></pre>
  
  <p><strong>Session #2:</strong></p>
  
  <pre><code>begin;
  Query OK, 0 rows affected (0,00 sec)
  
  select * from d where id = 700 for update;
  Empty set (0,00 sec)
  </code></pre>
  
  <p>Now I try to insert new record in <strong>Session #2</strong> and session "freezes":</p>
  
  <pre><code>insert into d values (700);
  </code></pre>
  
  <p>And when I try to do the same (with another id field) in <strong>Session #1</strong> it crashes:</p>
  
  <pre><code>insert into d values (100); --&gt; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction in Session #1
  insert into d values (700); --&gt; Query OK, 1 row affected (4,08 sec) in Session #2
  </code></pre>
  
  <p>How can I to fix the deadlock? InnoDB status is:</p>
  
  <pre><code>------------------------
  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)
  </code></pre>