spring-retry-conventions

Database Retry Conventions

Context

• Apply when implementing database operations in service classes (src/main/java/**/service/**/*.java) that need resilience against transient database failures (e.g., connection issues, temporary unavailability, deadlocks).
• Applies specifically to services interacting with the database via jOOQ within this application.
• This pattern relies on the RetryConfig, AbstractDatabaseService, and DatabaseRetryProperties components configured in the project.
• Note: {base.package} represents your project's base package (e.g., com.example.myapp). Projects must implement the referenced classes (AbstractDatabaseService, DatabaseRetryException, etc.).

Requirements

1. Extend Base Class: Service classes performing database operations that should be retried MUST extend {base.package}.service.common.AbstractDatabaseService.
2. Constructor Injection: Services extending AbstractDatabaseService MUST receive DSLContext, RetryTemplate, and PlatformTransactionManager via constructor injection and pass them to the super(dsl, retryTemplate, txManager) constructor.
3. Use Retry Methods: ALL individual database operations within these services MUST be wrapped in one of the retry methods provided by the base class:
   • executeWithRetry(String operationName, DatabaseOperation<T> operation) — for write operations
   • executeReadOnlyWithRetry(String operationName, DatabaseOperation<T> operation) — for read-only operations (uses a read-only TransactionTemplate)
4. No @Transactional on Methods: Do NOT annotate service methods with @Transactional. Transactions are managed programmatically inside each retry attempt using TransactionTemplate with PROPAGATION_REQUIRES_NEW. Each attempt gets a fresh transaction and connection, preventing poisoned-connection reuse after failures.
5. Provide Operation Name: A meaningful, unique operationName string (typically matching the service method name or the specific action) MUST be provided as the first argument for clear logging and error reporting.
6. Use Logging Helpers (Optional): The base class provides logOperationStart(String operation, String details) and logOperationSuccess(String operation, String details) helpers. These can be used for additional debug-level logging outside the retry lambda if needed, but executeWithRetry/executeReadOnlyWithRetry already handles INFO-level start/success logging and ERROR-level failure logging.
7. Exception Handling:
   • Only transient database errors are retried. The recoverable SQLState classification is centralised in PostgreSQLExceptionOverride.isRecoverableState(String):
     • 08xxx — Connection exceptions (connection lost, timeout)
     • 57Pxx — Operator intervention (admin shutdown, failover)
     • 40xxx — Transaction rollback (serialization failure, deadlock)
     • 53xxx — Insufficient resources (out of memory, too many connections)
     • 25006 — Read-only transaction (PostgreSQL failover)
   • Non-recoverable errors (constraint violations 23xxx, syntax errors 42xxx, data errors 22xxx) are not retried.
   • If all retries are exhausted, the retry method wraps the last thrown exception in a {base.package}.exception.DatabaseRetryException.
   • Service methods should generally allow DatabaseRetryException to propagate upwards to be handled by the GlobalExceptionHandler.
8. HikariCP Connection Eviction: PostgreSQLExceptionOverride implements SQLExceptionOverride and returns MUST_EVICT for recoverable SQLStates, ensuring stale connections are removed from the pool before the next retry acquires a fresh one. When adding new recoverable SQLStates, update only isRecoverableState().
9. Configuration: Do not hardcode retry attempts or backoff periods. Rely on the centrally configured RetryTemplate which uses settings from DatabaseRetryProperties (defined in application.yml):

   database:
     retry:
       max-attempts: ${DB_RETRY_MAX_ATTEMPTS:3}
       initial-interval: ${DB_RETRY_INITIAL_INTERVAL:500}
       multiplier: ${DB_RETRY_MULTIPLIER:2.0}
       max-interval: ${DB_RETRY_MAX_INTERVAL:10000}
       timeout: ${DB_RETRY_TIMEOUT:30000}