How to resolve the lock waiting caused by the uncommitted mysql transaction

1. Experimental environment

Myql version 5.7.17-log

table structure

(root@localhost)[apex]> show create table test;+-------+-----------------------------------------------------------------------------------------------------------------------------------+| Table| Create Table |+-------+-----------------------------------------------------------------------------------------------------------------------------------+|test | CREATE TABLE `test` ( `x` int(11) NOT NULL, `y` int(11) DEFAULT NULL, PRIMARY KEY (`x`))ENGINE=InnoDB DEFAULT CHARSET=gbk |+-------+-----------------------------------------------------------------------------------------------------------------------------------+1 row inset (0.01 sec)

insert data

(root@localhost)[apex]> insert into test values(1,1);(root@localhost)[apex]> insert into test values(2,2);(root@localhost)[apex]> insert into test values(3,3);

Session 1: Open transaction, update data, do not commit

(root@localhost)[apex]> begin;QueryOK, 0 rows affected (0.00 sec)(root@localhost)[apex]> update test set y=y+1 where x=1;QueryOK, 1 row affected (0.00 sec)Rowsmatched: 1 Changed: 1 Warnings: 0

View current connection id (thread id)

(root@localhost)[apex]> select connection_id();+-----------------+|connection_id() |+-----------------+| 4 |+-----------------+1 row inset (0.00 sec)

Session 2: Start another transaction, update the same row of data,

(root@localhost)[apex]> begin;QueryOK, 0 rows affected (0.00 sec) (root@localhost)[apex]> update test set y=y+1 where x=1;ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When executing update test set operation, it will be stuck there. If it is not executed, an error will be reported after 50 seconds;

(The above stuck phenomenon is due to the lock. You can obtain the lock status by looking at the table information_schema.innodb_lock)

(root@localhost)[information_schema]> select * from information_schema.innodb_locks;+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+|lock_id | lock_trx_id | lock_mode| lock_type | lock_table | lock_index| lock_space | lock_page | lock_rec | lock_data |+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+|757082:3279:3:2 | 757082 | X | RECORD | `apex`.` test` | PRIMARY | 3279 | 3 | 2 | 1 ||757081:3279:3:2 | 757081 | X | RECORD | `apex`.` test` | PRIMARY | 3279 | 3 | 2 | 1 |+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+2 rowsin set, 1 warning (0.00 sec)

View current connection id (thread id)

(root@localhost) [apex]> selectconnection_id();+-----------------+|connection_id() |+-----------------+| 5 |+-----------------+1 row inset (0.00 sec)

The 50 seconds mentioned above are determined by the system parameter innodb_lock_wait_timeout.

(root@localhost)[apex]> show variables like 'innodb_lock_wait_timeout';+--------------------------+-------+|Variable_name | Value |+--------------------------+-------+| innodb_lock_wait_timeout| 50 |+------------------------------+------+1 row inset (0.00 sec)3. mysql How to view uncommitted transactions

Method 1:

(root@localhost)[performance_schema]> SELECT * FROMinformation_schema.INNODB_TRX\G***************************1. row *************************** trx_id: 756996 trx_state: RUNNING trx_started: 2017-05-08 15:08:07 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql_thread_id: 4 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: 1 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1trx_last_foreign_key_error:NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0trx_autocommit_non_locking:01 row inset (0.00 sec)

It can be seen from the above that thread id 4 has not been committed, and the transaction start time is 2017-05-08 15:08:07.

Method 2: Show engine innodb status\G

There's a paragraph describing the transaction.

TRANSACTIONS------------Trx idcounter 756998Purgedone for trx's n:o

< 0 undo n:o < 0 state: running but idleHistorylist length 0LIST OFTRANSACTIONS FOR EACH SESSION:---TRANSACTION421519065333360, not started0 lockstruct(s), heap size 1136, 0 row lock(s)---TRANSACTION421519065332448, not started0 lockstruct(s), heap size 1136, 0 row lock(s)---TRANSACTION756996, ACTIVE 914 sec2 lockstruct(s), heap size 1136, 1 row lock(s), undo log entries 1MySQL thread id 4, OS thread handle 140041791522560, query id25 localhost root 从以上也可以看出线程id号为4的事务一直未提交。 4、如何解决未提交的事务 方法一：如果能知道哪个用户在执行这个操作，让他提交一下（这种可能性很小） 方法二：kill掉这个线程id号，让事务回滚， (root@localhost)[information_schema]>

show processlist;+----+-----------------+------------------+--------------------+---------+------+------------------------+------------------+| Id |User | Host | db | Command | Time | State | Info |+----+-----------------+------------------+--------------------+---------+------+------------------------+------------------+| 1 | event_scheduler | localhost | NULL | Daemon | 4469 | Waiting on empty queue | NULL || 4 | root | localhost | apex | Sleep | 871| | NULL || 5 | root | localhost | apex | Sleep | 82| | NULL || 6 | root | localhost | information_schema | Query | 0| starting | showprocesslist || 7 | root | 192.168.1.1:3708 | NULL | Sleep | 3221 | | NULL |+----+-----------------+------------------+--------------------+---------+------+------------------------+------------------+5 rowsin set (0.00 sec) (root@localhost)[information_schema]> kill 4;QueryOK, 0 rows affected (0.01 sec)