mysql 5.1 innodb trx_INFORMATION_SCHEMA.INNODB_TRX 详解

从192.168.11.186 上登录 192.168.11.185 数据库:

root 13246 547 0 13:39 pts/1 00:00:00 mysql -uroot -px xxxxxxx -h192.168.11.185

进程号 13246

zabbix:/root/mysql# netstat -nap |grep 3306 | grep 185

tcp 0 0 192.168.11.186:40366 192.168.11.185:3306 ESTABLISHED 13246/mysql

从192.168.11.185 上查看登录的线程号:

centos6.5:/root#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"show processlist"

Warning: Using a password on the command line interface can be insecure.

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 1367 | root | 192.168.11.186:40366 | NULL | Sleep | 78 | | NULL |

线程号为1367

zabbix:/root/mysql# sh ./mon_mysql_all.sh

Warning: Using a password on the command line interface can be insecure.

Warning: Using a password on the command line interface can be insecure.

mysql[192.168.11.185] processid[1367] root@192.168.11.186:40366 in db[DEVOPS] hold transaction time 141

centos6.5:/root#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"select * from INFORMATION_SCHEMA.INNODB_TRX\G "

Warning: Using a password on the command line interface can be insecure.

*************************** 1. row ***************************

trx_id: 5451

trx_state: RUNNING

trx_started: 2016-11-22 13:48:24

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 3

trx_mysql_thread_id: 1367

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 2

trx_lock_memory_bytes: 360

trx_rows_locked: 4

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: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

INNODB_TRX 表包含信息关于每个事务(不包含只读事务) 当前在InnoDB内执行的,

包含交易是否是等待一个lock,当 事务开始后,和SQL 语句正在执行的

INNODB_TRX Columns:

TRX_ID：唯一的事务ID表示,针对于InnoDB内部的(从MySQL5.6开始,那些IDs是不会被创建用于事务 只读的或者是非锁定的)

TRX_WEIGHT：一个事务的权重,反映(但不一定是确切的计数) 影响的行的数量,和被事务锁定的行的数量。

为了解决死锁,InnoDB 选择一个小的权重的事务来回滚。

TRX_STATE 事务执行状态,允许值为RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.

TRX_STARTED： 事务开始时间

centos6.5:/root#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"show processlist"

Warning: Using a password on the command line interface can be insecure.

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 1367 | root | 192.168.11.186:40366 | DEVOPS | Sleep | 46 | | NULL |

| 1404 | root | 192.168.11.186:46149 | DEVOPS | Query | 3 | updating | delete from test where username='admin' |

| 1405 | root | 192.168.11.185:43080 | NULL | Query | 0 | init | show processlist |

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

1367 持有行锁

1404 被堵塞

centos6.5:/root#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"select * from INFORMATION_SCHEMA.INNODB_TRX\G "

Warning: Using a password on the command line interface can be insecure.

*************************** 1. row ***************************

trx_id: 5458

trx_state: LOCK WAIT

trx_started: 2016-11-22 14:01:57

trx_requested_lock_id: 5458:14:3:2

trx_wait_started: 2016-11-22 14:01:57

trx_weight: 2

trx_mysql_thread_id: 1404

trx_query: delete from test where username='admin'

trx_operation_state: starting index read

trx_tables_in_use: 1

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 360

trx_rows_locked: 1

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

*************************** 2. row ***************************

trx_id: 5453

trx_state: RUNNING

trx_started: 2016-11-22 14:01:14

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 3

trx_mysql_thread_id: 1367

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 2

trx_lock_memory_bytes: 360

trx_rows_locked: 4

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: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

TRX_REQUESTED_LOCK_ID: 持有者为trx_requested_lock_id: NULL

被堵塞者trx_requested_lock_id: 5458:14:3:2

事务当前等待的锁的ID,如果TRX_STATE 是LOCK WAIT; 否则值为NULL。

得到 lock的详细信息,关联这个列和 INNODB_LOCKS 表的LOCK_ID列

SELECT

NOW(),

(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,

b.id,

b.user,

b.host,

b.db,

c.lock_type,

c.lock_table,

c.lock_index

FROM

information_schema.innodb_trx a

INNER JOIN

information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id

INNER JOIN

information_schema.INNODB_LOCKS c

on a.trx_requested_lock_id=c.lock_id;

INFORMATION_SCHEMA.INNODB_LOCKS 这个表只有在堵塞的时候才有数据

TRX_WAIT_STARTED： 事务开始等待锁的时间,只有是TRX_STATE 是LOCK WAIT; 否则为空

TRX_MYSQL_THREAD_ID: MySQL 事务ID 得到thread 的详细信息,关联这个列和INFORMATION_SCHEMA PROCESSLIST table的ID列。

SELECT

NOW(),

(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,

b.id,

b.user,

b.host,

b.db

FROM

information_schema.innodb_trx a

INNER JOIN

information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id;

TRX_QUERY 语句当前事务执行的

TRX_OPERATION_STATE: 事务的当前的操作,如果any 否则NULL

TRX_TABLES_IN_USE InnoDB tables 的数量当前用于处理SQL语句

TRX_TABLES_LOCKED: InnoDB 表的数量