# Spring + Hibernate + Oracle StoredProcedure with commit

**URL:** <https://discourse.hibernate.org/t/spring-hibernate-oracle-storedprocedure-with-commit/7097>\
**Category:** Hibernate ORM\
**Created:** [January 4, 2023, 8:29am UTC](https://discourse.hibernate.org/t/spring-hibernate-oracle-storedprocedure-with-commit/7097 "2023-01-04T08:29:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![floss.dev](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/floss.dev/32/2192_2.png) [@floss.dev](https://discourse.hibernate.org/u/floss.dev)\
**Post date:** [January 4, 2023, 8:29am UTC](https://discourse.hibernate.org/t/spring-hibernate-oracle-storedprocedure-with-commit/7097/1 "2023-01-04T08:29:50Z")

</div>

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):

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

```

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [January 4, 2023, 7:28pm UTC](https://discourse.hibernate.org/t/spring-hibernate-oracle-storedprocedure-with-commit/7097/2 "2023-01-04T19:28:10Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![floss.dev](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/floss.dev/32/2192_2.png) [@floss.dev](https://discourse.hibernate.org/u/floss.dev)\
**Post date:** [March 10, 2023, 9:06pm UTC](https://discourse.hibernate.org/t/spring-hibernate-oracle-storedprocedure-with-commit/7097/3 "2023-03-10T21:06:34Z")

</div>

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