title: SELECT FOR UPDATE vs. UPDATE, then SELECT
body: <p>I've created a service application that uses multi-threading for parallel processing of data located in an InnoDB table (about 2-3 millions of records, and no more InnoDB-related queries performed by the application). Each thread makes the following queries to the mentioned table:</p>
  
  <ol>
  <li>START TRANSACTION</li>
  <li>SELECT FOR UPDATE (SELECT pk FROM table WHERE status='new' LIMIT 100 FOR UPDATE)</li>
  <li>UPDATE (UPDATE table SET status='locked' WHERE pk BETWEEN X AND Y)</li>
  <li>COMMIT</li>
  <li>DELETE (DELETE FROM table WHERE pk BETWEEN X AND Y)</li>
  </ol>
  
  <p>The guys from forum.percona.com gave me a piece of advice - do not use SELECT FOR UPDATE and UPDATE because of longer time needed for transaction to execute (2 queries), and waiting lock timeouts that result. Their advice was (autocommit is on):</p>
  
  <ol>
  <li>UPDATE (UPDATE table SET status='locked', thread = Z LIMIT 100)</li>
  <li>SELECT (SELECT pk FROM table WHERE thread = Z)</li>
  <li>DELETE (DELETE FROM table WHERE pk BETWEEN X AND Y)</li>
  </ol>
  
  <p>and it was supposed to improve performance. However, instead, I got even more deadlocks and wait lock timeouts than before...</p>
  
  <p>I read a lot about optimizing InnoDB, and tuned the server correspondlingly, so my InnoDB settings are 99% ok. This fact is also proven by the first scenario working fine and better than second one. The my.cnf file:</p>
  
  <pre><code>innodb_buffer_pool_size = 512M
  innodb_thread_concurrency = 16
  innodb_thread_sleep_delay = 0
  innodb_log_buffer_size = 4M
  innodb_flush_log_at_trx_commit=2
  </code></pre>
  
  <p>Any ideas why the optimization had no success?</p>