MySQL Update Multiple Rows in Large Table

Question

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?

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.

• curiousMysqlUser: 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.
• Bunny: Is your userID indexed?
• curiousMysqlUser: Yes, both userId and someColumn are indexed in two separate indices (not a composite).
• Bunny: How are they indexed? Both (userID, someColumn) or an index for UserID and another index for someColumn?
• curiousMysqlUser: One index for userId and a second index for someColumn