Does "set transaction" start a transaction when autocommit is ON?

Question

I am using Oracle with AutoCommit ON mode (Java Application using JDBC).

When I execute multiple DML statements as a single transaction, I thought I could do something like this:

set transaction read write
update user_tbl set name='mark' where email='[email protected]'
update user_tbl set name='ken' where email='[email protected]'
--if other things are successful, then:
commit
-- else:
--rollback

However it appears that, whenever I do end up executing rollback, the rows have the new values given my above statements.

So, even though set transaction was executed at the beginning, is it possible that update statements are being executed in AutoCommit ON mode?

krokodilko krokodilko · Accepted Answer · 2015-12-10T21:19:52

This is clearly explained in the documentaton: http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html
http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setAutoCommit(boolean)

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. By default a Connection object is in auto-commit mode, which means that it automatically commits changes after executing each statement. If auto-commit mode has been disabled, the method commit must be called explicitly in order to commit changes; otherwise, database changes will not be saved.

setAutoCommit
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. The commit occurs when the statement completes. The time when the statement completes depends on the type of SQL Statement:

For DML statements, such as Insert, Update or Delete, and DDL statements, the statement is complete as soon as it has finished executing. For Select statements, the statement is complete when the associated result set is closed. For CallableStatement objects or for statements that return multiple results, the statement is complete when all of the associated result sets have been closed, and all update counts and output parameters have been retrieved. NOTE: If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op.

The above means, that your code, which is running in auto-commit mode, is equivalent to:

set transaction read write;
commit; -- invoked by JDBC autocommit
update user_tbl set name='mark' where email='[email protected]';
commit; -- invoked by JDBC autocommit
update user_tbl set name='ken' where email='[email protected]';
commit; -- invoked by JDBC autocommit
--if other things are successful, then:
commit;
commit; -- invoked by JDBC autocommit

Does "set transaction" start a transaction when autocommit is ON?

1 Answers