# SELECT FOR UPDATE vs. UPDATE, then SELECT

**URL:** <https://forums.percona.com/t/select-for-update-vs-update-then-select/1606>\
**Category:** Other MySQL® Questions\
**Created:** [February 7, 2011, 4:40am UTC](https://forums.percona.com/t/select-for-update-vs-update-then-select/1606 "2011-02-07T04:40:09Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![lex0r](https://avatars.discourse-cdn.com/v4/letter/l/cdc98d/32.png) [@lex0r](https://forums.percona.com/u/lex0r)\
**Post date:** [February 7, 2011, 4:40am UTC](https://forums.percona.com/t/select-for-update-vs-update-then-select/1606/1 "2011-02-07T04:40:09Z")

</div>

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?