Session 1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update ClientActionTrack20151125 set ip='2.2.2.2';
Warning: Using a password on the command line interface can be insecure.
+-----+------+-----------+------+---------+------+----------+---------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------+---------+------+----------+---------------------------------------------------+
| 447 | root | localhost | zjzc | Query | 5 | updating | update ClientActionTrack20151125 set ip='2.2.2.2' |
2016-11-25 17:40:26,39,447,root,localhost,zjzc
mysql[192.168.11.187] processid[447] root@localhost in db[zjzc] hold transaction time 39
mysql> select * from information_schema.innodb_trxG;
*************************** 1. row ***************************
trx_id: 112075119
trx_state: RUNNING
trx_started: 2016-11-25 17:39:47
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 6115055
trx_mysql_thread_id: 447
my $hostSql = qq{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};
show processlist 看到的ID 447 就是 a.TRX_MYSQL_THREAD_ID = b.id
开启session 2:
Database changed
mysql> update ClientActionTrack20151125 set ip='9.9.9.9';
Vsftp:/root# mysql -uroot -p1234567 -e"show processlist"
Warning: Using a password on the command line interface can be insecure.
+-----+------+----------------------+--------------------+---------+------+----------+---------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+----------------------+--------------------+---------+------+----------+---------------------------------------------------+
| 447 | root | localhost | zjzc | Query | 197 | updating | update ClientActionTrack20151125 set ip='2.2.2.2' |
| 454 | root | localhost | zjzc | Query | 3 | updating | update ClientActionTrack20151125 set ip='9.9.9.9' |
| 457 | root | 192.168.11.186:47208 | information_schema | Sleep | 1 | | NULL |
| 458 | root | localhost | NULL | Query | 0 | init | show processlist |
+-----+------+----------------------+--------------------+---------+------+----------+---------------------------------------------------+
2016-11-25 17:43:39,232,447,root,localhost,zjzc
mysql[192.168.11.187] processid[447] root@localhost in db[zjzc] hold transaction time 232
2016-11-25 17:43:39,38,454,root,localhost,zjzc
mysql[192.168.11.187] processid[454] root@localhost in db[zjzc] hold transaction time 38
112075120,454,update ClientActionTrack20151125 set ip='9.9.9.9',112075119,447,update ClientActionTrack20151125 set ip='2.2.2.2'
mysql[192.168.11.187] blocking_thread[447] blocking_query[update ClientActionTrack20151125 set ip='2.2.2.2'] blocking waiting_thread[454]'s update ClientActionTrack20151125 set ip='9.9.9.9'
取的是 information_schema.innodb_trx:
r.trx_mysql_thread_id waiting_thread,
b.trx_mysql_thread_id blocking_thread,
mysql> select * from information_schema.innodb_trxG;
*************************** 1. row ***************************
trx_id: 112075121
trx_state: LOCK WAIT
trx_started: 2016-11-25 17:45:27
trx_requested_lock_id: 112075121:442:4:149
trx_wait_started: 2016-11-25 17:45:27
trx_weight: 2
trx_mysql_thread_id: 454
trx_query: update ClientActionTrack20151125 set ip='9.9.9.9'
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: 112075119
trx_state: RUNNING
trx_started: 2016-11-25 17:39:47
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 19816885
trx_mysql_thread_id: 447
trx_query: update ClientActionTrack20151125 set ip='2.2.2.2'
trx_operation_state: updating or deleting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 638128
trx_lock_memory_bytes: 56555048
trx_rows_locked: 19816884
trx_rows_modified: 19178758
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 rows in set (0.13 sec)
ERROR:
No query specified