MySQL如何查看未提交的事务SQL
2023-06-13 09:16:43 时间
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
相关文章
- SQL Prompt10 安装激活教程,让你写sql 如鱼得水[通俗易懂]
- SQL之mysql到hive批量生成建表语句
- MySQL事务回滚:确保数据安全可靠(mysql回滚事务)
- 深入MySQL:使用相似查找功能(mysql查找相似数据)
- 深入MySQL:嵌套事务的处理(mysql嵌套事务)
- 一步搞定:MySQL批量更新SQL技巧(mysql批量更新sql)
- 处理MySQL事务嵌套处理:深入探索(mysql事务嵌套)
- 解决MySQL关闭事务的问题(mysql关闭事务)
- MySQL查找提升系统性能的慢SQL(mysql查看慢sql)
- MySQL数据库连接指南:简单快速连接数据库!(mysql数据库如何连接)
- MySQL高效查询:轻松实现最快效率(mysql快速查询)
- MySQL优化器源码分析:优化SQL性能的新路(mysql优化器源码)
- MySQL保存文章——安全可靠的存储方式(mysql保存文章)
- MySQL主从自动切换:实现零停机高可用性(mysql主从自动切换)
- Ubuntu系统下安装MySQL数据库(ubuntu下安装mysql)
- MySQL字符转换为数组的方法(mysql字符转数组)
- 语句执行记录MySQL 查看SQL语句执行记录(mysql查看sql)
- 解决MySQL事务失败的回滚技巧(mysql失败回滚)
- MySQL递归函数实现及示例(mysql递归调用函数)
- MySQL如何关闭同步?(mysql关闭同步)
- An Introductory Guide to SQL Database Systems: Understanding MySQL(mysql英文)
- Linux 快速登录 SQL 数据库(linux进入sql)
- SQL Server和MySQL:功能对比(sqlserver和mysql区别)
- 从PL/SQL到MySQL:迈出第一步(plsql访问mysql)
- 如何在MySQL中进行从低到高的排序(mysql中从低到高排序)
- MySQL中事务操作数据保障的有效手段(mysql中事物)
- MySQL中的1265错误问题分析与解决方案(mysql中1265问题)
- 用MySQL实现个性化搜索技巧解析(mysql 个性化搜索)
- 用CMD命令快速连接Mysql数据库(cmd命令连接mysql)
- MySQL查询条件详解,让你熟练掌握SQL语句中的几个条件(mysql中几个条件)
- 如何设置MySQL一般日志25字浓缩如下MySQL一般日志配置详解(mysql一般日志配置)
- MySQL如何使用AVG函数求平均值(mysql下求平均值)
- 初学者如何在MySQL上进行实战操作指南(mysql上机怎么操作)