SELECT FOR UPDATE vs. UPDATE, then SELECT

MySQL & MariaDB Other MySQL® Questions
1

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 against the table:

  1. START TRANSACTION
  2. SELECT FOR UPDATE
  3. UPDATE
  4. COMMIT
  5. DELETE

The guys from the forum gave me an (advice) - do not use SELECT FOR UPDATE and UPDATE because of longer time needed for transaction to execute, and waiting lock timeouts. Their advice was (autocommit is on):

  1. UPDATE
  2. SELECT (simple, non-locking like SELECT FOR UPDATE)
  3. DELETE

that should have improved performance. Instead, I got more deadlocks and wait lock timeouts…

My settings are ok, at least the first scenario works fine and better than second with them:

innodb_buffer_pool_size = 512Minnodb_thread_concurrency = 16innodb_thread_sleep_delay = 0innodb_log_buffer_size = 4Minnodb_flush_log_at_trx_commit=2

Any ideas why the optimization had no success?