Should autocommit of a datasource be set to false?

Question

please see the comments in spring DataSourceTransactionManager.java, function doBegin:

// Switch to manual commit if necessary. This is very expensive in some JDBC drivers,
// so we don't want to do it unnecessarily (for example if we've explicitly
// configured the connection pool to set it already).
        if (con.getAutoCommit()) {
            txObject.setMustRestoreAutoCommit(true);
            if (logger.isDebugEnabled()) {
                logger.debug("Switching JDBC Connection [" + con + "] to manual commit");
            }
            con.setAutoCommit(false);
        }

In the project I'm working on, autocommit is not configured. So it is true by default. We are using Spring to manage transactions, and all SQLs are executed within @Transactional annotated functions. So Transactions are acturally manually committed. Everytime a transaction begin, the db connection is set autocommit to false, and after the transaction exit autocommit is set back to true. A typical workflow would be (at JDBC level)：

1. conn = dataSource.getConnection();
2. conn.setAutoCommit(false);
3. stmt = conn.createStatement();
4. stmt.executeQuery(...);
5. conn.commit()/ conn.rollback();
6. conn.setAutoCommit(true);

Is setting autocommit back and forth expensive? should we config datasource connection pool autocommit=false for performance reason? to skip the step 2 and step 6.

Solution

1) autocommit is totally dependent on the database, what it means is that, each and every statement through the connection will be executed in a separate transaction which is implicitly carried out. Unless and until, you want to use personal coding and avoid those locks being held by multiple statements which could lead to conflicts with other users, there is no need to set the autocommit to false.

2)From performance points of view,

a) if you have a lot of users and there is some conflict that is occuring because of holding of database locks, then, there may be a need to check into the issues pertaining to it but as a general rule, autocommit was introduced for simplification of stuff for beginners.

b) there may be instances where you need to rollback .

c) you want to commit the transaction manually based on a specific condition.

EDIT: I see you have edited the question, to answer you simply, autocommit=false will force you to write your own commit/rollback/etc, performance is totally dependent on the database, the number of locks held at a moment in real time!!

No. setting autocommit to false and true again will not increase the toll on the system.

NO, do not config datasource connection pool autocommit=false unless you are doing it for some specific reason and are an experienced person. From performance points of view, as i already decalred, it is dependent on the type of database and real time users accessing the database at an instance, for your project, 99.99 percent you wouldn't be needing to set it to false.

setting autocommit to true will just ensure that commit is called after each and every statement.

I also see that you are getting your connection from a datasource, in such cases, it is best to leave the connection with default settings so that the next time that connection is fetched from the pool, there wouldn't be any trouble with the workflow

Hope this helped!!