What is use of Connection.setAutoCommit(false); when using Oracle database?

Question

I was setting con.setAutoCommit(false); as soon as I create connection so that nothing goes in DB uncommitted. But it turns out if you close the connection all transaction will be committed, no matter what your setAutoCommit() status is.

    Class.forName("oracle.jdbc.driver.OracleDriver");
        con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.7.5:xxxx:xxx", "xxx", "xxx");

        con.setAutoCommit(false);

        st = con.createStatement();
        String sql = "INSERT INTO emp (eid, name, dob, address) VALUES (?, ?, ?, ?)";
        PreparedStatement statement = con.prepareStatement(sql);
        statement.setInt(1, 8 );
        statement.setString(2, "kkk" );
        statement.setDate(3, date );
        statement.setString(4, "pppp" );
        pst = con.prepareStatement(sql);
        int rowsInserted = statement.executeUpdate();

        //con.commit ();

        System.out.println ("rowsInserted "+rowsInserted);

        con.close ();

Even after commenting con.commit (); row is being inserted when connection closes so I was wondering what is the use of con.commit (); ?

Another answer says it's vendor specific:

If a Connection is closed without an explicit commit or a rollback; JDBC does not mandate anything in particular here and hence the behaviour is dependent on the database vendor. In case of Oracle, an implict commit is issued.

It does not make sense.Thanks.

You typically don't open your connection for only a single statement. You keep it open (e.g. in a connection pool). And if you use auto-commit how would you rollback a multi-statement transaction? — , Oct 06 '16 at 08:10
Oracle has very well definition for [transanction](https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html) in its docs. But when you call close method what is actually happening is everything is in form of single transaction which follows its ACID property which states that every statement after transaction has to be complete that's why it is saving the data. — legend, Oct 06 '16 at 08:17
Note that other JDBC drivers might rollback if you close a connection without explicit commit or rollback. — Mark Rotteveel, Oct 06 '16 at 10:02

score 2 · Answer 1 · answered Oct 06 '16 at 12:08

Logging off of Oracle commits any pending transaction. This happens whether you use sqlplus or the JDBC driver through conn.close(). Note that it's not the driver that issues the commit it's the server. During logoff the server commits pending changes. From your Java program you can always call conn.rollback() before calling conn.close() if you want to make sure that pending changes are not committed.

You asked what is the use of conn.commit(). It's used to explicitly commit a transaction at a specific point of your business logic. For example if you cache connections in a connection pool, you can disable auto-commit and commit pending changes before releasing the connection back to the pool. Some people prefer enabling auto-commit mode (which is the default in JDBC) and not worry about explicitly committing or rolling back changes. It depends on your business logic. You can ask yourself: will I ever need to rollback a DML execution? If the answer is yes then you should disable auto-commit and explicitly commit transactions.

score 1 · Answer 2 · answered Oct 06 '16 at 08:12

con.commit() is an explicit commit and you can call it whenever you have to commit the transaction. In your case there is no explicit commit or rollback though you have set AutoCommit to false. The Oracle Database commits all the transactions of a session which exits the connection gracefully. If the session terminates abnormally then it rolls back the transactions.

score 1 · Accepted Answer · edited May 23 '17 at 11:48

1

Oracle documentation provides a very good explanation of when and why this should be used. Please go through the same!

If your JDBC Connection is in auto-commit mode pro grammatically or by default, (this is by default, fyi), then every SQL statement is committed to the database upon its completion.

You can refer to this question for more detailed explanation on the same topic.

edited May 23 '17 at 11:48

Community

1
1

answered Oct 06 '16 at 08:23

N00b Pr0grammer

4,503
5
32
46

Thanks! Needed this answer. I had that doc open with other tabs but i did not read it. Doc first, lesson learnt. – Kisslaay Oct 06 '16 at 09:16

What is use of Connection.setAutoCommit(false); when using Oracle database?

3 Answers3