MySQL中经常遇到事务中的SQL正在执行或执行完成后未提交，如何找出对应的SQL？
1. 查看正在执行的SQL
查看事务中正在执行的SQL方式有多种，例如
1.1 通过processlist查看
会话1：执行1个SQL
mysql>begin;
QueryOK, 0rowsaffected (0.00sec)
mysql>selectsleep(20),now() ,idfromtest1;
会话2：开启另一个会话，查看对应的SQL
mysql>selectid ,infofrominformation_schema.processlistwhereinfoisnotnull;
+----+------------------------------------------------------------------------------+
|id|info|
+----+------------------------------------------------------------------------------+
|36|selectsleep(20),now() ,idfromtest1|
|37|selectid ,infofrominformation_schema.processlistwhereinfoisnotnull|
+----+------------------------------------------------------------------------------+
2rowsinset (0.00sec)
可以看到正在执行的SQL，包括自己的SQL的id及内容。
1.2 通过events_statements_current查看
会话1：执行1个SQL
mysql>begin;
QueryOK, 0rowsaffected (0.00sec)
mysql>selectsleep(20),now() ,idfromtest1;
会话2：查看对应的SQL
mysql>selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id\G
***************************1.row***************************
id: 36
info: selectsleep(20),now() ,idfromtest1
thread_id: 76
sql_text: selectsleep(20),now() ,idfromtest1
***************************2.row***************************
id: 37
info: selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id
thread_id: 77
sql_text: selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id
2rowsinset (0.01sec)
2. 方式对比
通过processlist和通过events_statements_current区别在于，processlist中能查到的SQL是正在运行的SQL，而运行结束的SQL是看不到的。
会话1：执行1个SQL
mysql>begin;
QueryOK, 0rowsaffected (0.00sec)
mysql>selectsleep(2),now() ,idfromtest1;
+----------+---------------------+----+
|sleep(2) |now() |id|
+----------+---------------------+----+
|0|2023-01-0322:01:09|1|
+----------+---------------------+----+
1rowinset (2.00sec)
此时查看事务情况
mysql>select*frominformation_schema.innodb_trx\G
***************************1.row***************************
trx_id: 421227264232664
trx_state: RUNNING
trx_started: 2023-01-0322: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: REPEATABLEREAD
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
1rowinset (0.00sec)
其中trx_mysql_thread_id=36的会话正是我们会话1的线程id,但是我们看不到具体的SQL。
mysql>select*frominformation_schema.processlistwhereid=36;
+----+------+-----------+--------+---------+------+-------+------+
|ID|USER|HOST|DB|COMMAND|TIME|STATE|INFO|
+----+------+-----------+--------+---------+------+-------+------+
|36|root|localhost|testdb|Sleep|177||NULL|
+----+------+-----------+--------+---------+------+-------+------+
1rowinset (0.00sec)
但是此时通过方式2就可以查到
mysql>selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id\G
***************************1.row***************************
id: 36
info: NULL
thread_id: 76
sql_text: selectsleep(2),now() ,idfromtest1
***************************2.row***************************
id: 37
info: selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id
thread_id: 77
sql_text: selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id
2rowsinset (0.00sec)
注意：此时只能查到一个事务中的多条SQL的最后一个。
例如：
mysql>begin;
QueryOK, 0rowsaffected (0.00sec)
mysql>selectsleep(2),now() ,idfromtest1;
+----------+---------------------+----+
|sleep(2) |now() |id|
+----------+---------------------+----+
|0|2023-01-0322:01:09|1|
+----------+---------------------+----+
1rowinset (2.00sec)
mysql>selectsleep(1),now() ,idfromtest1;
+----------+---------------------+----+
|sleep(1) |now() |id|
+----------+---------------------+----+
|0|2023-01-0322:06:35|1|
+----------+---------------------+----+
会话2查看结果
mysql>selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id\G
***************************1.row***************************
id: 36
info: NULL
thread_id: 76
sql_text: selectsleep(1),now() ,idfromtest1
***************************2.row***************************
id: 37
info: selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id
thread_id: 77
sql_text: selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id
2rowsinset (0.00sec)
可见，查到的是最后一个SQL了，如果事务手动commit提交了，则显示的是commit
1. 查看正在执行的SQL
查看事务中正在执行的SQL方式有多种，例如
1.1 通过processlist查看
会话1：执行1个SQL
mysql>begin;
QueryOK, 0rowsaffected (0.00sec)
mysql>selectsleep(20),now() ,idfromtest1;
会话2：开启另一个会话，查看对应的SQL
mysql>selectid ,infofrominformation_schema.processlistwhereinfoisnotnull;
+----+------------------------------------------------------------------------------+
|id|info|
+----+------------------------------------------------------------------------------+
|36|selectsleep(20),now() ,idfromtest1|
|37|selectid ,infofrominformation_schema.processlistwhereinfoisnotnull|
+----+------------------------------------------------------------------------------+
2rowsinset (0.00sec)
可以看到正在执行的SQL，包括自己的SQL的id及内容
1.2 通过events_statements_current查看
会话1：执行1个SQL
mysql>begin;
QueryOK, 0rowsaffected (0.00sec)
mysql>selectsleep(20),now() ,idfromtest1;
会话2：查看对应的SQL
mysql>selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id\G
***************************1.row***************************
id: 36
info: selectsleep(20),now() ,idfromtest1
thread_id: 76
sql_text: selectsleep(20),now() ,idfromtest1
***************************2.row***************************
id: 37
info: selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id
thread_id: 77
sql_text: selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id
2rowsinset (0.01sec)
2. 方式对比
通过processlist和通过events_statements_current区别在于，processlist中能查到的SQL是正在运行的SQL，而运行结束的SQL是看不到的。
会话1：执行1个SQL
mysql>begin;
QueryOK, 0rowsaffected (0.00sec)
mysql>selectsleep(2),now() ,idfromtest1;
+----------+---------------------+----+
|sleep(2) |now() |id|
+----------+---------------------+----+
|0|2023-01-0322:01:09|1|
+----------+---------------------+----+
1rowinset (2.00sec)
此时查看事务情况
mysql>select*frominformation_schema.innodb_trx\G
***************************1.row***************************
trx_id: 421227264232664
trx_state: RUNNING
trx_started: 2023-01-0322: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: REPEATABLEREAD
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
1rowinset (0.00sec)
其中trx_mysql_thread_id=36的会话正是我们会话1的线程id,但是我们看不到具体的SQL。
mysql>select*frominformation_schema.processlistwhereid=36;
+----+------+-----------+--------+---------+------+-------+------+
|ID|USER|HOST|DB|COMMAND|TIME|STATE|INFO|
+----+------+-----------+--------+---------+------+-------+------+
|36|root|localhost|testdb|Sleep|177||NULL|
+----+------+-----------+--------+---------+------+-------+------+
1rowinset (0.00sec)
但是此时通过方式2就可以查到
mysql>selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id\G
***************************1.row***************************
id: 36
info: NULL
thread_id: 76
sql_text: selectsleep(2),now() ,idfromtest1
***************************2.row***************************
id: 37
info: selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id
thread_id: 77
sql_text: selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id
2rowsinset (0.00sec)
注意：此时只能查到一个事务中的多条SQL的最后一个。
例如：
mysql>begin;
QueryOK, 0rowsaffected (0.00sec)
mysql>selectsleep(2),now() ,idfromtest1;
+----------+---------------------+----+
|sleep(2) |now() |id|
+----------+---------------------+----+
|0|2023-01-0322:01:09|1|
+----------+---------------------+----+
1rowinset (2.00sec)
mysql>selectsleep(1),now() ,idfromtest1;
+----------+---------------------+----+
|sleep(1) |now() |id|
+----------+---------------------+----+
|0|2023-01-0322:06:35|1|
+----------+---------------------+----+
会话2查看结果
mysql>selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id\G
***************************1.row***************************
id: 36
info: NULL
thread_id: 76
sql_text: selectsleep(1),now() ,idfromtest1
***************************2.row***************************
id: 37
info: selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id
thread_id: 77
sql_text: selecta.id,a.info, b.thread_id, c.sql_textfrominformation_schema.processlista, performance_schema.threadsb, performance_schema.events_statements_currentcwherea.id=b.processlist_idandb.thread_id=c.thread_id
2rowsinset (0.00sec)
可见，查到的是最后一个SQL了，如果事务手动commit提交了，则显示的是commit。
文章来源网络，作者：运维，如若转载，请注明出处：https://shuyeidc.com/wp/291864.html<
