title: MySQL gap lock reasoning
body: <p>I'm having a deadlock and I'm trying to figure out the reasoning behind it.</p>
  
  <p>The question can be reduced to this:</p>
  
  <p>table:</p>
  
  <p><code>create table testdl (id int auto_increment, c int, primary key (id), key idx_c (c));</code></p>
  
  <p>Isolation level is repeatable read</p>
  
  <p>(Tx1): <code>begin; delete from testdl where c = 1000; -- nothing is deleted coz the table is empty</code></p>
  
  <p>(Tx2): <code>begin; insert into testdl (c) values (?);</code></p>
  
  <p>Whatever the value in Tx2 is, it hangs. So it basically means that Tx1 holds the gap of the whole range (-&#x221e;, +&#x221e;), when <code>delete from testdl where c = 1000</code> fails to find a match, right?.</p>
  
  <p><strong>So my question is: is this by design? What's the point of this if it is?</strong> </p>
  
  <p>Update:</p>
  
  <p>Say we already have a record in <code>testdl</code>:</p>
  
  <pre><code>+----+------+
  | id | c    |
  +----+------+
  |  1 | 1000 |
  +----+------+
  </code></pre>
  
  <p>Case 1:</p>
  
  <p>(Tx1): <code>select * from testdl where c = 500 for update; -- c = 500 not exists</code></p>
  
  <p>(TX2): <code>insert into testdl (c) values (?);</code></p>
  
  <p>In this case, any value >= 1000 can be inserted, so Tx1 locks the gap (-&#x221e;, 1000)</p>
  
  <p><strong>Again, is locking (-&#x221e;, 1000) necessary? What's the reasoning behind this?</strong></p>