Re: SELECT FOR UPDATE is very slow

[Rick James]

1. Turn off auto_commit; instead use BEGIN and COMMIT.

2. Don't use FOR UPDATE for longer than doing an UPDATE in the database. That is do not use it for hanging onto the row.

3. Add a field that says the process is working on the item. Later turn off this flag, or drop the row.

4. For robustness, consider making that flag a timestamp -- and write code to discover that an item was 'taken' but then forgotten. Without this code, you could have 'taken' item permanently in the table (when the processing code dies or otherwise fails to release it).