JDBC when does Auto-Commit commit

Question

I am working on reducing deadlocks and it was pointed out to me that I should not use multiple queries with one connection because the transaction may not be committed and open, causing deadlocks. So in pseudo code, something like this:

try(Connection con = datasource.getConnection()) 
    {
        PreparedStatement stm1 = con.prepareStatement(getSQL());
        stm1.setString(1, owner);
        stm1.setTimestamp(2, Timestamp.valueOf(LocalDateTime.now()));
        stm1.setInt(3, count);

        int updateCount = stm1.executeUpdate();
        stm1.close();


        PreparedStatement stm2 = con.prepareStatement(getSQL2());
        stm2.setString(1, owner);
        ResultSet rs = stm2.executeQuery();

        List<Object> results = new ArrayList<>();
        while(rs.next()) {
            results.add(create(rs));
        }

        return results;
    } catch (SQLException e) {
        throw new RuntimeException("Failed to claim message",e);
    }

When does stm1 commit the transaction when auto-commit is set to true?

Is it good practise to reuse a connection like that or should both statements use separate connections instead?

Answer 1

Questions like these can usually be answered by reading the JDBC specification. JDBC 4.2 section 10.1 Transaction Boundaries and Auto-commit says:

When to start a new transaction is a decision made implicitly by either the JDBC driver or the underlying data source. Although some data sources implement an explicit “begin transaction” statement, there is no JDBC API to do so. Typically, a new transaction is started when the current SQL statement requires one and there is no transaction already in place. Whether or not a given SQL statement requires a transaction is also specified by SQL:2003.

The Connection attribute auto-commit specifies when to end transactions. Enabling auto-commit causes a transaction commit after each individual SQL statement as soon as that statement is complete. The point at which a statement is considered to be “complete” depends on the type of SQL statement as well as what the application does after executing it:

• For Data Manipulation Language (DML) statements such as Insert, Update, 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.

In your code a transaction is committed as part of stm1.executeUpdate() (this transaction might have been started on prepare, or on execute). A new transaction is started at prepare or execute of stmt2, but as you don't close stmt2 or rs, the connection close will trigger the commit.

As to whether you should reuse connections and statements: it depends on context and your code. For a specific unit of work you use a single connection. If you want further reuse of connections you should use a connection pool. Reusing statements should only be done when it makes sense to do so (otherwise your code might get complicated with resource leaks as a consequence), and again there are connection pools that provide built-in statement pooling that reduces this complexity for you.

Statements like "[..] should not use multiple queries with one connection because the transaction may not be committed and open, causing deadlocks." are usually incorrect and would lead to badly performing applications if applied. It might apply to misbehaving drivers that don't properly follow the auto-commit rules above, or maybe in situations were the connection is much longer lived and you don't properly finish a statement (like in the case of stmt2). That said, it is usually better to disable auto-commit and explicitly commit or rollback when you are done.

Your code could be improved by using try-with-resources for the statements and result sets as well, as this ensures result set and statement are closed as soon as possible, even when exceptions occur:

try (Connection con = datasource.getConnection()) {
    try (PreparedStatement stm1 = con.prepareStatement(getSQL())) {
        stm1.setString(1, owner);
        stm1.setTimestamp(2, Timestamp.valueOf(LocalDateTime.now()));
        stm1.setInt(3, count);

        int updateCount = stm1.executeUpdate();
    }

    try (PreparedStatement stm2 = con.prepareStatement(getSQL2())) {
        stm2.setString(1, owner);
        try (ResultSet rs = stm2.executeQuery()) {

            List<Object> results = new ArrayList<>();
            while(rs.next()) {
                results.add(create(rs));
            }
            return results;
        }
    }
} catch (SQLException e) {
    throw new RuntimeException("Failed to claim message", e);
}