Spring + Hibernate + Oracle StoredProcedure with commit


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:

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:password="${database.password}" />
    <!--  El entity manager factory  -->
    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
        p:data-source-ref="dataSource" >
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"
        <property name="jpaProperties">
               <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>

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

    <!-- 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" />

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.