建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
MySQL如何查看未提交的事務(wù)SQL
MySQL中經(jīng)常遇到事務(wù)中的SQL正在執(zhí)行或執(zhí)行完成后未提交，如何找出對應(yīng)的SQL？
成都創(chuàng)新互聯(lián)主要從事成都做網(wǎng)站、網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)陽明,10年網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):18982081108
1. 查看正在執(zhí)行的SQL
查看事務(wù)中正在執(zhí)行的SQL方式有多種，例如
1.1 通過processlist查看
會話1：執(zhí)行1個SQL
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select sleep(20),now() ,id from test1;
會話2：開啟另一個會話，查看對應(yīng)的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)
可以看到正在執(zhí)行的SQL，包括自己的SQL的id及內(nèi)容。
1.2 通過events_statements_current查看
會話1：執(zhí)行1個SQL
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select sleep(20),now() ,id from test1;
會話2：查看對應(yīng)的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區(qū)別在于，processlist中能查到的SQL是正在運行的SQL，而運行結(jié)束的SQL是看不到的。
會話1：執(zhí)行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)
此時查看事務(wù)情況
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)
注意：此時只能查到一個事務(wù)中的多條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查看結(jié)果
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了，如果事務(wù)手動commit提交了，則顯示的是commit
1. 查看正在執(zhí)行的SQL
查看事務(wù)中正在執(zhí)行的SQL方式有多種，例如
1.1 通過processlist查看
會話1：執(zhí)行1個SQL
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select sleep(20),now() ,id from test1;
會話2：開啟另一個會話，查看對應(yīng)的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)
可以看到正在執(zhí)行的SQL，包括自己的SQL的id及內(nèi)容
1.2 通過events_statements_current查看
會話1：執(zhí)行1個SQL
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select sleep(20),now() ,id from test1;
會話2：查看對應(yīng)的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區(qū)別在于，processlist中能查到的SQL是正在運行的SQL，而運行結(jié)束的SQL是看不到的。
會話1：執(zhí)行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 |
+----------+----------------
網(wǎng)站標(biāo)題：MySQL如何查看未提交的事務(wù)SQL
網(wǎng)站網(wǎng)址：http://www.fisionsoft.com.cn/article/djsiojc.html
