Spring Boot @Transactional(readOnly=true)在AWS RDS+HikariCP下未释放连接

在AWS RDS环境下，使用Spring Boot 3.4.3、MySQL 8.0、Hibernate 6.6.8.Final，通过JPA+QueryDSL实现CRUD操作。所有功能正常，但给只读查询方法添加@Transactional(readOnly = true)注解后，数据库连接未被归还，持续处于sleep状态，且不断创建新连接，最终可能导致连接池耗尽。

服务层、Repository层、Controller层代码如下：

服务层代码 #

@Transactional(readOnly = true)
public Page<BarCode> getAllActiveBarcodes(Pageable pageable, Boolean isAction, String keyword, String searchType, String type) throws IllegalArgumentException {
    return barCodeRepository.searchBarcodes(pageable, isAction, keyword, searchType, type);
}

@Transactional(readOnly = true)
public List<BarCode> barCodeSearch(String keyword) throws IllegalArgumentException {
    return barCodeRepository.searchKeyword(keyword);
}

@Transactional(readOnly = true)
public List<BarCode> barCodeList(){
    return barCodeRepository.findAll();
}

Repository层实现代码 #

@Override
public List<BarCode> searchKeyword(String keyword) {
    BooleanBuilder builder = new BooleanBuilder();
    builder.and(barCode.isActive.isTrue());
    builder.and(barCode.deleteDate.isNull());

    if (keyword != null && !keyword.trim().isEmpty()) {
        BooleanBuilder keywordBuilder = new BooleanBuilder();
        keywordBuilder.or(barCode.barcode.containsIgnoreCase(keyword));
        keywordBuilder.or(barCode.type.containsIgnoreCase(keyword));
        keywordBuilder.or(barCode.message.containsIgnoreCase(keyword));
        keywordBuilder.or(barCode.descText.containsIgnoreCase(keyword));
        keywordBuilder.or(barCode.createUser.containsIgnoreCase(keyword));
        builder.and(keywordBuilder);
    }

    return queryFactory
            .selectFrom(barCode)
            .where(builder)
            .orderBy(barCode.createDate.desc())
            .fetch();
} 
@Override
public Page<BarCode> searchBarcodes(Pageable pageable, Boolean isAction, String keyword, String searchType, String type) {
    BooleanBuilder builder = new BooleanBuilder();

    if (isAction != null) {
        builder.and(barCode.isActive.eq(isAction));
    }
    if(type != null && !type.trim().isEmpty()) {
        builder.and(barCode.type.eq(type));
    }

    if (keyword != null && !keyword.trim().isEmpty()) {
        switch (searchType.toLowerCase()) {
            case "barcode" -> builder.and(barCode.barcode.containsIgnoreCase(keyword));
            case "message" -> builder.and(barCode.message.containsIgnoreCase(keyword));
            case "desc_text" -> builder.and(barCode.descText.containsIgnoreCase(keyword));
            case "create_user" -> builder.and(barCode.createUser.containsIgnoreCase(keyword));
            default -> throw new IllegalArgumentException("잘못된 검색 유형입니다.");
        }
    }

    long total = Optional.ofNullable(
            queryFactory
                    .select(barCode.count())
                    .from(barCode)
                    .where(builder)
                    .fetchOne()).orElse(0L);


    if (total == 0) {
        return Page.empty(pageable);
    }

    List<BarCode> result = queryFactory
            .selectFrom(barCode)
            .where(builder)
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .orderBy(barCode.createDate.desc())
            .fetch();

    return new PageImpl<>(result, pageable, total);
}

Controller层代码 #

@GetMapping("/barcodes")
public ResponseEntity<Page<BarCode>> getAllBarcodes(@PageableDefault(page = 0, size = 32) Pageable pageable,
                                                    @RequestParam(value = "isAction", required = false) Boolean isAction,
                                                    @RequestParam("type") String type,
                                                    @RequestParam("searchType") String searchType,
                                                    @RequestParam("keyword") String keyword)  {

    try {
        Page<BarCode> barcodeList = barcodeService.getAllActiveBarcodes(pageable, isAction, keyword, searchType, type);

        if(barcodeList.isEmpty()){
            return ResponseEntity.noContent().build();
        }

        return ResponseEntity.ok(barcodeList);
    } catch (IllegalStateException e) {
        return ResponseEntity.status(HttpStatus.BAD_REQUEST).build();
    } catch (Exception e) {
        return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();
    }
}

@GetMapping("/barcodes/all")
public ResponseEntity<List<BarCode>> getBarcode() throws IllegalArgumentException {
    try {
        List<BarCode> barCodeList = barcodeService.barCodeList();
        return ResponseEntity.ok(barCodeList);
    }catch (IllegalStateException e){
        return ResponseEntity.status(HttpStatus.BAD_REQUEST).build();
    }catch (Exception e){
        return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();
    }
}


@GetMapping("/barcodes/active")
public ResponseEntity<List<BarCode>> getActiveBarcode(@RequestParam(required = false) String keyword) throws IllegalArgumentException {

    try{
        List<BarCode> search = barcodeService.barCodeSearch(keyword);
        return ResponseEntity.ok(search);
    }catch (IllegalStateException e){
        return ResponseEntity.status(HttpStatus.BAD_REQUEST).build();
    }catch (Exception e){
        return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();
    }
}

1. QueryFactory未绑定到事务内的EntityManager #

QueryDSL的JPAQueryFactory如果是通过非Spring代理的EntityManager创建（比如直接从EntityManagerFactory手动创建），会导致查询使用独立的连接，不受Spring事务管理器管控，事务结束后连接无法自动归还。

解决方法： #

确保Repository中注入Spring管理的EntityManager，并用它初始化JPAQueryFactory：

@Repository
public class BarCodeRepositoryImpl implements BarCodeRepositoryCustom {

    private final EntityManager entityManager;
    private JPAQueryFactory queryFactory;

    // 注入Spring代理的EntityManager
    @Autowired
    public BarCodeRepositoryImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @PostConstruct
    public void init() {
        // 使用当前事务的EntityManager创建QueryFactory
        this.queryFactory = new JPAQueryFactory(entityManager);
    }

    // 后续查询逻辑不变
}

2. 连接池参数配置不合理 #

默认的连接池参数（比如HikariCP）可能允许空闲连接长时间存活，加上AWS RDS的连接超时机制，会导致sleep连接堆积，无法被及时回收。

解决方法： #

在application.yml中调整HikariCP参数，适配AWS RDS环境：

spring:
  datasource:
    hikari:
      max-lifetime: 1800000          # 连接最大存活时间（30分钟，小于AWS RDS默认超时）
      idle-timeout: 600000           # 空闲连接回收时间（10分钟）
      maximum-pool-size: 20          # 连接池最大容量（根据业务并发调整）
      minimum-idle: 5                # 最小空闲连接数
      connection-timeout: 30000      # 获取连接超时时间（30秒）

3. Hibernate连接释放模式未配置 #

如果Hibernate没有在事务结束后及时释放连接，会导致连接被占用。

解决方法： #

添加Hibernate连接释放配置：

spring:
  jpa:
    properties:
      hibernate:
        connection:
          release_mode: after_transaction  # 事务结束后立即释放连接

4. 事务边界验证 #

确认事务是否正确开启与关闭，可通过日志排查：

解决方法： #

开启Spring事务与JPA日志，查看事务生命周期：

logging:
  level:
    org.springframework.transaction: DEBUG
    org.springframework.orm.jpa: DEBUG

查看日志中是否有Committing transaction或Rolling back transaction的记录，确认事务正常结束。

内容的提问来源于stack exchange，提问作者박아</think_never_used_51bce0c785ca2f68081bfa7d91973934>