大量的“ SET autocommit = 0/1” MySQL中的查询 [英] Massive number of "SET autocommit=0/1" queries in MySQL

问题描述

我正在系统上进行一些负载测试，我注意到正在执行大量的 SET autocommit = 0和 SET autocommit = 1查询。 1分钟内大约25,000。我试图弄清楚是什么原因以及如何消除它。

I'm running some load tests on our system and I notice a massive number of "SET autocommit=0" and "SET autocommit=1" queries being executed. Something around 25,000 within 1 minute. I'm trying to figure out what is causing this and how to get rid of it.

我们使用以下技术：

• MySQL

• MySQL

休眠状态

光明

春天

Tomcat

我尝试了以下操作，但似乎无济于事：

I have tried the following but it did not seem to help:

在MySQL中

• SET autocommit = 0

• "SET autocommit = 0" in MySQL

在db连接URL中添加了elideSetAutoCommits属性。 jdbc：mysql：// localhost / db_name？useUniCode = true& characterEncoding = UTF-8& pinGlobalTxToPhysicalConnection = true& elideSetAutoCommits = true

Added the elideSetAutoCommits property in the db connection URL. "jdbc:mysql://localhost/db_name?useUniCode=true&characterEncoding=UTF-8&pinGlobalTxToPhysicalConnection=true&elideSetAutoCommits=true"

有人可以指出导致这些查询的原因吗？

Could someone point me towards what might be causing these queries?

推荐答案

有人可以指出导致这些查询的原因吗？

Could someone point me towards what might be causing these queries?

您的查询是 Connection＃setAutoCommit（boolean） ，用于从自动提交模式的默认模式切换到交易模式，以便插入/更新/删除/读取事务中的数据。

Your queries are the consequence of Connection#setAutoCommit(boolean) which is used to switch from the default mode that is auto-commit mode to transactional mode in order to insert/update/delete/read data within a transaction.

常见代码是：

// Switch to transactional mode which actually triggers a SET autocommit = 0
con.setAutoCommit(false);
try {
    // Some operations on the db
    con.commit();
} finally {
    // Switch back to auto-commit mode which actually triggers a SET autocommit = 1
    con.setAutoCommit(true);
} 

这里是一个很好的链接，解释了事务在 JDBC 中的工作方式。

Here is a good link that explains how transactions work in JDBC.

如果您知道您的连接池将始终用于以事务方式获取连接，则可以在 Hikari 的配置，这要归功于参数 autoCommit 设置为 false 这样，连接将已经处于事务处理模式，因此不再需要修改该模式。

If you know that your pool of connections will always be used to get connections in transactional mode, you can set the default mode in the configuration of Hikari thanks to the parameter autoCommit to set to false this way the connections will be already in transactional mode such that it won't be needed anymore to modify the mode.

此属性控制从池返回的连接
的默认自动提交行为。它是一个布尔值。默认值： true

有关 Hikari 此处。

这篇关于大量的“ SET autocommit = 0/1” MySQL中的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！