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

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.

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.