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 C
do
echo $C
if [ "$C" -gt 20 ]
then
echo "事务持有时间--$C"
fi
done mysql> 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: 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 trx_mysql_thread_id: 48 wjdb3:/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 table 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; 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 b
on a.TRX_MYSQL_THREAD_ID=b.id;" | while read A B C D E F G
do
echo $C
if [ "$C" -gt 20 ]
then
echo "processid[$D] $E@$F in db[$G] hold transaction time $C"
fi
done
wjdb3:/root# sh ./mon_lock.sh
Warning: Using a password on the command line interface can be insecure.
670
processid[65] root@192.168.32.26:49153 in db[zjzc] hold transaction time 670