Clarification of Java/SQLite batch and auto-commit

Question

I copied the following example from an SQLite Java library website:

PreparedStatement prep =
         conn.prepareStatement("insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);

I'm struggling to understand the significance of toggling autoCommit() either side of executeBatch(). Does it merely prevent a commit being made for each individual batch operation? Thus a single 'bulk' commit is will be made by setAutoCommit(true).

Answer 1

The auto commit is disabled before batch because enabling auto commit will commit (i.e. wait for sync to happen which means it will wait the data is actually written to persistent storage like hard disk) after every row that is inserted.

If auto commit is false, it will not wait for sync.

The difference in waiting for sync and not waiting is the guaranty that whether data is actually to hard disk or it is in the buffer (that could be buffered IO or buffer of hard disk).

In short, disabling auto commit gives you performance boost. And I think by default auto commit is enabled.

Another way of optimization

If you want to have auto commit ON and still need performance boost just try to start as transaction before the batch operation and commit the transaction after. This way sqlite wont auto commit after every insert and it will give good performance boost.

EDIT:

When you starting a transaction you are only disabling auto commit for that transaction and it will be again 'on' once transaction is over. What auto commit helps is when you are inserting/updating rows separately (not as batch), then you dont have to start a transaction explicitly for every insert/update. And regarding setting auto-commit to true, after the fact, does not do call for commit. If you make auto-commit true and whatever you have already inserted/updated wont have any effect and won't have same guaranties as auto-commit true prior to making those insert/update.

Here's some information about speeding up Sqlite INSERTs.

Answer 2

There has been some wrong information shared in the answers and comments here so I'd thought I'd add an answer.

1. FYI, SQLite does not support auto-commit directly. Calling setAutoCommit(false) on a SQLite JDBC connection actually starts a transaction with the Xerial and Zentus drivers. See this SO question: How to disable autocommit in sqlite4java?

2. I'm almost positive that setting auto-commit back to true will not do a commit on the database connection. The next statement that finishes will persist any outstanding changes but you have to do a conn.commit(); to actually commit the batch operations.

3. Starting a transaction basically is the same as turning off auto-commit so it is improper to imply that you can leave auto-commit on while inside of a transaction.