Execute Multiple DML on single java.sql.Statement

Question

How transactions will behave; If I use following syntax to execute multiple DML using single java.sql.Statement

String sqlStr = "INSERT INTO.... \n update t1 set....";
statemet.execute(sqlStr);

I am not altering default connection setting (autocommit = true).

I am aware this is not the right way / best practice; just curious to know the transactional nature of above code That is if update fails with SQLException, will insert rollback?

Answer 1

With autocommit set to true, then the individual statements would very likely execute in their own individual transactions. This might depends on the database, but I suspect not.

Best way to find out is to try it.

Answer 2

Tried out, got some surprising results with MS SQL sever 2008

I tried 2 update statements and as per observation they run in a single transaction; that is if second update fails first update statement is rolled back.

The surprising thing I noticed is :

https://connect.microsoft.com/SQLServer/feedback/details/620522/invalid-object-exception-not-thrown-by-com-microsoft-sqlserver-jdbc-sqlserverdriver#

• Sean Reilly: In this sort of situation, you can make SQL Server roll back previous statements (or not) by using SET XACT_ABORT: msdn.microsoft.com/en-us/library/ms188792.aspx

Answer 3

I tried in MySQL environment. It worked very well. And I think it is the basic way to do so.