The 
Attempt 2:
private TransactionTemplate transactionTemplate;
public TestRepository(PlatformTransactionManager transactionManager) {
this.transactionTemplate = new TransactionTemplate(transactionManager);
}
public void createData() {
transactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
transactionTemplate.execute(status -> {
updateTable1();
updateTable2();
updateTable3();
} catch (Exception exception) {
exception.printStackTrace();
status.setRollbackOnly();
throw new RuntimeException(exception);
}
Attempt 2 Logging:
===================================
2021-10-11 17:36:54 TRACE
TraceId: bba0ffd336161184
CustomerId:
CLASS: org.springframework.transaction.interceptor.TransactionInterceptor
Completing transaction for [com.abccompany.services.service.charge.capture.server.facade.EncounterFacade.createEncounter] after exception: java.lang.RuntimeException: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO dbo.EncounterProcedure(EncounterID, ProcedureCodeDictionaryID, ServiceChargeAmount, AnesthesiaTime, ServiceUnitCount, ProcedureModifier1, ProcedureModifier2, ProcedureModifier3, ProcedureModifier4, ServiceEndDate, ProcedureDateOfService, PracticeID) VALUES (?, ? , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; Data truncation; nested exception is java.sql.BatchUpdateException: Data truncation
===================================
2021-10-11 17:36:54 TRACE
TraceId: bba0ffd336161184
CustomerId:
CLASS: org.springframework.transaction.interceptor.RuleBasedTransactionAttribute
Applying rules to determine whether transaction should rollback on java.lang.RuntimeException: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO dbo.EncounterProcedure(EncounterID, ProcedureCodeDictionaryID, ServiceChargeAmount, AnesthesiaTime, ServiceUnitCount, ProcedureModifier1, ProcedureModifier2, ProcedureModifier3, ProcedureModifier4, ServiceEndDate, ProcedureDateOfService, PracticeID) VALUES (?, ? , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; Data truncation; nested exception is java.sql.BatchUpdateException: Data truncation
===================================
2021-10-11 17:36:54 TRACE
TraceId: bba0ffd336161184
CustomerId:
CLASS: org.springframework.transaction.interceptor.RuleBasedTransactionAttribute
Winning rollback rule is: null
===================================
2021-10-11 17:36:54 TRACE
TraceId: bba0ffd336161184
CustomerId:
CLASS: org.springframework.transaction.interceptor.RuleBasedTransactionAttribute
No relevant rollback rule found: applying default rules
CodePudding user response：
It should work based on what codes you posted.
Several things that you can check for further troubleshooting :
Make sure you are using @Transactional from the org.springframework package but not the one from javax.transaction package
Turn on and configure following loggers for more detailed logging to see if there are any insights :
org.springframework.transaction.interceptor to TRACE levelorg.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
2021-10-11 15:00:21.746 INFO 23860
2021-10-11 15:00:21.746 INFO 23860
2021-10-11 15:00:21.909 INFO 23860
2021-10-11 15:00:21.909 ERROR 23860
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
2021-10-11 15:22:22.734 TRACE 23124
2021-10-11 15:22:22.765 INFO 23124
2021-10-11 15:22:22.787 INFO 23124
2021-10-11 15:22:22.787 INFO 23124
2021-10-11 15:22:22.939 TRACE 23124
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
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
2021-10-11 15:22:22.941 TRACE 23124
2021-10-11 15:22:22.941 INFO 23124
2021-10-11 15:22:22.941 ERROR 23124
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]
Update:
Even if I use batchUpdate method the results remains the same
SqlParameterSource[] params = new SqlParameterSource[] {
new MapSqlParameterSource("FIRST_NAME", 1),
};
jdbcTemplate.batchUpdate("insert into TABLE1(FIRST_NAME) values (:FIRST_NAME)", params);
Corresponding logs
2021-10-12 00:12:42.075 TRACE 22352
2021-10-12 00:12:42.094 INFO 22352
2021-10-12 00:12:42.134 INFO 22352
2021-10-12 00:12:42.134 INFO 22352
2021-10-12 00:12:42.385 TRACE 22352
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-12 00:12:42.385 TRACE 22352
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-12 00:12:42.385 TRACE 22352
2021-10-12 00:12:42.385 TRACE 22352
2021-10-12 00:12:42.385 INFO 22352
2021-10-12 00:12:42.385 ERROR 22352
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]
application.yml
logging:
level:
org.springframework:
transaction:
support: DEBUG
interceptor: TRACE
