When is @Transactional required when calling a stored procedure?-CodePudding

I have SQL Server stored procedure as follows:

procedure [dbo].[perf] (
  @endDate date,
  @startDate date,
  @productName varchar(20),
  @endPrice decimal(10,2) output,
  @startPrice decimal(10,2) output,
  @startEv decimal(10,3) output,
  @endEv decimal(10,3) output) as 

BEGIN 

--TSQL code

END

If I call this method without @Transactional:

public interface PriceRepository extends Repository<Price, Long> {

    @Procedure(name = "Price.perf")
    Map<String, Object> getStartPrice(@Param("endDate") LocalDate endDate, @Param("startDate") LocalDate startDate, @Param("productName") String productName);
    
}

I get the following error:

org.springframework.dao.InvalidDataAccessApiUsageException: OUT/INOUT parameter not available: endPrice

If I annotate getStartPrice with @Transactional, it works without error as noted in this post. Why and when is it necessary to make the call transactional?

Update

It appears all stored procedures must be called with @Transactional. If the stored procedure just ends with return:

procedure [dbo].[perf] (
  @endDate date,
  @startDate date,
  @productName varchar(20),
  @endPrice decimal(10,2) output,
  @startPrice decimal(10,2) output,
  @startEv decimal(10,3) output,
  @endEv decimal(10,3) output) as 

BEGIN 

--TSQL code
  
  return
END

then @Transactional can be on the Repository method. Without it, you get the error as I noted above. However, if the stored procedure ends with a select:

procedure [dbo].[perf] (
  @endDate date,
  @startDate date,
  @productName varchar(20),
  @endPrice decimal(10,2) output,
  @startPrice decimal(10,2) output,
  @startEv decimal(10,3) output,
  @endEv decimal(10,3) output) as 

BEGIN 

--TSQL code
  
  select * from someTable;
END

Then you must use @Transactional on some method calling the repository method. If you don't use @Transactional though, you get a more descriptive error stating:

Caused by: org.springframework.dao.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

Why do these differences exist depending on how the stored procedure ends?

CodePudding user response：

According to Vlad Mihalcea, a Hibernate maintainer, after calling a stored procedure

by default, the CallableStatement is closed upon ending the currently running database transaction, either via calling commit or rollback.

This means that without a wrapping transaction, the underlying CallableStatement of the prepared statement could remain open which is bad from a resource usage view and if Oracle is used, it can even lead to ORA-01000: maximum open cursors exceeded issues.

Citing Vlad Mihalcea:

Releasing the underlying JDBC CallableStatement as soon as possible is very important when calling a stored procedure with JPA and Hibernate, as otherwise, the database cursors will be open until the current transaction is committed or rolled back.

That is the reason why you should wrap the code that is calling the procedure with @Transactional. If it fixes another exception in your code - even better!

CodePudding user response：

When writing the same code in JDBC, you don't need to wrap calls to read-only procedures into transaction, and Statement is a Closeable and putting it in a try-with-resource will auto close it. So the only logical conclusion is that the need of wrapping into a transaction when using the interface Repository is due to the layer cake of calls introduced by Spring and Hibernate to do the same job, complicating how the results are given back to the original caller because the code creating the proxy based on the Repository interface has no way to know how the caller will consume the data. The only thing the JDBC layer can tell is that there is a ResultSet pending to be consumed or not (hence the difference of behavior with "return" or ending with a "select..." with MS-SQLServer or DBMS_SQL.RETURN_RESULT(cursor) and "? := cursor;" with ORACLE).