Does JPA call a stored procedure in the same transaction?

Question

I have a stored procedure that does not handle transactions (no commit, rollback) and I call it from a method like this:

@Transactional
void work(Long id) {
   var entity = (id == null) ? create() || fetch(id);
   process(entity);
   repository.callProcedure(id);
} 

This code works fine for existing entities but it fails on a newly created entities. It seems that Oracle procedure does not see uncommitted data. I believed that the procedure shall share the same transaction and so it can actually see uncommitted data. I would prefer to have a single transaction, so if the procedure fails, Spring will rollback changes made both in Java and the procedure.

Answer 1

Based on your answer to my comment above (flush is a workaround, why is it needed) I assume that it helped. You didn't share your code, but I assume that create does something like:

entityManager.persist(myNewEntity);

This creates the entity in memory and will create it in the database eventually, but there is nothing that forces JPA to execute the insert immediately. In general, JPA will wait until commit, manual flush or an automatic flush issued before a query (where JPA knows the flush is needed for correct results).

When you make your own database queries/updates outside of JPA, you first need to flush any pending changes. That is what flush is for.

Don't flush "just in case" as it hurts performance, but in this case you know that you have pending changes (you just created the entity) that the procedure needs to see. In that situation, flush is the way to go.