title: Avoiding MySQL Deadlocks in a multithreaded Spring app
body: <p>The scenario is simple.<br>
  I have a somehow large MySQL db containing two tables:   </p>
  
  <pre><code>-- Table 1
  id (primary key) | some other columns without constraints
  -----------------+--------------------------------------
         1         |       foo
         2         |       bar
         3         |       foobar
        ...        |       ...
  
  -- Table 2
  id_src | id_trg | some other columns without constraints
  -------+--------+---------------------------------------
     1   |   2    |    ...
     1   |   3    |    ...
     2   |   1    |    ...
     2   |   3    |    ...
     2   |   5    |    ...
     ...
  </code></pre>
  
  <ul>
  <li>On table1 only <code>id</code> is a primary key. This table contains about 12M entries.</li>
  <li>On table2 <code>id_src</code> and <code>id_trg</code> are both primary keys and both have foreign key constraints on table1's <code>id</code> and they also have the option <code>DELETE ON CASCADE</code> enabled. This table contains about 110M entries.</li>
  </ul>
  
  <p>Ok, now what I'm doing is only to create a list of <code>id</code>s that I want to remove from table 1 and then I'm executing a simple <code>DELETE FROM table1 WHERE id IN (&lt;the list of ids&gt;);</code>  </p>
  
  <p>The latter process is as you may have guessed would delete the corresponding id from table2 as well. So far so good, but the <strong>problem</strong> is that when I run this on a multi-threaded env and I get many <code>Deadlocks</code>!  </p>
  
  <p><strong>A few notes:</strong>  </p>
  
  <ul>
  <li>There is no other process running at the same time nor will be (for the time being)</li>
  <li>I want this to be fast! I have about 24 threads (if this does make any difference in the answer)</li>
  <li>I have already tried almost all of transaction isolation levels (except the TRANSACTION_NONE) <a href="http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html" rel="noreferrer">Java sql connection transaction isolation</a>  </li>
  <li>Ordering/sorting the id's I think would not help!</li>
  <li><p>I have already tried <code>SELECT ... FOR UPDATE</code>, but a simple <code>DELETE</code> would take up to 30secs! (so there is no use of using it) : </p>
  
  <pre><code>DELETE FROM table1 
  WHERE id IN ( 
      SELECT id FROM (
          SELECT * FROM table1 
          WHERE id='some_id' 
          FOR UPDATE) AS x);  
  </code></pre></li>
  </ul>
  
  <p>How can I fix this?</p>
  
  <p>I would appreciate any help and thanks in advance :)</p>
  
  <p><strong>Edit:</strong>  </p>
  
  <ol>
  <li>Using InnoDB engine</li>
  <li>On a single thread this process would take a dozen hours even maybe a whole day, but I'm aiming for a few hours!</li>
  <li>I'm already using a connection pool manager: <code>java.util.concurrent</code></li>
  <li>For explanation on double nested <code>SELECT</code>s please refer to <a href="http://www.codeproject.com/Tips/831164/MySQL-can-t-specify-target-table-for-update-in-FRO" rel="noreferrer">MySQL can’t specify target table for update in FROM clause</a></li>
  <li>The list that is to be deleted from DB, may contain a couple of million entries in total which is divided into chunks of 200</li>
  <li>The <code>FOR UPDATE</code> clause is that I've heard that it locks a single row instead of locking the whole table</li>
  <li>The app uses Spring's batchUpdate(String sqlQuery) method, thus the transactions are managed automatically</li>
  <li>All ids have index enabled and the ids are unique 50 chars max!</li>
  <li><code>DELETE ON CASCADE</code> on <code>id_src</code> and <code>id_trg</code> (each separately) would mean that every delete on table1 <code>id=x</code> would lead to deletes on table2 <code>id_src=x</code> and <code>id_trg=x</code></li>
  <li><p>Some code as requested:  </p>
  
  <pre><code>public void write(List data){
      try{
          Arraylist idsToDelete = getIdsToDelete();
          String query = "DELETE FROM table1 WHERE id IN ("+ idsToDelete + " )";
          mysqlJdbcTemplate.getJdbcTemplate().batchUpdate(query);
         } catch (Exception e) {
             LOG.error(e);
         }
  }
  </code></pre></li>
  </ol>
  
  <p>and <code>myJdbcTemplate</code> is just an abstract class that extends <code>JdbcDaoSupport</code>.</p>