MySQL 5.7 thread blocking processing

The error that occurred:

ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction

Workaround: View the process of sleep

Mysql> Show full processlist;+-------+----------+-------------------+----------+---------+-------+----------+- ----------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+-------+----------+-------------------+----- -----+---------+-------+----------+---------------------------------------------------------------------------- -+| 32579 | Official | 127.0.0.1:42690 | Official | Sleep |          20926 | | NULL | | 32715 | ERP | 127.0.0.1:2078 | NULL |  Sleep |          4570 | | NULL | | 32716 | ERP | 127.0.0.1:2079 | ERP |  Sleep |          3590 | | NULL | | 32717 | ERP | 127.0.0.1:2080 | ERP |  Sleep |          3577 | | NULL | | 32732 | ERP | 127.0.0.1:2081 | ERP |  Sleep |          3590 | | NULL | | 32733 | ERP | 127.0.0.1:2082 | NULL |  Sleep |          1412 | | NULL | | 32734 | ERP | 127.0.0.1:2083 | ERP |  Sleep |          1160 | | NULL | | 32735 | ERP | 127.0.0.1:2084 | ERP |   Sleep |          985 | | NULL | | 32736 | ERP | 127.0.0.1:2085 | ERP |  Sleep |          1160 | | NULL | | 32739 | ERP | 127.0.0.1:2088 | ERP |   Sleep |   695 |       | NULL | | 32740 | ERP | 127.0.0.1:2089 | ERP |   Sleep |          768 | | NULL | | 32741 | ERP | 127.0.0.1:2090 | ERP |   Sleep |          688 | | NULL | | 32742 | ERP | 127.0.0.1:2091 | ERP |   Sleep |          686 | | NULL | | 32743 | ERP | 127.0.0.1:2092 | ERP |   Sleep |          687 | | NULL | | 32745 | ERP | 127.0.0.1:2093 | ERP |   Sleep |          686 | | NULL | | 32746 | ERP | 127.0.0.1:2094 | ERP |   Sleep |          686 | |                                       Null                                 || 32763 | Root | localhost | ERP |     Query | 0 | Starting | Show Full Processlist | | 32765 | ERP | 127.0.0.1:58216 | ERP |    Sleep |          18 | | NULL | | 32766 | ERP | 127.0.0.1:58218 | ERP |    Sleep |          41 | | NULL | | 32767 | ERP | 127.0.0.1:58220 | ERP |    Sleep |          41 | | NULL | | 32768 | ERP | 127.0.0.1:58222 | ERP |    Sleep |          41 | | NULL | | 32769 | ERP | 127.0.0.1:58224 | ERP |     Sleep |          2 | | NULL | | 32770 |     Erp | 127.0.0.1:2106 | NULL |   Sleep |          153 | | NULL | | 32771 | ERP | 127.0.0.1:2107 | ERP |   Sleep |          148 | | NULL | | 32772 | ERP | 127.0.0.1:2108 | ERP |    Query | 18 | Updating | UPDATE ' erp_user ' SET ' pwd ' = ' oyuhgt21gtp2/5ufgbkztq== ' WHERE (' id ' = ' 10113 ') |+-------+----------+------------------- +----------+---------+-------+----------+---------------------------------------------------------------------- -------+

Do not see the slow SQL record thread being executed, then go to the InnoDB transaction table Innodb_trx, see if there is a transaction thread that is being locked, and see if the ID is in the sleep thread in show full processlist, if so, It proves that the thread transaction of sleep is stuck without commit or rollback, and we need to kill it manually.

  mysql> SELECT * from INFORMATION_SCHEMA. innodb_trx\g;*************************** 1. Row *************************** trx_id:20342 trx_state:running Trx_star       ted:2018-01-29 16:48:13 trx_requested_lock_id:null trx_wait_started:null trx_weight:4         trx_mysql_thread_id:32735 trx_query:null Trx_operation_state:null trx_tables_in_use:0         Trx_tables_locked:1 trx_lock_structs:2 trx_lock_memory_bytes:1136 trx_rows_locked:1 Trx_rows_modified:2 trx_concurrency_tickets:0 trx_isolation_level:repeatable READ Trx_unique_check S:1 Trx_foreign_key_checks:1trx_last_foreign_key_error:null trx_adaptive_hash_latched:0 Trx_adaptive_hash_timeout : 10000 trx_is_read_only:0trx_autocommit_non_locking:01 row in Set (0.00 sec) Error:no query specified  pre>

See this 32735 sql,kill drop, execute kill 32735;

kill 32735;

Then go to query the Innodb_trx table, there will be no blocking transaction sleep thread exists, as follows:

mysql> SELECT * FROM information_schema.INNODB_TRX\G;Empty set (0.00 sec)

SQL statements can now be executed normally.

MySQL 5.7 thread blocking processing