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_trx\G;

*************************** 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_trx\G;

*************************** 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