title: MySQL deadlocks when there shouldn't be
body: <p>I have a queue in a MySQL InnoDB table and 10 daemon processes that process this queue.
  The table "queue" has:</p>
  
  <ul>
  <li>id (auto-increment primary key)</li>
  <li>daemon (integer to record which daemon is working that item)</li>
  <li>processed (tinyint to show that processing is complete)</li>
  <li>other fields as needed</li>
  </ul>
  
  <p>My simplistic approach is to, for each deamon, reserve x rows in the queue table, and then process them.</p>
  
  <p>To reserve rows, I have:</p>
  
  <p><code>UPDATE queue SET daemon={daemonId} WHERE daemon IS NULL LIMIT {x}</code></p>
  
  <p>eg:
  <code>UPDATE queue SET daemon=3 WHERE daemon IS NULL LIMIT 50</code></p>
  
  <p>I get a lot of MySQL deadlocks here due to concurrency, but that should not be a problem. If a daemon fails to reserve rows, it will just continue with nothing, and try again in the next loop iteration.</p>
  
  <p>Once reserved, I read in the rows:</p>
  
  <p><code>SELECT * FROM queue WHERE daemon={daemonId} AND processed=0</code></p>
  
  <p>eg:
  <code>SELECT * FROM queue WHERE daemon=3 AND processed=0</code></p>
  
  <p>And then I process each item.</p>
  
  <p>After processing an item, I mark it as completed:
  <code>UPDATE queue SET processed=1 WHERE id={id}</code></p>
  
  <p>eg:
  <code>UPDATE queue SET processed=1 WHERE id=657954</code></p>
  
  <p>The thing I can't understand is that I'm getting MySQL errors in the log, eg:</p>
  
  <p><code>Deadlock found when trying to get lock; try restarting transaction UPDATE queue SET processed='1' WHERE id='69215794'</code></p>
  
  <p>How can this be?</p>
  
  <p>Since each deamon reserves it's rows, I don't see any way for different deamons to compete for the same rows, and I see no way that an individual daemon can deadlock itself.</p>