Roll back A if B goes wrong. spring boot, jdbctemplate

Question

I have a method, 'databaseChanges', which call 2 operations: A, B in iterative way. 'A' first, 'B' last. 'A' & 'B' can be Create, Update Delete functionalities in my persistent storage, Oracle Database 11g.

Let's say,

'A' update a record in table Users, attribute zip, where id = 1.

'B' insert a record in table hobbies.

Scenario: databaseChanges method is been called, 'A' operates and update the record. 'B' operates and try to insert a record, something happen, an exception is been thrown, the exception is bubbling to the databaseChanges method.

Expected: 'A' and 'B' didn't change nothing. the update which 'A' did, will be rollback. 'B' didn't changed nothing, well... there was an exception.

Actual: 'A' update seems to not been rolled back. 'B' didn't changed nothing, well... there was an exception.

---

Some Code

If i had the connection, i would do something like:

private void databaseChanges(Connection conn) {
   try {
          conn.setAutoCommit(false);
          A(); //update.
          B(); //insert
          conn.commit();
   } catch (Exception e) { 
        try {
              conn.rollback();
        } catch (Exception ei) {
                    //logs...
        }
   } finally {
          conn.setAutoCommit(true);
   }
}

The problem: I don't have the connection (see the Tags that post with the question)

I tried to:

@Service
public class SomeService implements ISomeService {
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    @Autowired
    private NamedParameterJdbcTemplate npjt;

    @Transactional
    private void databaseChanges() throws Exception {   
        A(); //update.
        B(); //insert
    }
}

---

My AppConfig class:

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

@Configuration
public class AppConfig {    
    @Autowired
    private DataSource dataSource;

    @Bean
    public NamedParameterJdbcTemplate namedParameterJdbcTemplate() {
        return new NamedParameterJdbcTemplate(dataSource);
    }   
}

---

'A' makes the update. from 'B' an exception is been thrown. The update which been made by 'A' is not been rolled back.

From what i read, i understand that i'm not using the @Transactional correctly. I read and tried several blogs posts and stackverflow Q & A without succeess to solve my problem.

Any suggestions?

---

EDIT

There is a method that call databaseChanges() method

public void changes() throws Exception {
    someLogicBefore();
    databaseChanges();
    someLogicAfter();
}

Which method should be annotated with @Transactional,

changes()? databaseChanges()?

Answer 1

@Transactional annotation in spring works by wrapping your object in a proxy which in turn wraps methods annotated with @Transactional in a transaction. Because of that annotation will not work on private methods (as in your example) because private methods can't be inherited => they can't be wrapped (this is not true if you use declarative transactions with aspectj, then proxy-related caveats below don't apply).

Here is basic explanation of how @Transactional spring magic works.

You wrote:

class A {
    @Transactional
    public void method() {
    }
}

But this is what you actually get when you inject a bean:

class ProxiedA extends A {
   private final A a;

   public ProxiedA(A a) {
       this.a = a;
   }

   @Override
   public void method() {
       try {
           // open transaction ...
           a.method();
           // commit transaction
       } catch (RuntimeException e) {
           // rollback transaction
       } catch (Exception e) {
           // commit transaction
       }
   }
} 

This has limitations. They don't work with @PostConstruct methods because they are called before object is proxied. And even if you configured all correctly, transactions are only rolled back on unchecked exceptions by default. Use @Transactional(rollbackFor={CustomCheckedException.class}) if you need rollback on some checked exception.

Another frequently encountered caveat I know:

@Transactional method will only work if you call it "from outside", in following example b() will not be wrapped in transaction:

class X {
   public void a() {
      b();
   }

   @Transactional
   public void b() {
   }
}

It is also because @Transactional works by proxying your object. In example above a() will call X.b() not a enhanced "spring proxy" method b() so there will be no transaction. As a workaround you have to call b() from another bean.

When you encountered any of these caveats and can't use a suggested workaround (make method non-private or call b() from another bean) you can use TransactionTemplate instead of declarative transactions:

public class A {
    @Autowired
    TransactionTemplate transactionTemplate;

    public void method() {
        transactionTemplate.execute(status -> {
            A();
            B();
            return null;
        });
    }

...
} 

Update

Answering to OP updated question using info above.

Which method should be annotated with @Transactional: changes()? databaseChanges()?

@Transactional(rollbackFor={Exception.class})
public void changes() throws Exception {
    someLogicBefore();
    databaseChanges();
    someLogicAfter();
}

Make sure changes() is called "from outside" of a bean, not from class itself and after context was instantiated (e.g. this is not afterPropertiesSet() or @PostConstruct annotated method). Understand that spring rollbacks transaction only for unchecked exceptions by default (try to be more specific in rollbackFor checked exceptions list).