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?