MySQL如何查看未提交的事务SQL

MySQL中经常遇到事务中的SQL正在执行或执行完成后未提交，如何找出对应的SQL？

1. 查看正在执行的SQL

查看事务中正在执行的SQL方式有多种，例如

1.1 通过processlist查看

会话1：执行1个SQL

mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select sleep(20),now() ,id from test1;

会话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 | +----+------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

可以看到正在执行的SQL，包括自己的SQL的id及内容。

1.2 通过events_statements_current查看

会话1：执行1个SQL

mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select sleep(20),now() ,id from test1;

会话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: 36 info: select sleep(20),now() ,id from test1 thread_id: 76 sql_text: select sleep(20),now() ,id from test1 *************************** 2. row *************************** id: 37 info: 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 thread_id: 77 sql_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_id 2 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: 421227264232664 trx_state: RUNNING trx_started: 2023-01-03 22:01:09 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 36 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 1128 trx_rows_locked: 0 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: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 trx_schedule_weight: NULL 1 row in set (0.00 sec)

其中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: 36 info: NULL thread_id: 76 sql_text: select sleep(2),now() ,id from test1 *************************** 2. row *************************** id: 37 info: 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 thread_id: 77 sql_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_id 2 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: 36 info: NULL thread_id: 76 sql_text: select sleep(1),now() ,id from test1 *************************** 2. row *************************** id: 37 info: 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 thread_id: 77 sql_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_id 2 rows in set (0.00 sec)

可见，查到的是最后一个SQL了，如果事务手动commit提交了，则显示的是commit

1. 查看正在执行的SQL

查看事务中正在执行的SQL方式有多种，例如

1.1 通过processlist查看

会话1：执行1个SQL

mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select sleep(20),now() ,id from test1;

会话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 | +----+------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

可以看到正在执行的SQL，包括自己的SQL的id及内容

1.2 通过events_statements_current查看

会话1：执行1个SQL

mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select sleep(20),now() ,id from test1;

会话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: 36 info: select sleep(20),now() ,id from test1 thread_id: 76 sql_text: select sleep(20),now() ,id from test1 *************************** 2. row *************************** id: 37 info: 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 thread_id: 77 sql_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_id 2 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: 421227264232664 trx_state: RUNNING trx_started: 2023-01-03 22:01:09 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 36 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 1128 trx_rows_locked: 0 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: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 trx_schedule_weight: NULL 1 row in set (0.00 sec)

其中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: 36 info: NULL thread_id: 76 sql_text: select sleep(2),now() ,id from test1 *************************** 2. row *************************** id: 37 info: 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 thread_id: 77 sql_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_id 2 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: 36 info: NULL thread_id: 76 sql_text: select sleep(1),now() ,id from test1 *************************** 2. row *************************** id: 37 info: 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 thread_id: 77 sql_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_id 2 rows in set (0.00 sec)

可见，查到的是最后一个SQL了，如果事务手动commit提交了，则显示的是commit。

原文地址：https://www.toutiao.com/article/7198154431853429283/