JDBC - setAutoCommit for read only operation

Question

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.

Answer 1

Autocommit doesn't have any value for SELECT queries. 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.

Answer 2

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

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.

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.

By the way, SELECT statements are affected by setAutoCommit(boolean) according to the 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.

Answer 3

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=true connection.

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.

Select statements 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.