2

I am using Mysql(INNODB) and does Mysql locks table when I do some insert operation?

I have a huge table and it has lots of indexes. I do some research and Mysql doc says INNODB only locks rows not the entire table but I have indexes and does it still only locks rows? My insert queries nearly takes 36 seconds.

Query_time: 35.829970

1 Answer 1

6

InnoDB will only lock the row. But it will eventually have to update all the indexes; however this is mostly deferred.

Was something else going on while the INSERT was taking 36 seconds? Perhaps a SELECT ... FOR UPDATE? Or even a SELECT can block the INSERT in certain circumstances.

Next time it happens, quickly do SHOW FULL PROCESSLIST to see what else is running. Then do SHOW ENGINE InnoDB STATUS to (hopefully) get some info specific to the INSERT and why it is blocked.

What version are you running? Newer versions have tables to help get more info, even after the fact.

1
  • 1
    Rick, you have saved one life 3 years and half later. Really thank you. :-)
    – JoséMi
    Oct 3, 2019 at 11:50

Your Answer

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

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