Does MySQL lock full table in case of update query on specific rows?

Question

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.

• mustaccio: You don't need to lock the table in order to block a query; a single row is enough.

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

• mohit: How to backtrace it in the very loaded system? Is there anyway?
• Rick James: @MohitKumar - What do you mean by "backtrace"? What it the goal of such?