Investigating Spring Transaction Timeouts

Environment

• JDK 21
• Spring Boot 3.4.6 & 3.5.0
  • Verified with MyBatis and JdbcTemplate
• PostgreSQL 16
• MySQL 8.0
• macOS 15.5

Summary for Busy People

• The default transaction timeout for the entire application can be configured in application.properties as spring.transaction.default-timeout=60s.
• Individual transaction timeouts can be set using @Transactional(timeout = 30).
• An exception is thrown immediately after the timeout expires.
  • For PostgreSQL: org.springframework.dao.DataAccessResourceFailureException
    • This will be corrected to org.springframework.dao.QueryTimeoutException in Spring Boot 3.5.1 or 3.5.2.
  • For MySQL: org.springframework.dao.QueryTimeoutException

If you are using an RDBMS other than PostgreSQL or MySQL, I recommend testing its behavior thoroughly.

Objective

Using something like @Transactional(timeout = 3) causes the transaction to timeout (in this example, after 3 seconds). I knew this in theory, but I wanted to verify how it actually behaves, so I decided to test it.

Sample Code

Database

schema.sql

DROP TABLE IF EXISTS sample;

CREATE TABLE sample(
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

data.sql

INSERT INTO sample(id, name)
VALUES (1, 'Sample1'),
       (2, 'Sample2'),
       (3, 'Sample3');

Configuration

The settings for both PostgreSQL and MySQL are provided. Switch between them by commenting/uncommenting as needed.

The default transaction timeout for the entire application is set like spring.transaction.default-timeout=5s.

application.properties

spring.application.name=transaction-timeout-sample-jdbc

# Transaction timeout
spring.transaction.default-timeout=5s

# PostgreSQL
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=

# MySQL
#spring.datasource.url=jdbc:mysql://localhost:3306/sample
#spring.datasource.username=user
#spring.datasource.password=password

# Run schema.sql and data.sql on startup
spring.sql.init.mode=always

# Logging
logging.level.org.apache.ibatis=trace
logging.level.org.mybatis=trace
logging.level.org.springframework=trace
logging.level.org.springframework.boot=info
logging.level.org.springframework.beans=info
logging.level.org.springframework.context=info
logging.level.org.springframework.core=info
logging.level.org.postgresql=trace
logging.level.com.mysql=trace
logging.level.com.example.transactiontimeoutsamplejdbc=trace

Java Code

Sample.java

public record Sample(Integer id, String name) {
}

The sleep functions differ between PostgreSQL and MySQL, so they are commented out. Switch them as needed.

SampleRepository.java

@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) {
    // PostgreSQL
    return jdbcTemplate.queryForObject("SELECT pg_sleep(?)", new Object[]{seconds}, Object.class);
    // MySQL
//    return jdbcTemplate.queryForObject("SELECT sleep(?)", new Object[]{seconds}, Object.class);
  }

  public void insert(Sample sample) {
    jdbcTemplate.update("INSERT INTO sample(id, name) VALUES (?, ?)", sample.id(), sample.name());
  }
}

Service Class

Transactions are controlled here.

SampleService.java

@Service
public class SampleService {
  private static final Logger logger = LoggerFactory.getLogger(SampleService.class);

  private final SampleRepository sampleRepository;

  public SampleService(SampleRepository sampleRepository) {
    this.sampleRepository = sampleRepository;
  }

  // Timeout in 2 seconds
  @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.");
  }
}

Test Code

Test code for both PostgreSQL and MySQL is provided. Swap the @Disabled annotation as needed.

This test passes, which means:

• In both PostgreSQL and MySQL, an exception is thrown immediately once the timeout period has elapsed.
• In PostgreSQL, DataAccessResourceFailureException is thrown.
• In MySQL, QueryTimeoutException is thrown.

SampleServiceTest.java

@SpringBootTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@Sql(
    scripts = {"classpath:schema.sql", "classpath:data.sql"},
    executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD
)
public class SampleServiceTest {
  @Autowired
  SampleService sampleService;

  @Nested
  @DisplayName("registerWithSleep()")
  class RegisterWithSleepTest {
    // For PostgreSQL
    @Test
    @DisplayName("DataAccessResourceFailureException in 2 seconds on PostgreSQL")
    void postgres() {
      long startTime = System.currentTimeMillis();
      DataAccessResourceFailureException exception = assertThrows(
          DataAccessResourceFailureException.class, () -> {
            sampleService.registerWithSleep(new Sample(4, "Sample4"), 3);  // timeout = 2
          }
      );
      // PostgreSQL error code indicating query cancellation
      // see https://www.postgresql.org/docs/16/errcodes-appendix.html
      assertEquals("57014", ((SQLException) exception.getCause()).getSQLState());
      long processSeconds = (System.currentTimeMillis() - startTime) / 1000;
      assertEquals(2, processSeconds);  // timeout should be 2 seconds
    }

    // For MySQL
    @Disabled  // Swap as needed
    @Test
    @DisplayName("QueryTimeoutException in 2 seconds on MySQL")
    void mysql() {
      long startTime = System.currentTimeMillis();
      QueryTimeoutException exception = assertThrows(
          QueryTimeoutException.class, () -> {
            sampleService.registerWithSleep(new Sample(4, "Sample4"), 3);  // timeout = 2
          }
      );
      long processSeconds = (System.currentTimeMillis() - startTime) / 1000;
      assertEquals(2, processSeconds);  // timeout should be 2 seconds
      SQLTimeoutException sqlException = (SQLTimeoutException) exception.getCause();
      assertEquals(0, sqlException.getErrorCode());
      assertNull(sqlException.getSQLState());
    }
  }
}

Diving into Internal Implementation (PostgreSQL)

In the case of PostgreSQL, a TimerTask running on a separate thread within the JDBC driver cancels the query once the timeout period has passed.

The source code is around here 👇

https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgStatement.java#L998

When the query is canceled, a java.sql.SQLException (actually its subclass org.postgresql.util.PSQLException) is thrown on the application side. Calling getSQLState() on this exception returns 57014. This is the error code representing "query_canceled" in the PostgreSQL documentation.

The documentation is around here 👇

https://www.postgresql.org/docs/16/errcodes-appendix.html

Since 57014 is not included in sql-error-codes.xml within spring-jdbc, it is ultimately translated into DataAccessResourceFailureException by SQLStateSQLExceptionTranslator.

Diving into Internal Implementation (MySQL)

Similarly for MySQL, a TimerTask running on a separate thread within the JDBC driver cancels the query once the timeout period has passed.

The source code is around here 👇

https://github.com/mysql/mysql-connector-j/blob/release/9.x/src/main/user-impl/java/com/mysql/cj/jdbc/StatementImpl.java#L1446

When the query is canceled, a java.sql.SQLTimeoutException is thrown on the application side. This exception is eventually translated into QueryTimeoutException by SQLExceptionSubclassTranslator.

Is it okay for exceptions to differ by RDBMS?

The fact that exceptions differ depending on the RDBMS doesn't seem very "Spring-like," as Spring excels at low-level abstraction (ensuring that application code doesn't need changes regardless of the underlying implementation).

So, I opened an issue with the Spring Framework to clarify 👇

https://github.com/spring-projects/spring-framework/issues/35073

As it turns out, it was indeed an oversight. It's expected that in Spring Boot 3.5.1 or 3.5.2, PostgreSQL will also throw QueryTimeoutException.

In the Case of Nested Transactions

Regarding the timeout attribute, the @Transactional Javadoc states the following:

Exclusively designed for use with Propagation.REQUIRED or Propagation.REQUIRES_NEW since it only applies to newly started transactions.

https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/transaction/annotation/Transactional.html#timeout()

In other words, it follows the configuration of the caller.