Spring JDBC: Oracle transaction errors out after 120 seconds
<p>For a particular requirement, I will have to iterate through a list of 50000 records and insert them into database. The requirement is that if any one of the 50000 records fail, all the other records should be rollback. And hence we did not involve any commit in the processing. But this resulted in the following error:</p>

<blockquote>
  <p>[2/1/16 14:01:47:939 CST] 000000be SystemOut     O ERROR
  org.springframework.jdbc.UncategorizedSQLException:
  PreparedStatementCallback; uncategorized SQLException for SQL [INSERT
  INTO ...) VALUES (...)]; SQL state [null]; error code [0]; Current
  thread has not commited in more than [120] seconds and may incur
  unwanted blocking locks. Please refactor code to commit more
  frequently.</p>
</blockquote>

<p>Now, when we implemented batching - We are using PreparedStatement.executeBatch() method to insert data in batches of 100, the above error doesn't arise. The autoCommit is set to true by default for the batching, so a commit happens after every batch execution.</p>

<p>Could anyone suggest how we can handle the rollback mechanism in the above case. If 50th batch execution fails, then we want all the previous 49 batch executions to be reverted. We are using Spring Data/JDBC, Oracle 11g database, WebSphere application server. I have read somewhere that the above 120 seconds timeout for commit can also be configured in the JTA settings of WebSphere. Is it so? Please suggest any alternatives or other possible solutions.</p>

<p>Thank you in advance.</p>