select * from INFORMATION_SCHEMA.INNODB_TRX\G
The INNODB_TRX table contains information about every transaction (excluding read-only transactions) currently executing inside InnoDB,
including whether the transaction is waiting for a lock, when the transaction started, and the SQL statement the transaction is executing, if any.
INNODB_TRX 表包含 信息关于每个事务(排除只读事务)当前执行的在InnoDB中,
包含事务是否是等待一个锁, 当事务启动时
1. 开始事务,运行一个短查询:
开启一个事务 运行一个查询：
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 2 |
+-----------------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t200;
ERROR 1046 (3D000): No database selected
mysql> select * from zjzc.t200;
+-----+---------+-------------+--------+
| sn | phoneNo | channelType | status |
+-----+---------+-------------+--------+
| 1 | 1 | 2 | 1 |
| 2 | 2 | 2 | 2 |
| 3 | 3 | 2 | 3 |
| 4 | 4 | 2 | 4 |
| 5 | 5 | 2 | 5 |
| 6 | 6 | 2 | 6 |
| 7 | 7 | 2 | 7 |
| 8 | 8 | 2 | 8 |
| 9 | 9 | 2 | 9 |
| 10 | 10 | 2 | 10 |
| 11 | 11 | 2 | 11 |
| 12 | 12 | 2 | 12 |
| 13 | 13 | 2 | 13 |
| 14 | 14 | 2 | 14 |
| 15 | 15 | 2 | 15 |
| 16 | 16 | 2 | 16 |
| 17 | 17 | 2 | 17 |
| 18 | 18 | 2 | 18 |
| 19 | 19 | 2 | 19 |
| 20 | 20 | 2 | 20 |
| 21 | 21 | 2 | 21 |
| 22 | 22 | 2 | 22 |
| 23 | 23 | 2 | 23 |
| 24 | 24 | 2 | 24 |
| 25 | 25 | 2 | 25 |
| 26 | 26 | 2 | 26 |
| 27 | 27 | 2 | 27 |
| 28 | 28 | 2 | 28 |
| 29 | 29 | 2 | 29 |
| 30 | 30 | 2 | 30 |
| 31 | 31 | 4 | 31 |
| 34 | 34 | 3 | 34 |
| 39 | 39 | 3 | 39 |
| 41 | 41 | 1 | 41 |
| 46 | 46 | 1 | 46 |
| 100 | 1 | 1 | 1 |
| 101 | 1 | 1 | 1 |
+-----+---------+-------------+--------+
37 rows in set (0.00 sec)
此时会出现事务:
mysql> select * from INFORMATION_SCHEMA.INNODB_TRX\G;
*************************** 1. row ***************************
trx_id: 389256466
trx_state: RUNNING
trx_started: 2016-12-19 12:39:40
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 2
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 360
trx_rows_locked: 0
trx_rows_modified: 0
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: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
ERROR:
No query specified
zabbix:/root/mysql# sh ./mon_mysql_all.sh
,,,,,
2016-12-19 12:43:00,200,2,root,localhost,
mysql[192.168.11.187] processid[2] root@localhost in db[] hold transaction time 200
会出现事务
2.不开启事务,直接运行大的查询
mysql> select * from ClientActionTrack20151125;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: zjzc
mysql> select * from INFORMATION_SCHEMA.INNODB_TRX\G;
*************************** 1. row ***************************
trx_id: 389259097
trx_state: RUNNING
trx_started: 2016-12-19 12:50:55
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 3
trx_query: select * from ClientActionTrack20151125
trx_operation_state: fetching rows
trx_tables_in_use: 1
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 360
trx_rows_locked: 0
trx_rows_modified: 0
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: 10000
trx_is_read_only: 1
trx_autocommit_non_locking: 1
1 row in set (0.11 sec)
ERROR:
No query specified
