How to Disable Automatic Commit Mode in Oracle JDBC

  • by
Oracle JDBC driver enables automatic commit on by defaults for every new connection. Since it might be expensive to commit to the database after every successful DML, DBA usually suggests ASA(Application Server Administrator) to disable this mode on application-server-level controlled by ASA or connection-level controlled by Java program to improve the performance.

We can expect the performance will be improved greatly, especially when those DML generate too many but share very little cursors. To disable the auto-commit mode by doing this:
OracleDataSource ods = new OracleDataSource();
Connection conn = ods.getConnection();
// To disable auto-commit mode.
conn.setAutoCommit (false);
// To enable auto-commit mode again by doing this.
conn.setAutoCommit (false);

Without auto-commit mode, JDBC hands over the controller of commit to Java program to decide when and where to commit the transactions. Therefore, it will not only improve performance, but also change the business logic. For example, thousands of various transactions are processing online, they could not see the real-time results from other sessions as usual do. Furthermore, some junior Java developers might forgot to commit DML in the program and causes a lot of unnecessary, repeated or pending transactions, and users may get confused for a long time.

Leave a Reply

Your email address will not be published. Required fields are marked *