mysql事務未提交導致鎖等待如何解決

1、實驗環境

Myql版本5.7.17-log

實驗表結構

(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)

插入數據

(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);

2、鎖產生步驟

會話一：開啟事務，更新數據，不提交

(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

查看當前連接id號（線程id號）

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

會話二：開啟另一個事務，更新同一行數據，

(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

執行update test set操作時，會卡在那邊，不執行，經過50秒后，會報錯；

（上面的卡住現象，是由于鎖，可以通過查看表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)

查看當前連接id號（線程id號）

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

以上說的50秒，是系統參數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 如何查看未提交的事務

方法一：

(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: 1
trx_last_foreign_key_error:NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking:0
1 row inset (0.00 sec)

通過以上可看出線程id為4 一直未提交，事務開始的時間為2017-05-08 15:08:07。

方法二：通過 show engine innodb status\G

其中有一段關于事務的描述

TRANSACTIONS
------------
Trx idcounter 756998
Purgedone for trx's n:o < 0 undo n:o < 0 state: running but idle
Historylist length 0
LIST OFTRANSACTIONS FOR EACH SESSION:
---TRANSACTION421519065333360, not started
0 lockstruct(s), heap size 1136, 0 row lock(s)
---TRANSACTION421519065332448, not started
0 lockstruct(s), heap size 1136, 0 row lock(s)
---TRANSACTION756996, ACTIVE 914 sec
2 lockstruct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL 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)