DataAccessResourceFailureException thrown when transaction times out on PostgreSQL #35073
Description
Environment
- JDK 21
- Spring Boot 3.4.6 + 3.5.0
- PostgreSQL 16.9 (installed with Homebrew)
- macOS 15.5
Reproducible code
What happens
My repository class:
@Repository public class SampleRepository { private final JdbcTemplate jdbcTemplate; public SampleRepository(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } public List<Sample> selectAll() { return jdbcTemplate.query("SELECT id, name FROM sample ORDER BY id", new DataClassRowMapper<>(Sample.class)); } public Object sleep(int seconds) { return jdbcTemplate.queryForObject("SELECT pg_sleep(?)", new Object[]{seconds}, Object.class); } public void insert(Sample sample) { jdbcTemplate.update("INSERT INTO sample(id, name) VALUES (?, ?)", sample.id(), sample.name()); } }My service class. registerWithSleep() will timed out in 2 seconds.
@Service public class SampleService { private static final Logger logger = LoggerFactory.getLogger(SampleService.class); private final SampleRepository sampleRepository; public SampleService(SampleRepository sampleRepository) { this.sampleRepository = sampleRepository; } @Transactional(timeout = 2, readOnly = false) public void registerWithSleep(Sample sample, int seconds) { logger.info("Sleep {}seconds...", seconds); sampleRepository.sleep(seconds); logger.info("Sleep completed. Starting INSERT..."); sampleRepository.insert(sample) ; logger.info("INSERT completed."); } }This method times out in 2 seconds as I expected, but throws DataAccessResourceFailureException.
Expected behavior
TransactionTimedOutException is thrown?
I could see
QueryTimeoutExceptionis thrown on MySQL 8.0.
Why does it behave like this?
When the transaction times out, PostgreSQL returns the SQL state "57014" (query_cancelled).
PostgreSQL JDBC Driver cancells a query with
TimerTask(see here)
Since sql-error-codes.xml does not contain "57014", Spring fallbacks to SQLStateSQLExceptionTranslator that has "57" (DB2: out-of-memory exception / database not started) in DATA_ACCESS_RESOURCE_FAILURE_CODES.