mysql老是出现锁等待_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 in set (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;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [apex]> update test set y=y+1 where x=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

查看当前连接id号(线程id号)

(root@localhost) [apex]> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|               4 |

+-----------------+

1 row in set (0.00 sec)

会话二：开启另一个事务，更新同一行数据，

(root@localhost) [apex]> begin;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [apex]> update test set y=y+1 where x=1;

ERROR 1205 (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 rows in set, 1 warning (0.00 sec)

查看当前连接id号(线程id号)

(root@localhost) [apex]> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|               5 |

+-----------------+

1 row in set (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 in set (0.00 sec)

3、mysql如何查看未提交的事务

方法一：

(root@localhost) [performance_schema]>  SELECT * FROM information_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 in set (0.00 sec)

通过以上可看出线程id为4 一直未提交，事务开始的时间为2017-05-08 15:08:07。

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

其中有一段关于事务的描述

TRANSACTIONS

------------

Trx id counter 756998

Purge done for trx's n:o 

History list length 0

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 421519065333360, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421519065332448, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 756996, ACTIVE 914 sec

2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 4, OS thread handle 140041791522560, query id 25 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               | show processlist |

|  7 | root            | 192.168.1.1:3708 | NULL               | Sleep   | 3221 |                        | NULL             |

+----+-----------------+------------------+--------------------+---------+------+------------------------+------------------+

5 rows in set (0.00 sec)

(root@localhost) [information_schema]> kill 4;

Query OK, 0 rows affected (0.01 sec)