title: MySQL - Multi-threaded Update (no thread updates the same id) but having Deadlock
body: <p>I have a multi threaded java program that is running update statements on my MySQL DB. When I use threads I get deadlock although I am never updating the same rows in any two threads. For each time this query is ran field_a is different so why would I have lock issues?</p>
  
  <pre><code>thread1: field_a - 'A'
  thread2: field_a - 'B'
  thread3: field_a - 'C'
  </code></pre>
  
  <p>I am running a query like this</p>
  
  <pre><code> UPDATE table as t, 
              (
               SELECT field_a,
                     field_b,
                     TRUNCATE(AVG(Sumfield_c), 2) avgfield_c,
                     TRUNCATE(AVG(Sumfield_d), 2) avgfield_d
               FROM
                (SELECT field_a,
                        field_b,
                        DateString,
                        sum(field_c) Sumfield_c,
                        sum(field_d) Sumfield_d
                 FROM table
                 WHERE DateString &gt; DATE_FORMAT(SUBDATE(CURDATE(), 22), '%Y%m%d') and field_a = ? and id &lt;= ?
                 GROUP BY field_a,
                 field_b,
                          DateString) A
               GROUP BY field_a,
                       field_b
              ) as temp
               SET t.Avgfield_c = temp.avgfield_c, t.Avgfield_d = temp.avgfield_d WHERE t.field_a = temp.field_a and t.field_b = temp.field_b and t.id &gt; ?;
  </code></pre>