2

I just wanted to know if the MySQL 5.5 with InnoDB will lock the whole table instead of some specific rows for SELECT query while UPDATE query is running on that specific table?

I can see that the SELECT queries stop responding while I was running the below query. All of the associated services were down in that particular period.

UPDATE table SET column='sample' where column is null;

Around 3.6 million(Total: 4 Million) rows were going to updated with this query.

  1. Index on the column which is part of where clause makes any effect on this?
  2. Is there any possible way to minimise the execution time? I am ok with the query changes as long as the output remains the same.
  • You don't need to lock the table in order to block a query; a single row is enough. – mustaccio Jul 25 '18 at 14:46
2

Locking 3.6M rows, especially when it is 90% of the table, has multiple burdens on the system. It may "feel like" the entire table is locked.

This sounds like a 1-time fix; perhaps you should simply wait for it to finish -- which could take many minutes.

If you need a better, but more complex, solution, do the UPDATE in chunks of 100-1000 rows. More details: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

  • How to backtrace it in the very loaded system? Is there anyway? – Mohit Kumar Jul 31 '18 at 7:03
  • @MohitKumar - What do you mean by "backtrace"? What it the goal of such? – Rick James Aug 16 '18 at 20:04

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.