I have a large InnoDB MySQL database table. I would like to run this query against the database table:

Update myBigTable Set someColumn=x WHERE userId = 7 OR userId = 88 OR userId = 22 OR userId = 99;

The exact contents of the where clause is dynamic. Through a web front end, an administrator checks one or more users who they want to update via this query.

However, when the above query is run, mysql usually returns:

#1205 - Lock wait timeout exceeded; try restarting transaction

So I'm trying to find some way to refactor my sql query so that it doesn't need to do a table lock. The 'someColumn' that is being updated and the userId both have indices defined.

I first tried:

Update myBigTable Set someColumn=x WHERE userId IN (7, 88, 22, 99);

However, the same problem persisted. I know that I can do four separate queries like:

Update myBigTable Set someColumn=x WHERE userId = 7;
Update myBigTable Set someColumn=x WHERE userId = 88;
Update myBigTable Set someColumn=x WHERE userId = 22;
Update myBigTable Set someColumn=x WHERE userId = 99;

The above approach does work, but it requires four separate queries (and in the worst case, a user could spawn 150 queries based on our current UI design - this number could grow over time as well).

What is the best practice for doing a sql update statement in MySQL Innodb to update multiple rows in a large table to the same value?

1 Answer 1


SHOW ENGINE INNODB STATUS\G and see https://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded increasing the lock timeout period could solve the issue. Apparently default is 50seconds. Which is maybe enough for all of the rows with userid=99, but not enough for rows with 99,22,88 or 7 userid. Just speculation.

  • I was really hoping to find a way to fundamentally change my query to make it not dependent on the timeout period. Even with an increased timeout, end users will probably not want to wait 50+ seconds for the operation to complete. Jul 14, 2014 at 18:34
  • Is your userID indexed?
    – Bunny
    Jul 14, 2014 at 18:35
  • Yes, both userId and someColumn are indexed in two separate indices (not a composite). Jul 14, 2014 at 18:36
  • How are they indexed? Both (userID, someColumn) or an index for UserID and another index for someColumn?
    – Bunny
    Jul 14, 2014 at 18:37
  • One index for userId and a second index for someColumn Jul 14, 2014 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.