MySQL的 performance_schema.threads 和操作系统的线程 ID

在和同事讨论 systemtap 的时候， 提到 performance_schema 里的 threads 表，只要 client 连接上 server之后，在该 session 里面执行的所有 SQL 都有共同的线程 ID:

mysql> select processlist_id,thread_os_id from performance_schema.threads where processlist_info like 'select process%';
+----------------+--------------+
| processlist_id | thread_os_id |
+----------------+--------------+
|      197357865 |         4923 |
+----------------+--------------+
1 row in set (0.00 sec)
mysql> 
然后在数据库服务器端，执行 ps -eLf 即可看到 ID 为  4923  的 mysqld 线程

---

当前运行的事物：

SELECT it.trx_id,
          psh.sql_text,
          pt.thread_id,
          ptc.event_id AS trx_event_id,
          psh.event_id,
          psh.rows_sent
 FROM information_schema.innodb_trx it
 JOIN performance_schema.threads pt
     ON it.trx_mysql_thread_id = pt.processlist_id
 JOIN performance_schema.events_statements_history psh
     ON pt.thread_id = psh.thread_id
 JOIN performance_schema.events_transactions_current ptc
     ON psh.THREAD_ID = ptc.THREAD_ID
         AND (psh.NESTING_EVENT_ID = ptc.EVENT_ID
         OR psh.END_EVENT_ID >= ptc.EVENT_ID); 

检查锁：

SELECT t.trx_mysql_thread_id
FROM performance_schema.data_locks d, information_schema.innodb_trx t
WHERE d.lock_type in ('RECORD', 'TABLE')
    AND t.trx_id = d.ENGINE_TRANSACTION_ID;