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?
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;