Best Practice for AutoCommit statements in Java for MySQL usage via JDBC

Question

Within my Java code I interact with an MySQL database via a JDBC connection. I setup a connection and some statements (for reuse)

            connect = DriverManager.getConnection(connectionstring);            
            statement = connect.createStatement();
            statement2 = connect.createStatement();

For most of the code, statement.executeQuery(query) is fine, but some operations are "blocks", so I need to make sure that they either all go through together, or none of them do. I believe the following code achieves this.

            connect.setAutoCommit(false);
            statement.addBatch(insertquery1);
            statement.addBatch(insertquery2);
            statement.addBatch(insertquery3);
            statement.addBatch(insertquery4);
            statement.executeBatch();
            connect.commit();           
            connect.setAutoCommit(true);

As far as I can tell, the connect calls affect the entire connection, so would impact both statement and statement2. Also, I can seemingly set the AutoCommit values after I created the statement object and it still applies to it. Is this correct? And would it be better practice/more efficient to split them and keep a different connection object just for autocommit calls like below?

            // objects purely for batch operations
            connect = DriverManager.getConnection(connectionstring);            
            connect.setAutoCommit(false);
            statement = connect.createStatement();
            // objects for normal operations
            connect2 = DriverManager.getConnection(connectionstring);           
            connect2.setAutoCommit(true);
            statement2 = connect2.createStatement();

In my application they represent less than 1% of calls, but that percentage does need to be done in batches. What is best practice?

score 5 · Accepted Answer · answered Apr 02 '20 at 06:10

5

The whole point of setting auto commit to false is so that multiple SQL statements can all run inside the same transaction. And, should anything go wrong with any of your 4 insert statements, the whole transaction would logically be rolled back, so that you don't end up with inconsistent data in your database. The pattern you are already following is best practice:

connect.setAutoCommit(false);
statement.addBatch(insertquery1);
statement.addBatch(insertquery2);
statement.addBatch(insertquery3);
statement.addBatch(insertquery4);
statement.executeBatch();
connect.commit();           
connect.setAutoCommit(true);

That is, it is best practice to return auto commit to being true after you have completed your transaction. This is so that any other part of your code, or perhaps your future code, should someone else inherit it, which still uses the same connection object would receive the default behavior of auto commit being true.

answered Apr 02 '20 at 06:10

Tim Biegeleisen

502,043
27
286
360

Thanks. The question is whether I should separate autocommit and normal queries into different connections, e.g. for performance reasons? – Rob Apr 02 '20 at 06:25
Actually, if you are really concerned about performance, you should look into using connection pools, rather than the raw JDBC interface. There is a big overhead in getting a connection from scratch as you are doing it here. That being said, if you can't use connection pooling, then you should avoid creating a new connection for multiple queries, i.e. use the same connection for both auto commit and non auto commit. – Tim Biegeleisen Apr 02 '20 at 06:27
Thats what I am doing at moment, I centrally create a connection object and spin off a small number of statement objects which are then reused through the code elsewhere. Personally prefer this to connection pooling since I can manage the statement (re)usage manually fine. The connection object is created once and then lives up to 6 hours, so a small cost at start is fine. – Rob Apr 02 '20 at 06:29
The general answer to the other part of your question, then, is to avoid creating new connections, because this can be very expensive. If you need to execute, say, 1 transaction with auto commit turned off, and 1 with it turned on, try to use the same connection if possible. – Tim Biegeleisen Apr 02 '20 at 06:31
So there is no real cost associated with the change of autocommit status? I get millions of queries out of my initially created connection/statements, so am more concerned by running cost than start up cost. Probably should have made that clearer in question. – Rob Apr 02 '20 at 06:37
I don't know the answer to your last comment, and the answer might depend on the specific SQL database you are using (or even the version). However, all changing auto commit does it is allows more than one statement to execute in a single transaction. My guess is that the running time of the SQL and/or getting the connections would be the limiting factor here. – Tim Biegeleisen Apr 02 '20 at 06:40
1

I would expect `setAutocommit(false);` not to do anything but set the flag that would result in a `BEGIN;` to be issued when the next statement is executed, beginning a transaction. Although that would be driver dependent. However `setAutocommit(true);` while in a transaction [will issue a commit](https://stackoverflow.com/questions/10457335/commit-or-conn-setautocommittrue). – Kayaman Apr 02 '20 at 07:16

Best Practice for AutoCommit statements in Java for MySQL usage via JDBC

1 Answers1