3

We are using hiberante,c3p0, postgresql for the persistent layer. While running data intensive jobs, mostly select/insert, one of the developers decided to use entityManager.flush() before commit , like so

entityManager.getTransaction().begin()
insert n elements
entityManager.flush()
entityManager.getTransaction().commit()

After a while all of the threads running the data intensive jobs were seemingly blocked, and we discovered they were waiting on database connections form the pool. All the connections on the pool where in "Idle in transaction" state. The situation could be replicated every time.

After removing flush() the situation disappeared.

Does anyone have any idea why would this happen?

Thanks :D

BL.
  • 89
  • 1
  • 6

1 Answers1

4

hibernate has an intelligent caching system which simply gathers a list of sql commands to execute and then it executes them upon commit. Flush serves to go ahead and partially execute these commands. It might seem that this would help, but when you consider that hibernate does not do anything until commit without flush, then you're causing the database to have to deal with large transactions in short bursts rather than all at once.

It would be the equivalent of handing over single sheets of paper to your work colleague to shred as opposed to letting him shred multiple sheets of paper at once. Combine this with the fact that the database may lock records in the middle of a transaction, from the moment you call flush the first time, the database dedicates itself to the task of executing commands until you commit. If the database isn't waiting on you and has all the commands at once, it can finish in the time it takes to do the actual work.

In short, hibernate knows what it's doing. Flush overrides hibernate's normal functioning and can actually diminish performance if you're not careful. You should probably only use flush if order is important (such as performing deletion prior to insert).

Neil
  • 5,762
  • 24
  • 36
  • 1
    So why does hibernate hangs in transaction when we flush? Shouldn't this be a bug? – ibrabeicker Apr 01 '14 at 18:22
  • 1
    @ibrabeicker Well if it hangs, then it is a bug. Though you should be sure that it isn't simply a taxing operation that takes time to finish and that it is actually hanging. If that is the case, there is likely some record which is locked and preventing your insert from completing. – Neil Apr 02 '14 at 09:41
  • So hibernate takes less than a second to insert 30 records using basic CrudRepository methods. It uses 10 seconds for 1000 records. At this scale it'll take over 10 days to insert 100 million records into one of my simple Oracle tables (It has one named index, and an integer primary key). I don't think hibernate is smart enough to do batch jobs out of the box. – obesechicken13 Nov 23 '15 at 22:01