Spring + Hibernate + Oracle StoredProcedure with commit

Hibernate ORM
1

Hello

I have a Spring project, with Hibernate as ORM and Oracle as DB.
Transaction management works as expected, though I found that use case:

  1. Transactional service:
    • Calls oRepositoryX.insert() (Entity01)
      • Calls oRepositoryZ.callDbStoredProcedure(). In the DB StoredProcedure:
        • Insert a row in Entity02
        • Executes a COMMIT; (still in StoredProdure)
        • Insert another row in Entity02
      • Extra Java code executes on the service, and an Exception is thrown (and rollback performed)

Due to the rollback, I didn’t expect to find anything on Entity01/Entity02, BUT:

  • First row inserted into Entity02 is there
  • Rollback only affected to Entity01 row and the second insert into Entity02

It is like the commit on the PL keeps out of the main transaction, and it doesn’t allow the rollback of the first row inserted in the SP

Perhaps I was wrong, and this behaviour is OK… Or am I missing something? (Spring misconfiguration?)
What do you think?

I made this tests also:

  1. If I comment out the commit on the SP, a full rollback is executed (as expected)
    • This makes me bear that the StoredProcedure is running under the same Tx that the Java service…
  2. If no exception is thrown, all rows are in the DB (commit OK)

Extra info:

  • Spring: 6.0.0
  • Spring-data-jpa: 3.0.0
  • Hibernate-core-jakarta: 5.6.10
  • ojdbc10:19.12.0.0

Any help/insight/comment is welcome
Thanks in advance

P.S.: No autonomous transaction defined on SP (PRAGMA)

Spring configuration (xml):

    <!-- Db config --> 
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"          
        p:driverClassName="${database.driverClassName}"                                                                 
        p:url="${database.url}"
        p:username="${database.username}"
        p:password="${database.password}" />
    
    <!--  El entity manager factory  -->
    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
        p:persistence-xml-location="classpath:test_persistence.xml" 
        p:data-source-ref="dataSource" >
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"
                p:showSql="true"
                p:generateDdl="false">
            </bean>
        </property> 
        <property name="jpaProperties">
            <props>                             
               <prop key="hibernate.dialect">${hibernate.dialect}</prop>
                <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
                <prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
                <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>                
                <prop key="hibernate.hbm2ddl.import_files">${hibernate.hbm2ddl.import_files}</prop> 
                <prop key="hibernate.jdbc.batch_size">${hibernate.jdbc.batch_size}</prop>
                <prop key="hibernate.order_inserts">true</prop>
                <prop key="hibernate.order_updates">true</prop>
            </props>  
        </property>
    </bean>

    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory" />
    </bean>

    <!-- Enable transaction demarcation with annotations -->
    <tx:annotation-driven />

    <!-- Services -->
    <context:component-scan base-package="com.myapp.srv"/>

   <!--  Spring Data Repositories --> 
    <data:repositories base-package="com.myapp.srv" transaction-manager-ref="transactionManager" />
2

I think this is something that you should ask on Oracle forums, but I believe that stored procedures create sub-transactions if you use COMMIT explicitly.

3

After some tests, this is what I think is happening:

With Hibernate + Spring Jpa, all SQL commands (almost insert, update and deletes) are only sent to DB when @Transactional code ends.

But, if some “nativeQuery” annotated SQL or SP is called, it is sent directly to DB (not at the end of @Transactional). So, if a “commit” is executed somewhere on the SP/PL, it only affects that DB code. No the one managed by Hibernate/Spring JPA (still not sent to DB. Almost, until the end of the @Transactional code block)

So, best bet: I will let Spring manage transactions, and remove commits/rollbacks from Stored Procedures