JDBC - setAutoCommit 用于只读操作
假设我有一个创建数据库连接的常用方法：
Connection getConnection() throws SQLException {
Connection con = ... // create the connection
con.setAutoCommit(false);
return con;
}
我将 setAutoCommit(false) 调用放在这里，以便该方法的调用者永远不必担心设置它。但是，如果调用者执行的操作只是读取数据，这是一个不好的做法吗？有额外的开销吗？
我个人的观点是，最好将逻辑集中在一处，这样调用者就不必设置自动提交，从而避免了代码冗余。我只是想确保只读操作不会产生任何不必要的开销。
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?
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
在我看来，这很好，我个人认为永远不应该在应用程序中启用自动提交模式。所以我的建议是关闭自动提交。
从严格的性能角度来看，它为每个 SQL 语句启动和结束数据库事务，这会产生开销，并可能会降低应用程序的性能。
顺便说一下，SELECT 语句受
setAutoCommit(boolean)根据javadoc：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.
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.
By the way, SELECT statements are affected by
setAutoCommit(boolean)according to the javadoc:自动提交对于
SELECT查询没有任何价值。但关闭自动提交确实是一种更常见的做法。您经常希望在事务中触发查询。大多数连接池默认情况下也会将其关闭。不过，我建议将其作为连接管理器的配置设置和/或重载采用布尔参数的方法，以便您至少可以在这种情况下对其进行控制。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 any control over it for the case that.这是一个老问题了，但我想就这个问题给出不同的看法。
性能
事务的性能开销随并发控制机制的不同而不同：通常是多版本并发控制或锁定。其他答案中表达的担忧似乎归结于结束事务的成本，但根据我的经验，最大的痛苦是长时间运行的事务，这可能会导致性能瓶颈。例如，如果 DBMS 使用锁定，则在涉及该表的事务终止之前，表的某些部分无法更新。在 Oracle 等系统中，更令人沮丧的是 DDL 操作（
ALTER TABLE等）必须等到使用该表的所有事务都结束，从而导致麻烦的超时。因此，如果您只是使用SELECT，请不要认为您的交易不会受到任何惩罚。约定
关闭自动提交行为的一个微妙问题是您正在更改默认行为，因此使用您的代码的其他人可能不会期望它。在函数中留下意外路径非常容易，而不是以显式
提交或回滚结束，这可能会导致不可预测的结果随后调用的函数中的行为。相反，我见过的大部分数据库接口代码在每个函数中都包含一个语句，自动提交行为非常适合该语句。事实上，我遇到的许多多语句函数都可以用更多的 SQL 知识将其重写为单个语句 - 可悲的是，在 Java 中实现的联接的不良近似很常见。根据合理的经验，我个人对调用数据库的任何函数的偏好如下：
setAutocommit(false)并调用commit()（或rollback()（如果适用）放在最后，并且最好将rollback()放在catch块中；setAutocommit(true)放入将 JDBC 调用包装在函数中的finally块中来强制执行默认值（与 PHP/PDO 等 API 不同，JDBC 不会执行此操作在commit()/rollback()之后为您提供);setAutocommit(true)或setAutocommit(false)；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 usingSELECTs.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 easy to leave an accidental path through a function than does not end with an explicit
commitorrollback, 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.My personal preference, based on reasonable experience, is as follows for any functions making calls to a database:
setAutocommit(false)at the start of each function and callingcommit()(orrollback()if appropriate) at the end, and ideallyrollback()in thecatchblock;setAutocommit(true)in thefinallyblock that wraps your JDBC calls in the function (unlike APIs such as PHP/PDO, JDBC won't do this for you aftercommit()/rollback());setAutocommit(true)orsetAutocommit(false)at the start of every function;我永远不会在应用程序中的任何地方将 autoCommit 设置为 true。
与侧面相比，性能开销（如果有的话）根本不算什么
autocommit=true连接的影响。您说您永远不会使用此连接进行 DML。但这就是意图，可能是通过编码标准等来维护的。但在实践中，可以将此连接用于DML 语句。这足以让我从不设置自动提交。
Select语句肯定会占用一些内存/CPU/网络。让自动提交的开销成为每个 select 语句上的（非常微不足道的）固定开销，以确保维护应用程序的数据完整性和稳定性。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.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 is possible to use this connection for DML statements. This is enough reason for me to never set auto-commit on.
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.