java JDBC - 只读操作的 setAutoCommit

JDBC - setAutoCommit for read only operation

提问by dcp

Let's say I have a common method which creates a DB connection:

假设我有一个创建数据库连接的常用方法：

Connection getConnection() throws SQLException {
    Connection con = ... // create the connection
    con.setAutoCommit(false);
    return con;
}

I put the setAutoCommit(false)call here so that callers of this method never have to worry about setting it. However, is this a bad practice if the operation executed by the caller is only reading data? Is there any extra overhead?

我把setAutoCommit(false)调用放在这里，这样这个方法的调用者就不必担心设置它。但是，如果调用者执行的操作只是读取数据，这是一种不好的做法吗？是否有额外的开销？

My personal opinion is that it's better to centralize the logic in one place, that way callers never have to set the auto commit and this avoids code redundancy. I just wanted to make sure it didn't incur any unnecessary overhead for a read only operation.

我个人的观点是，最好将逻辑集中在一个地方，这样调用者就不必设置自动提交，从而避免代码冗余。我只是想确保它不会为只读操作带来任何不必要的开销。

采纳答案by Pascal Thivent

I put the setAutoCommit(false) call here so that callers of this method never have to worry about setting it.

我把 setAutoCommit(false) 调用放在这里，这样这个方法的调用者就不必担心设置它。

This is fine IMO and I personally believe that one should never ever enable auto-commit mode inside an application. So my recommendation would be to turn off auto-commit.

这是很好的 IMO，我个人认为永远不应该在应用程序中启用自动提交模式。所以我的建议是关闭自动提交。

However, is this a bad practice if the operation executed by the caller is only reading data? Is there any extra overhead?

但是，如果调用者执行的操作只是读取数据，这是一种不好的做法吗？是否有额外的开销？

From a strict performance point of view, it's starting and ending a database transaction for every SQL statement that has an overhead and may decrease the performance of your application.

从严格的性能角度来看，它为每个具有开销并可能降低应用程序性能的 SQL 语句开始和结束数据库事务。

By the way, SELECT statements are affected by setAutoCommit(boolean)according to the javadoc:

顺便说一句，setAutoCommit(boolean)根据javadoc ，SELECT语句受以下影响：

Sets this connection's auto-commit mode to the given state. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. By default, new connections are in auto-commit mode.

The commit occurs when the statement completes.The time when the statement completes depends on the type of SQL Statement:

• For DML statements, such as Insert, Update or Delete, and DDL statements, the statement is complete as soon as it has finished executing.
• For Select statements, the statement is complete when the associated result set is closed.
• For CallableStatement objects or for statements that return multiple results, the statement is complete when all of the associated result sets have been closed, and all update counts and output parameters have been retrieved.

将此连接的自动提交模式设置为给定状态。如果连接处于自动提交模式，则其所有 SQL 语句都将作为单独的事务执行和提交。否则，它的 SQL 语句将被分组到通过调用 commit 方法或 rollback 方法终止的事务中。默认情况下，新连接处于自动提交模式。

当语句完成时发生提交。语句完成的时间取决于 SQL 语句的类型：

• 对于DML 语句，例如Insert、Update 或Delete 以及DDL 语句，该语句一执行完成即完成。
• 对于 Select 语句，当关联的结果集关闭时，语句就完成了。
• 对于 CallableStatement 对象或返回多个结果的语句，当所有关联的结果集都已关闭并且所有更新计数和输出参数已被检索时，该语句就完成了。

回答by BalusC

Autocommit doesn't have any value for SELECTqueries. But turning autocommit off is indeed a more common practice. More than often you'd like to fire queries in a transaction. Most of the connection pools also by default turns it off. I would however suggest to make it a configuration setting of your connection manager and/or to overload the method taking a boolean argument, so that you at least have anycontrol over it for the case that.

自动提交对SELECT查询没有任何价值。但是关闭自动提交确实是一种更常见的做法。您经常希望在事务中触发查询。大多数连接池默认情况下也将其关闭。不过，我会建议让您的连接管理器的配置设置和/或重载方法采取一个布尔值参数，所以你至少有任何针对该情况进行控制。

回答by beldaz

This is an old question, but I wanted to give give a different opinion on the issue.

这是一个老问题，但我想就这个问题给出不同的意见。

Performance

表现

The performance overhead from transactions varies with the concurrency control mechanism: normally multi-version concurrency control or locking. The concern expressed in the other answers seems to be down to the cost of ending a transaction, but in my experience the biggest pain is long-running transactions, which can cause performance bottlenecks. For instance, if the DBMS uses locking, some parts of a table cannot be updated until a transaction involving that table has been terminated. More frustrating in systems such as Oracle is that DDL operations (ALTER TABLE, etc.) have to wait until all transactions using that table have ended, leading to troublesome time-outs. So don't think your transaction has no penalty if you're just using SELECTs.

事务的性能开销因并发控制机制而异：通常是多版本并发控制或锁定。其他答案中表达的担忧似乎归结为结束交易的成本，但根据我的经验，最大的痛苦是长时间运行的交易，这可能会导致性能瓶颈。例如，如果 DBMS 使用锁定，则在涉及该表的事务终止之前，表的某些部分无法更新。在 Oracle 等系统中更令人沮丧的是，DDL 操作（ALTER TABLE等）必须等到使用该表的所有事务都结束，从而导致麻烦的超时。所以如果你只是使用SELECTs，不要认为你的交易没有惩罚。

Conventions

公约

A subtle problem with setting the autocommit behaviour off is that you are changing from the default, so anyone else working with your code may not be expecting it. It is really easyto leave an accidental path through a function than does not end with an explicit commitor rollback, and this can lead to unpredictable behaviour in subsequently called functions. Conversely, a large proportion of the DB-interfacing code that I have seen contains a single statement within each function, for which autocommit behaviour is very well suited. In fact a lot of the multi-statement functions I have encountered could have been re-written as single statements with a little more SQL know-how - poor approximations to joins implemented in Java are sadly common.

关闭自动提交行为的一个微妙问题是您正在更改默认值，因此使用您的代码的其他人可能不会期望它。这是很容易通过函数比不明确的结尾留下一个偶然的路径commit或者rollback，这可能导致在随后调用的函数不可预知的行为。相反，我看到的大部分 DB 接口代码在每个函数中都包含一条语句，自动提交行为非常适合这种情况。事实上，我遇到的许多多语句函数都可以被重写为具有更多 SQL 知识的单个语句 - 遗憾的是，Java 中实现的连接的近似值很差很常见。

My personal preference, based on reasonable experience, is as follows for any functions making calls to a database:

我个人的偏好，基于合理的经验，对于调用数据库的任何函数如下：

• keep to the default JDBC behaviour of auto-commit on;
• when your function includes more than one SQL statement, use an explicit transaction by setting setAutocommit(false)at the start of each function and calling commit()(or rollback()if appropriate) at the end, and ideally rollback()in the catchblock;
• enforce the default by putting setAutocommit(true)in the finallyblock that wraps your JDBC calls in the function (unlike APIs such as PHP/PDO, JDBC won't do this for you after commit()/rollback());
• if you're feeling extra defensive, explicitly set your choice of setAutocommit(true)or setAutocommit(false)at the start of every function;

• 保持自动提交的默认 JDBC 行为；
• 当您的函数包含多个 SQL 语句时，通过setAutocommit(false)在每个函数的开头设置并在末尾调用commit()（或rollback()如果合适），最好rollback()在catch块中使用显式事务；
• 通过将包含 JDBC 调用setAutocommit(true)的finally块放入函数中来强制执行默认值（与 PHP/PDO 等 API 不同，JDBC 不会在commit()/之后为您执行此操作rollback()）；
• 如果您感觉更加防御，请明确设置您的选择setAutocommit(true)或setAutocommit(false)在每个功能开始时；

回答by Nivas

I would never have autoCommit set to true anywhere in the application. The performance overhead if at all any is nothing compared to the side effects of a autocommit=trueconnection.

我永远不会在应用程序的任何地方将 autoCommit 设置为 true。与连接的副作用相比，性能开销（如果有的话）根本算不了什么autocommit=true。

You say you would never use this connection for DML. But that is the intention, maintained perhaps by coding standards etc. But in practice, it ispossible to use this connection for DML statements. This is enough reason for me to never set auto-commit on.

您说您永远不会将此连接用于 DML。但是，这是意图，通过编码标准等，但在实践中可能保持，但它是可以使用此连接的DML语句。这足以让我从不设置自动提交。

Selectstatements are definitely going to take some memory/CPU/network. Let the overhead of autocommit be a (very marginal) fixed overhead on every select statement, to make sure data integrity and stability of your application is maintained.

Select语句肯定会占用一些内存/CPU/网络。让自动提交的开销成为每个 select 语句上的（非常边缘的）固定开销，以确保维护应用程序的数据完整性和稳定性。