Stored procedure is executed twice when error is raised from DB.

Description

We are using spring boot JPA 3.2.2(did not specify any hibernate dependency manually in the pom file it is as part of the spring boot only). The issue is in hibernate-core-6.4.1.FINAL.jar. In a specific scenario we are facing an issue, stored procedures are executing twice.

We have a named stored procedure specified in an entity class

@NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(name = "Payment.recollectPayment",
        procedureName = "somepackage.procedure_name",
        parameters = {
            @StoredProcedureParameter(mode = ParameterMode.IN, name = "param_a", type = Long.class),
            @StoredProcedureParameter(mode = ParameterMode.IN, name = "param_b", type = Long.class),
            @StoredProcedureParameter(mode = ParameterMode.OUT, name = "out_param_a", type = Long.class),
            @StoredProcedureParameter(mode = ParameterMode.OUT, name = "out_param_b", type = Long.class)})})

and it is being declared in a JPA repository. We are using it in a service class.

@Procedure(procedureName = "somepackage.procedure_name")
Map<String, Object> recollectPayment(@Param("param_a") Long paramA,
    @Param("param_b") Long paramB);

In a specific scenario, we are raising an exception from the stored procedure. Upon receiving the exception/error from DB we observed that the stored procedure is being called again.

Below is the sequence of execution that we observed as part of this issue analysis

This doExecute method in JpaQueryExecution(This is spring data jpa calss) class is the starting point.

protected Object doExecute(AbstractJpaQuery jpaQuery, JpaParametersParameterAccessor accessor) {
    Assert.isInstanceOf(StoredProcedureJpaQuery.class, jpaQuery);
    StoredProcedureJpaQuery query = (StoredProcedureJpaQuery)jpaQuery;
    StoredProcedureQuery procedure = query.createQuery(accessor);

    Object var7;
    try {
      boolean returnsResultSet = procedure.execute();
      if (!returnsResultSet) {
        var7 = query.extractOutputValue(procedure);
        return var7;
      }

      if (!SurroundingTransactionDetectorMethodInterceptor.INSTANCE.isSurroundingTransactionActive()) {
        throw new InvalidDataAccessApiUsageException("You're trying to execute a @Procedure method without a surrounding transaction that keeps the connection open so that the ResultSet can actually be consumed; Make sure the consumer code uses @Transactional or any other way of declaring a (read-only) transaction");
      }

      var7 = this.collectionQuery ? procedure.getResultList() : procedure.getSingleResult();
    } finally {
      if (procedure instanceof AutoCloseable ac) {
        AutoCloseable var10001 = (AutoCloseable)procedure;

        try {
          ac.close();
        } catch (Exception var14) {
        }
      }

    }

    return var7;
  }
}

Upon receiving the error the close method in finally block is executed. (ProcedureCallImpl is the implementation class for StoredProcedureQuery and it is AutoCloseable).

These are close and getOutputs(inherited from its parent ProcedureCall) method from ProcedureCallImpl

default void close() {
  this.getOutputs().release();
}

public ProcedureOutputs getOutputs() {
    if (this.outputs == null) {
      this.outputs = this.buildOutputs();
    }

    return this.outputs;
  }

this buildOutputs has the logic to initialize ProcedureOutputsImpl, its initialization logic triggers triggering stored procedure to execute again.

return new ProcedureOutputsImpl(this, parameterRegistrations, (JdbcCallRefCursorExtractor[])refCursorExtractors.toArray(new JdbcCallRefCursorExtractor[0]), statement);

Is there any open ticket for this already?

Attachments

17 Oct 2024, 04:50 PM

Activity

Show:

VenkataPrasad Tappala November 7, 2024 at 5:50 AM
Edited

Hi, is this verified on your end?

Gavin King October 17, 2024 at 6:42 PM

thanks

VenkataPrasad Tappala October 17, 2024 at 4:54 PM

To reproduce the issue, I have created a simple maven jar project with only one Java file and attached it to this ticket.

I have used only Hibernate APIs and the H2 in-memory database.

Gavin King October 16, 2024 at 8:58 AM

Please submit an isolated, simple, easily-runnable test case which involves only Hibernate APIs and is clean of Spring stuff.

Details

Assignee

Marco Belladelli

Reporter

VenkataPrasad Tappala

Labels

Components

hibernate-core

Sprint

None

Affects versions

7.0.0.Beta1

6.6.2

Priority

Major

Created October 15, 2024 at 1:09 PM

Updated February 25, 2025 at 1:32 PM

Configure