Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
