2

I'm facing an issue with saving data to database using a Spring Data JPA repository.

My scenario: I'm using a loop to collect and save data one by one. It takes a lot of time to collect all the data. Therefore I want to save the data for each record immediately into table into the database. I'm using saveAndFlush method but the data is not being saved into the table immediately.

I cannot wait until all the data is collected because it maybe take a full day to collect all.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
Phương Nghi Phan Lý
  • 23
  • 1
  • 4

3 Answers3

3

I'm not 100% sure how you test that the data is not written immediately to the database, but I'm guessing you are checking with a separate database connection.

This probably means that your data in deed is written to the database, but not committed and therefore not visible for other sessions.

Make sure that the transaction scope is only inside the loop you are using for writing.

For this the method containing the for loop should not have a @Transactional annotation or in any other way included in a transaction, while the call to save is within a transaction. saveAndFlush is not necessary since the transaction commits and this will trigger a flush anyway.

If the call to save the only interaction with the database Spring will actually automatically wrap it in a transaction, because repositories are annotated with @Transactional out of the box. Otherwise you'll need to use the transaction support of Spring to achieve this.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • Yes, I removed @Transactional from the method. Thank you, Jens Schauder! – Phương Nghi Phan Lý Nov 06 '20 at 03:31
  • I'm happy the answer helped. The usual way to say thank you for a helpful answer is to upvote it (up arrow on the left side of the answer). If an answer actually solved the problem, consider marking it as the solution using the check mark. This make it visible to others that the question doesn't need more help anymore. – Jens Schauder Nov 07 '20 at 05:19
0

Maybe it's possible that your method has a @Transactional annotation, and maybe it waits for all entities to be ready in order to save it all at once in one transaction.

JPA has a 'saveAll' method where you can save a whole collection at once, I suggest you use this one so you don't have to send separate requests for each entity and increasing the DB workload and network bandwidth.

dextertron_
  • 921
  • 1
  • 10
  • 19
0

I dont think that saving each record one at a time is a good idea, though it is possible.

Flushing is the process of synchronizing the state of the persistence context with the underlying database. If something an expected happens the transaction will be rolled back persisting no data.

The naive solution to your case would be to use separate transactions(Propagation.REQUIRES_NEW). Note that this creates a huge performance hog. So, I personly recommend saving a number of records in a single transaction(batching). https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#batch

So, where ever you use the loop make sure that it runs in a separate transaction from the previous one.

OR

If you are using spring-data-jpa, there is even simpler way to process entities in a batch. You just need only two things done:

  1. In your property file, set the option spring.jpa.properties.hibernate.jdbc.batch_size=any_size
  2. Use saveAll() method of your repo with the list of entities prepared for inserting. Check here for more https://dzone.com/articles/50-best-performance-practices-for-hibernate-5-amp

Check the following images on how REQUIRES_NEW and REQUIRED do their job:

required

AND REQUIRES_NEW

requires_new

Aman
  • 1,627
  • 13
  • 19