Home > Net >  Spring Boot in JDBC: @Transactional is not working for multiple tables
Spring Boot in JDBC: @Transactional is not working for multiple tables

Time:10-12

I am trying to get @Transactional working in Spring Boot when updating for Multiple tables. This code is not rolling back on error. How can I fix this? I ran test case where Table 1 and 2 works, and Table3 fails. It should rollback on all 1, 2, 3. Are there any other syntax or annotations missing in the code?

@Transactional
public void createData() {
    updateTable1();
    updateTable2();
    updateTable3();
}

private updateTable1() {
    namedParameterJdbcTemplate.batchUpdate(TABLE1_INSERT_STRING, parameters1);
}

private updateTable2() {
    namedParameterJdbcTemplate.batchUpdate(TABLE2_INSERT_STRING, parameters2);
}

private updateTable3() {
    namedParameterJdbcTemplate.batchUpdate(TABLE3_INSERT_STRING, parameters3);
}

Application has @EnableTransactionManagement:

@EnableTransactionManagement
public class Application extends SpringBootServletInitializer {
    public static void main(String[] args) {
      

Attempt in application.yml

org:
  springframework:
    transaction:
      support: DEBUG
      interceptor: TRACE

CodePudding user response:

It should work based on what codes you posted.

Several things that you can check for further troubleshooting :

  1. Make sure you are using @Transactional from the org.springframework package but not the one from javax.transaction package

  2. Turn on and configure following loggers for more detailed logging to see if there are any insights :

    • org.springframework.transaction.interceptor to TRACE level
    • org.springframework.transaction.support to DEBUG level

Pay attention if TransactionInterceptor#completeTransactionAfterThrowing() will be invoked after the exception is thrown from the transactional method . It should be invoked to handle the rollback normally.

Also pay attention to see if there are some logs saying committing transaction before the transactional method returns. If yes , then something go wrong.

Show me what are logged if you are still struggling with it.

CodePudding user response:

I tried testing transaction behavior with jdbctemplate on a simple command line program with Spring Boot and in-memory database h2 - and it seems to be working as expected. Here is my code

ManagingTransactionsApplication



@SpringBootApplication
public class ManagingTransactionsApplication {

    public static void main(String[] args) {
        SpringApplication.run(ManagingTransactionsApplication.class, args);
    }

}


TransactionTestService


@Component
public class TransactionTestService {

    private final static Logger logger = LoggerFactory.getLogger(TransactionTestService.class);

    private final NamedParameterJdbcTemplate jdbcTemplate;

    public TransactionTestService(NamedParameterJdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Transactional
    public void bookInTransaction(String... persons) {
        insertInTable1(persons[0]);
        insertInTable2(persons[1]);
        insertInTable3(persons[2]);
    }

    public void insertInTable1(String person) {
        logger.info("Booking "   person   " in a seat...");
        Map<String, String> map = new HashMap<String, String>();
        map.put("FIRST_NAME", person);
        jdbcTemplate.update("insert into TABLE1(FIRST_NAME) values (:FIRST_NAME)", map);
    }

    public void insertInTable2(String person) {
        logger.info("Booking "   person   " in a seat...");
        Map<String, String> map = new HashMap<String, String>();
        map.put("FIRST_NAME", person);
        jdbcTemplate.update("insert into TABLE2(FIRST_NAME) values (:FIRST_NAME)", map);
    }

    public void insertInTable3(String person) {
        logger.info("Booking "   person   " in a seat...");
        Map<String, String> map = new HashMap<String, String>();
        map.put("FIRST_NAME", person);
        jdbcTemplate.update("insert into TABLE3(FIRST_NAME) values (:FIRST_NAME)", map);
    }

    public List<String> findAllBookings() {
        return jdbcTemplate.query("select FIRST_NAME from BOOKINGS", (rs, rowNum) -> rs.getString("FIRST_NAME"));
    }

}

AppRunner

@Component
class AppRunner implements CommandLineRunner {

    private final static Logger logger = LoggerFactory.getLogger(AppRunner.class);
    private final TransactionTestService transTestService;

    public AppRunner(BookingService bookingService, TransactionTestService transTestService) {
        this.transTestService = transTestService;
    }

    @Override
    public void run(String... args) throws Exception {

        try {
            transTestService.bookInTransaction("Alice", "Bob", "Shailendra");
        } catch (RuntimeException e) {
            logger.info("--- The following exception is expect because 'Shailendra' is too "   "big for the DB ---v");
            logger.error(e.getMessage());
        }

        for (String person : transTestService.findAllBookings()) {
            logger.info("So far, "   person   " is booked.");
        }

    }

}


schema.sql

drop table TABLE1 if exists;
create table TABLE1(ID serial, FIRST_NAME varchar(5) NOT NULL);

drop table TABLE2 if exists;
create table TABLE2(ID serial, FIRST_NAME varchar(5) NOT NULL);

drop table TABLE3 if exists;
create table TABLE3(ID serial, FIRST_NAME varchar(5) NOT NULL);

Basically I will do 3 inserts - with the last insert the value will exceeds max length of 5 and so the insert will fail and the overall transaction should also fail and on querying data it should result in none records saved. I get below on console and no data in database is found

2021-10-11 15:00:21.726  INFO 23860 --- [           main] c.e.m.TransactionTestService             : Booking Alice in a seat...
2021-10-11 15:00:21.746  INFO 23860 --- [           main] c.e.m.TransactionTestService             : Booking Bob in a seat...
2021-10-11 15:00:21.746  INFO 23860 --- [           main] c.e.m.TransactionTestService             : Booking Shailendra in a seat...
2021-10-11 15:00:21.909  INFO 23860 --- [           main] c.e.managingtransactions.AppRunner       : --- The following exception is expect because 'Shailendra' is too big for the DB ---v
2021-10-11 15:00:21.909 ERROR 23860 --- [           main] c.e.managingtransactions.AppRunner       : PreparedStatementCallback; SQL [insert into TABLE1(FIRST_NAME) values (?)]; Value too long for column "FIRST_NAME VARCHAR(5)": "'Shailendra' (10)"; SQL statement:
insert into TABLE1(FIRST_NAME) values (?) [22001-200]; nested exception is org.h2.jdbc.JdbcSQLDataException: Value too long for column "FIRST_NAME VARCHAR(5)": "'Shailendra' (10)"; SQL statement:
insert into TABLE1(FIRST_NAME) values (?) [22001-200]

On adding debug/trace for transaction below is more detailed information on transaction managed by Spring and it mentions the fact that it rolled back transaction

2021-10-11 15:22:22.734  INFO 23124 --- [           main] c.e.m.ManagingTransactionsApplication    : Started ManagingTransactionsApplication in 5.369 seconds (JVM running for 6.179)
2021-10-11 15:22:22.734 TRACE 23124 --- [           main] o.s.t.i.TransactionInterceptor           : Getting transaction for [com.example.managingtransactions.TransactionTestService.bookInTransaction]
2021-10-11 15:22:22.765  INFO 23124 --- [           main] c.e.m.TransactionTestService             : Booking Alice in a seat...
2021-10-11 15:22:22.787  INFO 23124 --- [           main] c.e.m.TransactionTestService             : Booking Bob in a seat...
2021-10-11 15:22:22.787  INFO 23124 --- [           main] c.e.m.TransactionTestService             : Booking Shailendra in a seat...
2021-10-11 15:22:22.939 TRACE 23124 --- [           main] o.s.t.i.TransactionInterceptor           : Completing transaction for [com.example.managingtransactions.TransactionTestService.bookInTransaction] after exception: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into TABLE1(FIRST_NAME) values (?)]; Value too long for column "FIRST_NAME VARCHAR(5)": "'Shailendra' (10)"; SQL statement:
insert into TABLE1(FIRST_NAME) values (?) [22001-200]; nested exception is org.h2.jdbc.JdbcSQLDataException: Value too long for column "FIRST_NAME VARCHAR(5)": "'Shailendra' (10)"; SQL statement:
insert into TABLE1(FIRST_NAME) values (?) [22001-200]
2021-10-11 15:22:22.941 TRACE 23124 --- [           main] **o.s.t.i.RuleBasedTransactionAttribute    : Applying rules to determine whether transaction should rollback on org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into TABLE1(FIRST_NAME) values (?)]; Value too long for column "FIRST_NAME VARCHAR(5)": "'Shailendra' (10)"; SQL statement:**
insert into TABLE1(FIRST_NAME) values (?) [22001-200]; nested exception is org.h2.jdbc.JdbcSQLDataException: Value too long for column "FIRST_NAME VARCHAR(5)": "'Shailendra' (10)"; SQL statement:
insert into TABLE1(FIRST_NAME) values (?) [22001-200]
2021-10-11 15:22:22.941 TRACE 23124 --- [           main] o.s.t.i.RuleBasedTransactionAttribute    : Winning rollback rule is: null
2021-10-11 15:22:22.941 TRACE 23124 --- [           main] **o.s.t.i.RuleBasedTransactionAttribute    : No relevant rollback rule found: applying default rules**
2021-10-11 15:22:22.941  INFO 23124 --- [           main] c.e.managingtransactions.AppRunner       : --- The following exception is expect because 'Shailendra' is too big for the DB ---v
2021-10-11 15:22:22.941 ERROR 23124 --- [           main] c.e.managingtransactions.AppRunner       : PreparedStatementCallback; SQL [insert into TABLE1(FIRST_NAME) values (?)]; Value too long for column "FIRST_NAME VARCHAR(5)": "'Shailendra' (10)"; SQL statement:
insert into TABLE1(FIRST_NAME) values (?) [22001-200]; nested exception is org.h2.jdbc.JdbcSQLDataException: Value too long for column "FIRST_NAME VARCHAR(5)": "'Shailendra' (10)"; SQL statement:
insert into TABLE1(FIRST_NAME) values (?) [22001-200]
  • Related