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?

Improve this question
4
  • Don't use autocommit = OFF; one tends to forget to COMMIT.
    – Rick James
    Sep 7, 2019 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, 2019 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, 2019 at 9:03
  • true == ON == 1; false == off = 0. (OK, my guess was not on target.)
    – Rick James
    Sep 9, 2019 at 22:18

1 Answer 1

Reset to default
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;
Improve this answer
1
  • 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, 2019 at 13:22

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.