MySQL中经常遇到事务中的SQL正在执行或执行完成后未提交，如何找出对应的SQL？
(资料图片仅供参考)
1. 查看正在执行的SQL
查看事务中正在执行的SQL方式有多种，例如
1.1 通过processlist查看
会话1：执行1个SQL
mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql>selectsleep(20),now(),idfromtest1;
会话2：开启另一个会话，查看对应的SQL
mysql> select id ,info from information_schema.processlist where info is not null;+----+------------------------------------------------------------------------------+| id | info |+----+------------------------------------------------------------------------------+| 36 | select sleep(20),now() ,id from test1 || 37 | select id ,info from information_schema.processlist where info is not null |+----+------------------------------------------------------------------------------+2rowsinset(0.00sec)
可以看到正在执行的SQL，包括自己的SQL的id及内容
1.2通过events_statements_current查看
会话1：执行1个SQL
mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql>selectsleep(20),now(),idfromtest1;
会话2：查看对应的SQL
mysql> select a.id,a.info, b.thread_id, c.sql_text from information_schema.processlist a, performance_schema.threads b, performance_schema.events_statements_current c where a.id=b.processlist_id and b.thread_id = c.thread_id\G*************************** 1. row ***************************id: 36info: select sleep(20),now() ,id from test1thread_id: 76sql_text: select sleep(20),now() ,id from test1*************************** 2. row ***************************id: 37info: select a.id,a.info, b.thread_id, c.sql_text from information_schema.processlist a, performance_schema.threads b, performance_schema.events_statements_current c where a.id=b.processlist_id and b.thread_id = c.thread_idthread_id: 77sql_text: select a.id,a.info, b.thread_id, c.sql_text from information_schema.processlist a, performance_schema.threads b, performance_schema.events_statements_current c where a.id=b.processlist_id and b.thread_id = c.thread_id2 rows in set (0.01 sec)
2.方式对比
通过processlist和通过events_statements_current区别在于，processlist中能查到的SQL是正在运行的SQL，而运行结束的SQL是看不到的。
会话1：执行1个SQL
mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select sleep(2),now() ,id from test1;+----------+---------------------+----+| sleep(2) | now() | id |+----------+---------------------+----+| 0 | 2023-01-03 22:01:09 | 1 |+----------+---------------------+----+1 row in set (2.00 sec)
此时查看事务情况
mysql> select * from information_schema.innodb_trx\G*************************** 1. row ***************************trx_id: 421227264232664trx_state: RUNNINGtrx_started: 2023-01-03 22:01:09trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 0trx_mysql_thread_id: 36trx_query: NULLtrx_operation_state: NULLtrx_tables_in_use: 0trx_tables_locked: 0trx_lock_structs: 0trx_lock_memory_bytes: 1128trx_rows_locked: 0trx_rows_modified: 0trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0trx_schedule_weight: NULL1rowinset(0.00sec)其中trx_mysql_thread_id=36的会话正是我们会话1的线程id,但是我们看不到具体的SQL
mysql> select * from information_schema.processlist where id=36;+----+------+-----------+--------+---------+------+-------+------+| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |+----+------+-----------+--------+---------+------+-------+------+| 36 | root | localhost | testdb | Sleep | 177 | | NULL |+----+------+-----------+--------+---------+------+-------+------+1 row in set (0.00 sec)
但是此时通过方式2就可以查到
mysql> select a.id,a.info, b.thread_id, c.sql_text from information_schema.processlist a, performance_schema.threads b, performance_schema.events_statements_current c where a.id=b.processlist_id and b.thread_id = c.thread_id\G*************************** 1. row ***************************id: 36info: NULLthread_id: 76sql_text: select sleep(2),now() ,id from test1*************************** 2. row ***************************id: 37info: select a.id,a.info, b.thread_id, c.sql_text from information_schema.processlist a, performance_schema.threads b, performance_schema.events_statements_current c where a.id=b.processlist_id and b.thread_id = c.thread_idthread_id: 77sql_text: select a.id,a.info, b.thread_id, c.sql_text from information_schema.processlist a, performance_schema.threads b, performance_schema.events_statements_current c where a.id=b.processlist_id and b.thread_id = c.thread_id2 rows in set (0.00 sec)
注意：此时只能查到一个事务中的多条SQL的最后一个
例如：
mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select sleep(2),now() ,id from test1;+----------+---------------------+----+| sleep(2) | now() | id |+----------+---------------------+----+| 0 | 2023-01-03 22:01:09 | 1 |+----------+---------------------+----+1 row in set (2.00 sec)mysql> select sleep(1),now() ,id from test1;+----------+---------------------+----+| sleep(1) | now() | id |+----------+---------------------+----+| 0 | 2023-01-03 22:06:35 | 1 |+----------+---------------------+----+
会话2查看结果
mysql> select a.id,a.info, b.thread_id, c.sql_text from information_schema.processlist a, performance_schema.threads b, performance_schema.events_statements_current c where a.id=b.processlist_id and b.thread_id = c.thread_id\G*************************** 1. row ***************************id: 36info: NULLthread_id: 76sql_text: select sleep(1),now() ,id from test1*************************** 2. row ***************************id: 37info: select a.id,a.info, b.thread_id, c.sql_text from information_schema.processlist a, performance_schema.threads b, performance_schema.events_statements_current c where a.id=b.processlist_id and b.thread_id = c.thread_idthread_id: 77sql_text: select a.id,a.info, b.thread_id, c.sql_text from information_schema.processlist a, performance_schema.threads b, performance_schema.events_statements_current c where a.id=b.processlist_id and b.thread_id = c.thread_id2rowsinset(0.00sec)
可见，查到的是最后一个SQL了，如果事务手动commit提交了，则显示的是commit
往期精彩回顾1.MySQL高可用之MHA集群部署
2.mysql8.0新增用户及加密规则修改的那些事3. 比hive快10倍的大数据查询利器-- presto4. 监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库
5. PostgreSQL主从复制--物理复制
6.MySQL传统点位复制在线转为GTID模式复制
7.MySQL敏感数据加密及解密
8.MySQL数据备份及还原（一）
9.MySQL数据备份及还原（二）
扫码关注
营业执照公示信息