0

We are using MYSQL INNODB,After inserted one record in table,Locked database operations resulted in proxy and application threads to increase that ultimately caused the proxy server to not accept connections.I would like to know the root cause for this tables locking?

migrated from serverfault.com Sep 7 at 1:54

This question came from our site for system and network administrators.

  • Don't use autocommit = OFF; one tends to forget to COMMIT. – Rick James Sep 7 at 2:41
  • Thanks for your info Rick James,but the insertion was success but after refreshing that page server got down and came to know that corresponding table got locked.then we start kill the processes even though servers not up,after rebooting the RDS ,servers got up.as per your answer if commit is the issue insertions wont be succeeded i think,so please help me to find the root cause. – prawin Sep 9 at 9:01
  • we used <li><strong>defaultAutoCommit</strong> - true or false: default auto-commit state of the connections created by this pool. Default: true</li> – prawin Sep 9 at 9:03
  • true == ON == 1; false == off = 0. (OK, my guess was not on target.) – Rick James Sep 9 at 22:18
0

In my personal opinion debugging table locks in MySQL can be a pain in the *ss sometimes. Some queries that may give you some insights are:

Get a list of the locked tables

SHOW OPEN TABLES WHERE In_Use > 0;

Get a list of all processes using MySQL resources with corresponding queries

SHOW FULL PROCESSLIST;

Get specific information about the InnoDB storage engine including deadlocks

SHOW ENGINE INNODB STATUS;
  • we did all above mentioned steps and finally reboot the RDS after that servers up. but i would like to know the root cause for this ,why tables locked ?i tried a lot to find this but no luck yet – prawin Sep 6 at 13:22

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.