javax.persistence.TransactionRequiredException while performing inserts in a loop

Question

I have an application which uses Spring, Hibernate and JTA.

We received a request with larger than expected data (10000 elements), general requests are 10-100 elements.

While processing this request we try to insert a record in DB for each of these elements and that's done in a for loop

pseudo code:

processRecords( list){

for (Element element: list){

dao.findBy -- This takes around 100 ms

-- some other checks and logic

dao.add(element); -- This takes around 150 ms
dao.flush();
 }

}

This block takes a lot of time to process the records and then I get *

"javax.persistence.TransactionRequiredException: no transaction is in progress"

*

I tried moving the flush out of the for loop, did not help, I tried researching on batch inserts for hibernate , but this is a huge application with lots of customization and I do not see that as an option as it will impact whole application, Also I tried finding where is the Transaction duration configured and only place I could find was in JTA on weblogic and it was set to 480 Sec.

Any indicators on how to solve this scenario would be much appreciated.

Edit: increasing the JTA Timeout in weblogic has solved the problem temporarily, but I set it to a very large value 5000 sec, is there anyway to improve the performance, since I am just inserting 8K records (I know batch processing is one option, but there are some constraint in custom "framework" around that)

Answer 1

Use @Transactional to control the transactions.

1. Make sure to not automatically start a transaction with PROPAGATION_NEVER
2. Start new transactions for each item in list (or subset of items in list) by calling a service method with REQUIRES_NEW.

In the second step, you might want to call on another bean so that the transaction annotation is actually picked up, can't remember whether this works on methods on the same object.

Answer 2

Based on your inline commments, your transaction duration is 2,500 seconds, of which 1,000 seconds is reading the database, and 1,500 is adding to the database.

You may reduce the database transaction by restructuring your logic a little bit: In most database drivers, the database doesn't actually start a transaction until the first "write" to the database. You can leverage this by restructuring your logic to do all the reads first, then do all the writes. I've had success with this approach with non-JPA data access, but have not tried it with JPA. The restructured pseudocode is something like this:

processRecords( list){

// do all the reads
List adds=new ...
for (Element element: list){
  dao.findBy -- This takes around 100 ms
  -- some other checks and logic
  adds.put(element);
}

// do all the writes
for (Element element: adds){
  dao.add(element); -- This takes around 150 ms
  dao.flush();
 }

}

Use a database trace to validate this is working as expected. In addition to reducing the transaction duration, this approach reduces the risk of blocking due to a read on another thread.