mysql 事务 rr_mysql rr 查询出现的事务情况

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