0

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?

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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