mysql 监控长事务

mysql> desc  information_schema.innodb_trx    -> ;+----------------------------+---------------------+------+-----+---------------------+-------+| Field                      | Type                | Null | Key | Default             | Extra |+----------------------------+---------------------+------+-----+---------------------+-------+| trx_id                     | varchar(18)         | NO   |     |                     |       || trx_state                  | varchar(13)         | NO   |     |                     |       || trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       || trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       || trx_wait_started           | datetime            | YES  |     | NULL                |       || trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       || trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       || trx_query                  | varchar(1024)       | YES  |     | NULL                |       || trx_operation_state        | varchar(64)         | YES  |     | NULL                |       || trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       || trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       || trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       || trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       || trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       || trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       || trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       || trx_isolation_level        | varchar(16)         | NO   |     |                     |       || trx_unique_checks          | int(1)              | NO   |     | 0                   |       || trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       || trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       || trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       || trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       || trx_is_read_only           | int(1)              | NO   |     | 0                   |       || trx_autocommit_non_locking | int(1)              | NO   |     | 0                   |       |+----------------------------+---------------------+------+-----+---------------------+-------+24 rows in set (0.00 sec)mysql> select now(),trx_started,(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(trx_started)) from information_schema.innodb_trx;+---------------------+---------------------+-------------------------------------------------------+| now()               | trx_started         | (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(trx_started)) |+---------------------+---------------------+-------------------------------------------------------+| 2016-10-21 09:39:31 | 2016-10-21 09:38:34 |                                                    57 |+---------------------+---------------------+-------------------------------------------------------+1 row in set (0.00 sec)mysql -N -uroot -pnewja01 -e "select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(trx_started)) diff_sec from information_schema.innodb_trx;" | while read A B Cdoecho $Cif [ "$C" -gt 20 ]   then   echo "事务持有时间--$C"fidonemysql> select * from information_schema.innodb_trx\G;*************************** 1. row ***************************                    trx_id: 2438309                 trx_state: RUNNING               trx_started: 2016-10-21 09:58:26     trx_requested_lock_id: NULL          trx_wait_started: NULL                trx_weight: 3       trx_mysql_thread_id: 48                 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: 7         trx_rows_modified: 1   trx_concurrency_tickets: 0       trx_isolation_level: REPEATABLE READ         trx_unique_checks: 1    trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000          trx_is_read_only: 0trx_autocommit_non_locking: 01 row in set (0.00 sec)ERROR: No query specified trx_mysql_thread_id: 48wjdb3:/root# mysql -uroot -p'newja01' -e"show processlist"Warning: Using a password on the command line interface can be insecure.+----+------+-----------+------+---------+------+-------+------------------+| Id | User | Host      | db   | Command | Time | State | Info             |+----+------+-----------+------+---------+------+-------+------------------+| 48 | root | localhost | zjzc | Sleep   |  205 |       | NULL             || 58 | root | localhost | NULL | Sleep   |  141 |       | NULL             || 60 | root | localhost | NULL | Query   |    0 | init  | show processlist |+----+------+-----------+------+---------+------+-------+------------------+wjdb3:/root# mysql> desc PROCESSLIST;+---------+---------------------+------+-----+---------+-------+| Field   | Type                | Null | Key | Default | Extra |+---------+---------------------+------+-----+---------+-------+| ID      | bigint(21) unsigned | NO   |     | 0       |       || USER    | varchar(16)         | NO   |     |         |       || HOST    | varchar(64)         | NO   |     |         |       || DB      | varchar(64)         | YES  |     | NULL    |       || COMMAND | varchar(16)         | NO   |     |         |       || TIME    | int(7)              | NO   |     | 0       |       || STATE   | varchar(64)         | YES  |     | NULL    |       || INFO    | longtext            | YES  |     | NULL    |       |+---------+---------------------+------+-----+---------+-------+8 rows in set (0.00 sec)关联Processlist:TRX_MYSQL_THREAD_ID：MySQL thread ID. To obtain details about the thread, join this column with the ID column of the INFORMATION_SCHEMA PROCESSLIST tableselect 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  bon a.TRX_MYSQL_THREAD_ID=b.id;wjdb3:/root# cat mon_lock.sh mysql -N -uroot -pnewja01 -e "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  bon a.TRX_MYSQL_THREAD_ID=b.id;" | while read A B C D E F Gdoecho $Cif [ "$C" -gt 20 ]   then   echo "processid[$D] $E@$F in db[$G] hold transaction time $C"fidonewjdb3:/root# sh ./mon_lock.sh Warning: Using a password on the command line interface can be insecure.670processid[65] root@192.168.32.26:49153 in db[zjzc] hold transaction time 670