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?
Best Answer
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.