title: mysql insert on duplicate key related deadlock
body: <p>In logs I have deadlocks that are related to this query: </p>
  
  <pre><code>INSERT INTO `driver_state` (id, state)
    VALUES('83799','waiting')
  ON DUPLICATE KEY UPDATE    
    state = IF(state = 'active', state, VALUES(state));
  </code></pre>
  
  <p>Exact error:</p>
  
  <pre><code>ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction 
  </code></pre>
  
  <p>I have tried to reason and understand how this query could even possibly cause a deadlock, but haven't gotten very far.</p>
  
  <p>Table structure</p>
  
  <pre><code> CREATE TABLE IF NOT EXISTS `driver_state` (
     `id` int(11) NOT NULL,
     `state` enum('inactive','waiting_orders','has_order','busy') DEFAULT NULL,
   PRIMARY KEY (`id`)
  ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>In this case we use MEMORY table, but with InnoDB it has same locking issues.</p>
  
  <p>Table gets lots of these insert on duplicate key queries and select queries (All select queries are very fast and optimized). Transactions are not used anywhere nor manual locking etc.</p>
  
  <p>Could you please propose any ideas what could possibly cause this?</p>