COMMIT OR conn.setAutoCommit(true)
java, jdbc, mysql, percona
Solution
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.
Problem
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(); ```