Shulou(Shulou.com)05/31 Report--

MySQL row lock super how to troubleshoot, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

1. Outline # 20191219 10 ERROR 10 com.alibaba.druid.filter.logging.Log4jFilter.statementLogError (Log4jFilter.java:152) | {conn-10593, pstmt-38675} execute error. Update xxx set xxx =?, xxx =? Where RowGuid =? Com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

Previously, in the article "how to effectively troubleshoot and solve the problem of MySQL row lock waiting timeout", we introduced how to monitor and solve the row lock timeout error. At that time, the monitoring scheme introduced at that time was mainly to use shell script + general_log to capture row lock waiting information, which later felt troublesome, so after optimization, it was changed to use the method of Event + Procedure to execute regularly in MySQl, and record the row lock waiting information in the log table. And add the transaction context information in the pfs table, which can save the process of logging in the server to execute scripts and analyze general_log, which is more convenient.

Because the system tables under Event and performance_schema are used, you need to open the configuration of both. Pfs can use the default monitoring items. Here, the events_statements_history table is mainly used, and the session 10 SQL are retained by default.

Performance_schema = on event_scheduler = 1. Step 2

At present, this method has only been used in MySQL version 5.7, and MySQL 8.0 has not been tested.

Create database `innodb_ monitor`

Create database `innodb_ monitor`

2.2 create a stored procedure

Use innodb_monitor; delimiter;; CREATE PROCEDURE pro_innodb_lock_wait_check () BEGIN declare wait_rows int; set group_concat_max_len = 1024000 CREATE TABLE IF NOT EXISTS `innodb_lock_wait_ log` (`report_ time` datetime DEFAULT NULL, `blocking_ id` int (11) DEFAULT NULL, `blocking_ id` int (11) DEFAULT NULL, `duration` varchar (50) DEFAULT NULL, `state`varchar (50) DEFAULT NULL, `waiting_ query` longtext DEFAULT NULL, `blocking_current_ query` longtext DEFAULT NULL, `blocking_thd_last_ query`longtext, `thread_ id` int (11) DEFAULT NULL); select count (*) into wait_rows from information_schema.innodb_lock_waits If wait_rows > 0 THEN insert into `innodb_lock_wait_ log` SELECT now (), r.trx_mysql_thread_id waiting_id,b.trx_mysql_thread_id blocking_id,concat (timestampdiff (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP ()),'s') AS duration, t.processlist_command state,r.trx_query waiting_query,b.trx_query blocking_current_query,group_concat (left (h.sql_text) 10000) order by h.TIMER_START DESC SEPARATOR' \ n') As blocking_thd_query_history,thread_id FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id LEFT JOIN performance_schema.threads t on t.processlist_id = b.trx_mysql_thread_id LEFT JOIN performance_schema.events_statements_history h USING (thread_id) group by thread_id R.trx_id order by r.trx_wait_started End if; END

2.3 create event

The event is executed every 5 seconds (usually equal to the value of innodb_lock_wait_timeout) and is monitored continuously for 7 days. After that, the event is automatically deleted, or the retention period can be customized.

Use innodb_monitor; delimiter;; CREATE EVENT `event_innodb_lock_wait_ check` ON SCHEDULE EVERY 5 SECOND STARTS CURRENT_TIMESTAMP ENDS CURRENT_TIMESTAMP + INTERVAL 7 DAY ON COMPLETION NOT PRESERVE ENABLE DO call pro_innodb_lock_wait_check ();

2.4 event start and stop

-- 1: global enable event; 0: global shutdown mysql > SET GLOBAL event_scheduler = 1;-- temporary shutdown event mysql > ALTER EVENT event_innodb_lock_wait_check DISABLE;-- shutdown enable event mysql > ALTER EVENT event_innodb_lock_wait_check ENABLE;. 3. Log table.

Then the innodb_lock_wait_log table is analyzed according to the error time of the application log and SQL. There are two main scenarios:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

The blocking_current_query is not empty, which means that the blocking transaction is running. At this point, you need to analyze whether there are performance problems in the current running SQL.

If blocking_current_query is empty and state is Sleep, the blocking transaction is in a suspended state, that is, SQL is no longer running. In this case, you need to analyze the transaction context by analyzing blocking_thd_last_query. Note that the SQL in this column is in descending order of time, that is, it is executed from bottom to top.

This is the answer to the question about how to troubleshoot the MySQL line lock. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.