本文共 5659 字，大约阅读时间需要 18 分钟。
Myql版本5.7.17-log
实验表结构
| 1 2 3 4 5 6 7 8 9 10 11 | (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) |
插入数据
| 1 2 3 | (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); |
会话一：开启事务，更新数据，不提交
| 1 2 3 4 5 | (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号）
| 1 2 3 4 5 6 7 | (root@localhost)[apex]> select connection_id(); + -----------------+ |connection_id() | + -----------------+ | 4 | + -----------------+ 1 row inset (0.00 sec) |
会话二：开启另一个事务，更新同一行数据，
| 1 2 3 4 5 | (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，获取锁的状态）
| 1 2 3 4 5 6 7 8 | (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号）
| 1 2 3 4 5 6 7 | (root@localhost) [apex]> selectconnection_id(); + -----------------+ |connection_id() | + -----------------+ | 5 | + -----------------+ 1 row inset (0.00 sec) |
以上说的50秒，是系统参数innodb_lock_wait_timeout决定的
| 1 2 3 4 5 6 7 | (root@localhost)[apex]> show variables like 'innodb_lock_wait_timeout' ; + --------------------------+-------+ |Variable_name | Value | + --------------------------+-------+ | innodb_lock_wait_timeout| 50 | + --------------------------+-------+ 1 row inset (0.00 sec) |
方法一：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | (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
其中有一段关于事务的描述
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | 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的事务一直未提交。
方法一：如果能知道哪个用户在执行这个操作，让他提交一下（这种可能性很小）
方法二：kill掉这个线程id号，让事务回滚，
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | (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) |
本文转自 corasql 51CTO博客，原文链接：http://blog.51cto.com/corasql/1923427，如需转载请自行联系原作者