Categories: Java

Does JDBC method Connection.setAutoCommit(boolean) have a performance penalty? A colleague was concerned, so I performed some tests..

The Behaviour

When an application estabishes a connection (aka “session”) to a database, the database initially marks that connection as having “autocommit” enabled, ie when an insert/update/delete statement is sent via that connection to the database then the change is automatically permanently committed. The database then provides a way for the client application to toggle this state, ie indicate whether the commands they send for a particular connection should be “autocommitted”, or whether this mode should be turned off (in which case the client must send explicit commit/rollback commands). For database client “drivers” that support the standard Java JDBC API, this is done via the method java.sql.Connection.setAutoCommit(boolean).

When using database connection pooling, and when most code using that connection-pool is performing transactional operations, the following sequence is very common:

• a connection is fetched from the connection-pool; it has state autocommit=true
• Connection.setAutoCommit(false) is called
• some operations are done
• Connection.commit is called
• the connection is returned to the pool, which triggers a call to Connection.setAutoCommit(true)
• the above cycle repeats.

The re-enabling of autocommit when the connection is returned to the pool is necessary to ensure that the next user of this pool gets a connection in a consistent state; it is not good if the pool sometimes returns a connection with autocommit on, and sometimes with autocommit off.

The consequence is frequent occurrences of the following sequence:

• connection.setAutoCommit(false)
• operations..
• connection.setAutoCommit(true)
• connection.setAutoCommit(false)
• operations..
• connection.setAutoCommit(true)
• connection.setAutoCommit(false)
• etc

The Question

The question is: do all these calls to setAutoCommit cause a significant performance problem? If each call triggers a round-trip to the database server, then that would be bad; if it just toggles a boolean flag in memory then it isn’t a problem.

The autocommit state is a per-connection property; it has no effect on operations outside that connection. And even when pooling is enabled, a jdbc Connection object has complete control over all data sent to the database for that connection - there is no way to communicate with the database over that logical connection except via the Connection object.

It therefore seems that the autocommit state can be easily cached in the Connection object; when a real operation (a select/insert/update/delete) is performed, the required autocommit state can be sent then. And by delaying communication until needed, calls to setAutoCommit which toggle the state twice (like those above) can simply be ignored. Doing this would require some optimisation code in the jdbc driver, but it seems pretty simple to implement and brings a significant benefit in a common use-case. Therefore any decent-quality JDBC driver could be expected to contain this optimisation.

To verify, I wrote a simple test program that:

1: loops 10000 times, toggling a boolean variable (for baseline)

2: loops 10000 times, calling setAutoCommit(true); setAutoCommit(false);

3: loops 10000 times, performing the simplest select I can think of (just to test the round-trip time to the database)

The “simplest” statement for oracle was “select 1 from DUAL”; for SQLServer it was “select 1”. Both the Oracle and SqlServer databases I tested against were remote (ie not on the local host), and there was a reasonably slow network connection from my test client system to the databases (good in this case, as it makes the round-trip delay significant).

The Results

Test 1 took 1 millisecond on my local system.

And below are the results for tests (2) and (3) with different JDBC thin database drivers. The times varied a little of course, but the exact values are not significant here.

oracle jdbc driver ojdb6 version 11.2.0.3

2: 6 milliseconds

3: 4400 msecs

oracle jdbc driver ojdbc7 version 12.1.0.2.0

2: 20msec

3: 5100 msec

microsoft sqlserver driver sqljdbc4 version 4.0.2206.100

2: 4760msec

3: 4550 msec

So clearly, Oracle’s jdbc driver has optimised this path. Surprisingly, Microsoft’s driver has not.

Interestingly, it also appears that on these operations, the newer Oracle driver is 10% slower than the older one…