title: Mysql Deadlock on insert and update
body: <p>I am running into a dead lock during moderate-to-high load situations. Here are the details.</p>
  
  <p>MySQL-5.5.21-55</p>
  
  <p>Engine: InnoDB</p>
  
  <p><strong>Table: Order</strong></p>
  
  <pre><code># Field, Type, Null, Key, Default, Extra
  id, bigint(20) unsigned, NO, PRI, , auto_increment
  sno, varchar(32), NO, MUL, ,
  misc1, int, NO, , 0,
  </code></pre>
  
  <p><strong>Table: OrderItem</strong> </p>
  
  <pre><code># Field, Type, Null, Key, Default, Extra
  id, bigint(20) unsigned, NO, PRI, , auto_increment
  order_id, bigint(20), YES, MUL, , 
  f1, varchar(50), YES, , , 
  f2, varchar(100), YES, , , 
  misc2, int, NO, , 0,
  </code></pre>
  
  <ul>
  <li><strong>Order</strong>.sno is unique</li>
  <li><strong>OrderItem</strong>.order_id is not defined as foreign key but used as foreign key in the application</li>
  <li><strong>Order</strong> has <em>one-to-many</em> relation with <strong>OrderItem</strong></li>
  <li><strong>OrderItem</strong>.order_id + <strong>OrderItem</strong>.f1 + <strong>OrderItem</strong>.f2 is unique</li>
  </ul>
  
  <p><strong>Usecase:</strong></p>
  
  <ul>
  <li>Whenever any record in <strong>Order</strong> or <strong>OrderItem</strong> needs to be updated, I want to invalidate the old records (or delete) and insert new ones.</li>
  <li>It may so happen that earlier, one record in <strong>Order</strong> (e.g order1) has 3 records in <strong>OrderItem</strong> (e.g orderItem1, orderItem2, orderItem3). But now I want to have it as order1->orderItem1, orderItem4, orderItem5 or entirely new set. This is the reason why i want to invalidate the old records all together and insert new ones as finding out what is changed in <strong>OrderItem</strong> is complicated.</li>
  </ul>
  
  <p>Multiple threads will be doing this operation; but they will work on different record sets. I'm operating on 25 <strong>Order</strong> s at a time</p>
  
  <p><strong>What I tried:</strong></p>
  
  <ul>
  <li>Insert into <strong>Order</strong>; on duplicate key update <strong>Order</strong> and delete all children from <strong>OrderItem</strong> and insert the <strong>OrderItem</strong> s.</li>
  <li>Have another column called <em>is_active</em> in <strong>Order</strong> and mark all the records for the same <em>sno</em> as 0 and insert the new records in <strong>Order</strong>; insert new children into <strong>OrderItem</strong>.</li>
  <li>Delete from <strong>Order</strong> for the given sno; delete from <strong>OrderItem</strong> for the same sno.Insert into both the tables freshly.</li>
  </ul>
  
  <p>All the above approaches resulted in deat lock, some or the other time.
  No other thread or process is working on these tables.</p>
  
  <p><strong>Observation:</strong></p>
  
  <p>Went through the following links</p>
  
  <ul>
  <li><a href="http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html" rel="nofollow">InnoDB record level locks</a></li>
  <li><a href="http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html" rel="nofollow">InnoDB locks set</a></li>
  </ul>
  
  <p>and found that updating/deleting multiple records causes MySQL to obtain <em>Next Key</em> locks at REPEATABLE_READ isolation level (which is the default). In my opinion this causes the problem.</p>
  
  <p>Appreciate if you can provide some direction on solving this.</p>