[MySQL] MySQL database transactions have been RUNNING in the production environment
Preface:
The operations staff reported that a ticket was submitted and the page was not returned.
1. At first, I suspected that the application server or database was under too high pressure. I checked the load on the application server and the database immediately. It seems to be OK. It is very low and should not be a db performance problem.
2. Check whether the table is locked. Check that two transactions have been RUNNING and are not finished .,
Mysql> select * from INNODB_TRX \ G;
3. Use trx_mysql_thread_id: 1662332 to query information_schema.processlist to find the SQL thread of the client request for transaction execution.
4. Use the SQL thread to find the application IP address and port 10.2xx.3.xx: 23452.
5. Find the project and check the error message of the tomcat application, as shown below:
--- Check the select ID, SHOP_NAME, clerk, LEGAL_PERSON, CORPORATION, SHOP_TEL, ADDRESS, ZIP_CODE, LEGAL_PERSON_MOBILE, STAT, AUTHENTICATE, CREDIT, LEVEL, SCORE, LICENSE_PATH, BUSSINESS_RANGE, MALL_ID, FLOOR, BIS_COMPANY, STORE_NO, MANAGER_NAME, MANAGER_TEL, BRIEF, LOGO, FAVOUR_NUM, HAS_WAREHOUSE, DESCRIPTION_FIT, SERVICE, SPEED, BACKGROUND, BIS_CONT_ID, BIS_SHOP_ID, CREATED_DATE, U PDATED_DATE, ENGLISH_PREF from SHOP where SHOP_NAME =? .
6. Now it is obvious that the transaction has not been committed. check all the java code with the developer and find two places. In Exception e, I forgot to write rollback.
} Catch (SQLException e ){
}
Conclusion: when writing database transactions, developers should remember to forget the rollback when handling exceptions.