java - JDBC - setAutoCommit 只读操作

Question

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

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

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

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

Answer 1

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?

从严格的性能角度来看，它为每个 SQL 语句启动和结束一个数据库事务，这会产生开销并可能降低应用程序的性能。

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

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.