How to handle Exceptions in Repository?

Using Spring 5.3 I have a repository

@Transactional(TransactionMode.REQUIRES_NEW)
public class Repository {
  public List<Object> hasData() {
    ...
    return entityManager.createQuery(query).getResultList();
  }
}

This method is called quite frequently and the database connection has to be assumed as non-reliable. So the code has to consider the possibility, the query fails because the database connection is closed. The desired behavior in this case is to return an empty Collection.

I have wrapped the call within the repo like

@Transactional(TransactionMode.REQUIRES_NEW)
public class Repository {
  public List<Object> hasData2() {
    try {
      return hasData();
    } catch (RuntimeException e) {
      // do some logging
    }
  }


  public List<Object> hasData() {
    ...
    return entityManager.createQuery(query).getResultList();
  }
}

However, when the hibernate call fails, hibernate marks the connection as read-only. Consequently, the spring code closing the transaction will fail with an UnexpectedRollbackException.

My next step is either wrapping this Repository into a second one, only concerned catching the UnexpectedRollbackException or by wrapping every call of this repository.

Both solutions seem wrong somehow.

Is there any way to prevent Spring from throwing the UnexpectedRollbackException or hibernate from setting the Transaction to rollback-only? What is the pattern when a failure of the database is not an exception but an accepted state (form the POV of the caller)?

In general, marking the transaction as rollback-only is the expected behavior in case of a runtime exception occurring during a transactional method. The purpose of the transactional annotation is to provide a consistent and atomic execution of a set of database operations, and if an error occurs, it's usually safer to roll back the transaction and start over rather than continuing with potentially inconsistent data.

However, if you want to avoid the UnexpectedRollbackException being thrown, one possible solution would be to catch the exception in your calling code and handle it there.
For example:

public List<Object> someMethod() {
  try {
    return repository.hasData2();
  } catch (UnexpectedRollbackException e) {
    // handle the exception and return an empty collection
    return Collections.emptyList();
  }
}

Another option would be to configure Spring to treat the UnexpectedRollbackException as a non-fatal exception, which would prevent it from being propagated to the calling code. You can do this by adding the following to your Spring configuration:

<bean id="transactionAttributeSource" class="org.springframework.transaction.annotation.AnnotationTransactionAttributeSource"/>
<bean id="transactionInterceptor" class="org.springframework.transaction.interceptor.TransactionInterceptor">
  <property name="transactionManager" ref="transactionManager"/>
  <property name="transactionAttributeSource" ref="transactionAttributeSource"/>
  <property name="rollbackOn" value="RuntimeException"/>
  <property name="nonTransactionalMethod" value="readOnly"/>
</bean>
<aop:config>
  <aop:advisor advice-ref="transactionInterceptor" pointcut="execution(* com.example.*.*(..))"/>
</aop:config>

This configuration will cause the transaction to be rolled back only if a RuntimeException is thrown explicitly (such as by calling throw new RuntimeException()), rather than for all runtime exceptions including the UnexpectedRollbackException.