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
    Commented Jul 25, 2018 at 14:46

1 Answer 1


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
    Commented Jul 31, 2018 at 7:03
  • @MohitKumar - What do you mean by "backtrace"? What it the goal of such?
    – Rick James
    Commented Aug 16, 2018 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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