title: Why update clause takes a long time while select clause (with the same logic) doesn't?
body: <p>Here is a <code>select</code> query which executes in <code>0.03</code> sec:</p>
  <pre><code>select * from engine.transactions 
  where transaction_row not in (select transaction_row from d.pos_transactions)
  </code></pre>
  <p>I want to update the matched (selected) rows from the query above this way:</p>
  <pre><code>update engine.transactions set retry = 0 
  where transaction_row not in (select transaction_row from d.pos_transactions)
  </code></pre>
  <p>which returns <code>timeout</code> error:</p>
  <blockquote>
  <p>#1205 - Lock wait timeout exceeded; try restarting transaction</p>
  </blockquote>
  <p>Why the performance drops this much on <code>update</code> clause?</p>
  <hr />
  <p>Noted that there is only one index on <code>engine.transactions</code> table: <code>transactions(psp_id, transaction_row)</code>.</p>
  <p>Also, the table engine is <code>InnoDB</code></p>