11

I have noticed some programmer using COMMIT other using conn.setAutoCommit(true); to end the transaction or roll back so what are the benefits of using one instead of the other?

Where is the main difference?

conn.setAutoCommit(true);

over

statement.executeQuery(query);
statement.commit();
HDJEMAI
  • 9,436
  • 46
  • 67
  • 93
Motasem
  • 599
  • 1
  • 6
  • 13
  • 1
    They are separate tools for different purposes. One is not "better" or "more correct" than the other. – Affe May 04 '12 at 23:26
  • when using commit client on network couldn't see the new records even they refreshing thier resultset, but when set to autocommit true, it's working fine, that's my problem. – Motasem May 04 '12 at 23:30
  • This questions show a misunderstanding of what the two methods are. As Affe said, they are in no way the same thing. When auto commit mode is enabled, then every statement is automatically committed. When it's enabled, transactions are implicitly started, and commit() must be called to actually commit them (even when a transaction was not explicitly started). commit() commits an open transaction. setAutoCommit determines whether or not transactions are started implicitly or explicitly (sort of). – Corbin May 05 '12 at 08:15
  • @LuiggiMendoza A driver changing to `autoCommit = true` after a commit is - as far as I am aware - not compliant with the JDBC spec (although you do need to read between the lines). – Mark Rotteveel May 05 '12 at 08:16

2 Answers2

14

You should in general use Connection.commit() and not Connection.setAutoCommit(true) to commit a transaction, unless you want to switch from using transaction to the 'transaction per statement' model of autoCommit.

That said, calling Connection.setAutoCommit(true) while in a transaction will commit the transaction (if the driver is compliant with section 10.1.1 of the JDBC 4.1 spec). But you should really only ever do that if you mean to stay in autoCommit after that, as enabling / disabling autoCommit on a connection may have higher overhead on a connection than simply committing (eg because it needs to switch between transaction managers, do additional checks, etc).

You should also use Connection.commit() and not use the native SQL command COMMIT. As detailed in the documentation of Connection:

Note: When configuring a Connection, JDBC applications should use the appropriate Connection method such as setAutoCommit or setTransactionIsolation. Applications should not invoke SQL commands directly to change the connection's configuration when there is a JDBC method available.

The thing is that commands like commit() and setAutoCommit(boolean) may do more work in the background, like closing ResultSets and closing or resetting Statements. Using the SQL command COMMIT will bypass this and potentially bring your driver / connection into an incorrect state.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • 2
    This isn't what the user was asking about. – Corbin May 05 '12 at 08:11
  • @Corbin I updated my answer. I focussed too much on the `COMMIT` in the text. – Mark Rotteveel May 05 '12 at 08:24
  • So in essence `conn.setAutoCommit(true)` is the same as `conn.commit(); conn.setAutoCommit(true);` yes? – rogerdpack May 07 '18 at 21:44
  • 1
    @rogerdpack Yes, see the javadoc of [`setAutoCommit(boolean)`](https://docs.oracle.com/javase/10/docs/api/java/sql/Connection.html#setAutoCommit(boolean)): _" If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed."_ – Mark Rotteveel May 08 '18 at 15:22
4

The usage of conn.setAutoCommit(); applies to the connection and gives you the possibility to execute X queries in a single transaction, or use one single transaction per execute

As the API describes: 

 void setAutoCommit(boolean autoCommit)
                    throws SQLException

Sets this connection's auto-commit mode to the given state. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. By default, new connections are in auto-commit mode

For a simple case: 

conn.setAutoCommit(false); 
statement.executeQuery(query); 
statement.commit();

will be the same as:

conn.setAutoCommit(true); 
statement.executeQuery(query);
MrJames
  • 676
  • 2
  • 8
  • 20
  • you should also show the difference when using 2 inserts/update/delete statements in a single method – Luiggi Mendoza May 04 '12 at 23:34
  • if conn.setAutoCommit(true); then i will be not save since ACID is a priority here, but can i use conn.setAutoCommit(false); statement.executeQuery(query); statement.commit(); conn.setAutoCommit(true); and what will be the reflection in this caase? – Motasem May 04 '12 at 23:37
  • @Motasem but `conn.setAutoCommit(true); statement.executeQuery(query);` will do the same as `conn.setAutoCommit(false); statement.executeQuery(query); statement.commit();` check if your `statement.commit()` is being called with success and not returning some `SQLException` – MrJames May 04 '12 at 23:43
  • No Error found but: conn.setAutoCommit(false); then after transaction done i did commit(); i have checked the status of autoCommit mode i found that it still false !!!!! – Motasem May 05 '12 at 00:02
  • when i started a transaction i have set conn.setAutoCommit(false); then after transaction done i did commit(); i have checked the status of autoCommit mode after commit i found that autoCommit mode still false !!!!! what is wrong ? – Motasem May 05 '12 at 00:06
  • 1
    @Motasem Please read the [Connection API docs](http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#commit()). What it says is that to explicitly use `commit()` you should have auto-commit mode disabled, it doesn't say that your autoCommit mode will change... – MrJames May 05 '12 at 00:12