Environment

• JDK 21
• Spring Boot 3.4.6 + 3.5.0
• PostgreSQL 16.9 (installed with Homebrew)
• macOS 15.5

Reproducible code

https://github.com/MasatoshiTada/transaction-timeout-sample-jdbc/blob/main/src/test/java/com/example/transactiontimeoutsamplejdbc/SampleServiceTest.java

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.

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.