为什么我只看到提交或提交显示完整的PROCESSLIST -没有SQL语句

Question

我在很多来自SHOW FULL PROCESSLIST的输出中只看到了COMMIT或commit (小写和调理混合)。这些交易是什么？为什么没有SQL语句？我们正在运行MariaDB 10.1.x和Galera复制(3个节点)。

如何对这些事务进行整数处理？

> select COMMAND,TIME,STATE,INFO,TIME_MS,STAGE,MAX_STAGE,PROGRESS,MEMORY_USED,EXAMINED_ROWS,QUERY_ID,INFO_BINARY,TID from INFORMATION_SCHEMA.PROCESSLIST where INFO like '%commit%';
+---------+------+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------+-----------+----------+-------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+
| COMMAND | TIME | STATE                | INFO                                                                                                                                                                              | TIME_MS    | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | EXAMINED_ROWS | QUERY_ID  | INFO_BINARY                                                                                                                                                                       | TID   |
+---------+------+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------+-----------+----------+-------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+
| Query   |    1 | init                 | COMMIT                                                                                                                                                                            |   1267.015 |     0 |         0 |    0.000 |       67544 |             0 | 483610134 | COMMIT                                                                                                                                                                            | 12241 |
| Query   |  112 | init                 | COMMIT                                                                                                                                                                            | 112442.763 |     0 |         0 |    0.000 |       67544 |             0 | 483594429 | COMMIT                                                                                                                                                                            | 12003 |
| Query   |  151 | init                 | COMMIT                                                                                                                                                                            | 151914.251 |     0 |         0 |    0.000 |       67544 |             0 | 483588122 | COMMIT                                                                                                                                                                            | 11972 |
| Query   |  156 | init                 | COMMIT                                                                                                                                                                            | 156962.716 |     0 |         0 |    0.000 |      141368 |             0 | 483587455 | COMMIT                                                                                                                                                                            | 11962 |
| Query   |  156 | init                 | COMMIT                                                                                                                                                                            | 156961.757 |     0 |         0 |    0.000 |      141368 |             0 | 483587456 | COMMIT                                                                                                                                                                            | 11960 |
| Query   |  182 | init                 | commit                                                                                                                                                                            | 182230.206 |     0 |         0 |    0.000 |       67544 |             0 | 483584325 | commit                                                                                                                                                                            | 11801 |
| Query   |  229 | init                 | COMMIT                                                                                                                                                                            | 229144.061 |     0 |         0 |    0.000 |       67544 |             0 | 483578193 | COMMIT                                                                                                                                                                            | 11529 |
| Query   |    0 | Filling schema table | select COMMAND,TIME,STATE,INFO,TIME_MS,STAGE,MAX_STAGE,PROGRESS,MEMORY_USED,EXAMINED_ROWS,QUERY_ID,INFO_BINARY,TID from INFORMATION_SCHEMA.PROCESSLIST where INFO like '%commit%' |      0.346 |     0 |         0 |    0.000 |      104808 |             0 | 483610236 | select COMMAND,TIME,STATE,INFO,TIME_MS,STAGE,MAX_STAGE,PROGRESS,MEMORY_USED,EXAMINED_ROWS,QUERY_ID,INFO_BINARY,TID from INFORMATION_SCHEMA.PROCESSLIST where INFO like '%commit%' | 11359 |
| Query   |   66 | init                 | commit                                                                                                                                                                            |  66835.790 |     0 |         0 |    0.000 |       67544 |             0 | 483601099 | commit                                                                                                                                                                            | 10917 |
| Query   |  353 | init                 | commit                                                                                                                                                                            | 353104.108 |     0 |         0 |    0.000 |       67544 |             0 | 483561401 | commit                                                                                                                                                                            | 10807 |
| Query   |  494 | init                 | COMMIT                                                                                                                                                                            | 494696.772 |     0 |         0 |    0.000 |      338232 |             0 | 483540392 | COMMIT                                                                                                                                                                            |  9997 |

Answer 1

如果数据是在显式事务期间写入/更新的，则在从日志文件复制最终COMMIT TRANSACTION语句之前，数据可能根本不会写入数据文件。这使得在错误、显式ROLLBACK TRANSACTION语句、服务器崩溃后重新启动时回滚的操作非常快，因为它只需将相关的日志条目标记为无效。

如果在该事务处理过程中添加或更改了大量数据，这可能需要一些时间。

为什么没有SQL语句？

在本例中，COMMIT TRANSACTION (或某些有效的缩写)是这些会话的当前SQL语句。

Answer 2

有些事务需要在COMMIT期间完成。我想这就是你所看到的。

然而，494秒是一个很长的时间！即使是121秒也是长的。查看您的代码，并为可能的事务找到SQL语句。在这里给出SQL；也许我们可以进一步解释。