0

I'm using spring data jpa with @EnableJpaRepositories and spring retry with @EnableRetry.

I want to retry a transactional method that calls another transactional method that acquires an Oracle DBMS_LOCK using a wrapper stored proc: MY_PROC_THAT_CALLS_ORACLE_DBMS_LOCK.

The lock acquired is set up to be released upon transaction commit and that is what I want.

But despite successfully acquiring the lock on, say, the second attempt, my transaction is getting marked as rollback_only and is failing.

I suspect the @Transactional annotation on my acquireDbmsLock method in my Repository is seeing the SQLException and setting the transaction to roll back only.

From my understanding of the transaction propogation semantics, I think I don't want @Transactional(propogation = Propogation.REQUIRES_NEW) on the acquireDbmsLock interface method. Looks like Hibernate doesn't support Propogation.NESTED.

So, what is a good solution for this problem?

I'm resisting setting noRollbackFor = SQLException.class.

Here's my code:

@Retriable(SQLException.class)
@Transactional
public MyThing myService(String thingId) {
  MyThing myThing = myRepository.findMyThing(thingId);

  if(myThing == null) {
    myLockRepository.acquireDbmsLock(thingId);
    myRepository.createMyThing(thingId);
  } else {
    return myThing;
  }
}

public interface MyLockRepository extends Repository<XXX, YY> {

  @Procedure(procedureName = "MY_PROC_THAT_CALLS_ORACLE_DBMS_LOCK")
  void acquireDbmsLock(String lockName);
}

I've tried overriding the default retry configuration as shown below as well (after removing the @EnableRetry annotation):

@Bean
  RetryConfiguration retryConfiguration() {
    // Need retries to happen before transaction is set to rollback only: https://stackoverflow.com/a/53654301/499635
    return new RetryConfiguration() {
      @Override
      public int getOrder() {
       return Ordered.HIGHEST_PRECEDENCE;
      }
    };
  }
nemo
  • 1,504
  • 3
  • 21
  • 41
  • Actually, setting `noRollbackFor` on `acquireDbmsLock`'s `@Transactional` didn't help either. The transaction is still being marked as `rollback-only`. – nemo May 11 '19 at 20:43
  • I'm reading that methods other than the CRUD methods aren't transactional, but my transactions are still getting marked `rollback-only` despite me taking off the `@Transactional` on the `acquireDbmsLock` method. How do I force a spring-data-jpa generated method to be non tranasctional? – nemo May 13 '19 at 20:48

0 Answers0