GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

SELECT FOR UPDATE vs. UPDATE, then SELECT

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SELECT FOR UPDATE vs. UPDATE, then SELECT

    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?
Working...
X