Database & Transaction Scenarios

Symptoms:

HikariPool-1 - Connection is not available, request timed out after 30000ms
(total=10, active=10, idle=0, waiting=42)

Identify the cause:

1. Metrics: hikaricp_connections_active, _pending and _usage (how long each connection is held). Long usage times point to long transactions.
2. Leak detection: set spring.datasource.hikari.leak-detection-threshold=20000. Hikari logs the stack trace of code that held a connection for more than 20 s.
3. Thread dumps: what are the threads holding connections doing? Often they're waiting on an HTTP call inside a @Transactional method.
4. Database: slow queries and lock waits keep connections busy (SHOW PROCESSLIST, pg_stat_activity).

Common root causes and fixes:

Increasing the pool size is the last step, not the first. A bigger pool on a struggling database usually makes things worse. See Caching, Connection Pooling & Migrations.

With little data, a full table scan is cheap. With millions of rows it isn't. Start with the execution plan:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 AND status = 'SHIPPED' ORDER BY created_at DESC LIMIT 20;

Look for full scans (type: ALL in MySQL, Seq Scan in PostgreSQL), filesorts, and huge row estimates.

Fixes:

• Add the right index. A composite index matching the filter and sort, with equality columns first: CREATE INDEX idx_orders_cust_status_created ON orders (customer_id, status, created_at).
• Make conditions index-friendly. No functions on indexed columns (WHERE DATE(created_at) = … → a range), no leading wildcards (LIKE '%x'), and no implicit type conversions (comparing a VARCHAR column with a number).
• Select only the needed columns. A covering index can then answer the query without touching the table.
• Paginate, and use keyset pagination for deep pages (Q19).
• Rewrite heavy patterns: OR → UNION, correlated subqueries → joins, NOT IN with NULLs → NOT EXISTS.
• Keep statistics fresh (ANALYZE TABLE / ANALYZE).
• For very large tables: partitioning (by date), archiving old rows, or precomputed summary tables for reports.

Then measure again. Index changes also slow down writes a little, so add only the indexes you need.

• Data volume and distribution. Production may have millions of rows, or skewed values (one customer with 2 million orders) that change the plan.
• The execution plan in production, which can differ because of different statistics, indexes or database version. Run EXPLAIN there, or on a production-sized copy.
• Missing indexes in production: a migration that wasn't applied, or an index created only locally by ddl-auto.
• Concurrency: locks and blocking from other transactions, which don't exist locally.
• Resources: database CPU, I/O, buffer pool or cache hit ratio, and noisy neighbours on shared hosts.
• Network latency: more round trips (N+1) cost much more than on localhost.
• Parameter-sensitive plans: a plan cached for one parameter value is bad for another.
• Configuration: isolation level, collation and character set (index usability), and database parameters.

Check the slow query log, compare plans, and test against realistic data before release.

It should degrade gracefully and recover automatically, never hang or crash.

• Fail fast: a short connection-timeout (for example 2–5 s instead of 30 s) so threads don't pile up waiting.
• Return a clear error: 503 Service Unavailable with a Retry-After header, not a stack trace or a hang.
• Readiness probes: report the instance as not ready so the load balancer stops sending traffic (management.endpoint.health.probes.enabled=true, with the DB check in the readiness group). Keep the database out of the liveness probe, otherwise Kubernetes restarts every pod during a database blip.
• Recover automatically: Hikari validates and replaces broken connections. Set max-lifetime below the database and firewall timeouts.
• Retry carefully: retry idempotent reads with backoff and jitter. Don't blindly retry writes (see Q20).
• Degrade: serve cached data for reads where acceptable. Queue writes (to Kafka or an outbox) if the business allows it.
• Protect the database when it comes back: circuit breakers and rate limiting avoid a thundering herd.

Also find out why it happened: too many connections from all instances, failover, network problems, or the database running out of resources.

The risk is the lost update: both read version A, both write, and the second write overwrites the first.

Options:

1. Optimistic locking (the default choice):

@Entity
public class Order {
    @Version
    private Long version;
}

The second update fails with ObjectOptimisticLockingFailureException. Then retry, or return 409 Conflict so the user can reload. For REST APIs, send the version as an ETag and require If-Match on updates.

1. Pessimistic locking for hot rows where conflicts are frequent (stock, seat booking):

@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("SELECT o FROM Order o WHERE o.id = :id")
Optional<Order> findForUpdate(@Param("id") Long id);

1. Atomic conditional updates for simple changes:

UPDATE product SET stock = stock - 1 WHERE id = ? AND stock > 0;   -- check that 1 row was updated

1. Database constraints (unique keys, checks) as the last line of defence.

See Transactions & Locking and Q47 for choosing between them.

Run the whole operation in one transaction, at the service layer:

@Service
public class OrderService {

    @Transactional(rollbackFor = Exception.class)
    public Order placeOrder(PlaceOrderCommand cmd) {
        Order order = orderRepository.save(Order.from(cmd));
        inventoryService.reserve(cmd.items());   // same transaction (REQUIRED)
        paymentRecordRepository.save(new PaymentRecord(order));
        return order;                             // an exception anywhere → everything rolls back
    }
}

Checklist:

• The method is public and called from another bean, through the Spring proxy.
• The exception propagates out of the method. Don't catch it and carry on.
• The exception type triggers rollback. Unchecked exceptions do by default; checked ones need rollbackFor.
• All repositories use the same data source and transaction manager.
• The tables use a transactional engine (InnoDB, not MyISAM).

If the operation also calls other services or Kafka, a database rollback can't undo those. Use the outbox pattern or a saga with compensating actions (Q21, Q26).

Check by enabling logging.level.org.springframework.transaction=DEBUG (or org.springframework.orm.jpa=DEBUG) to see when transactions begin, commit and roll back.

Spring applies @Transactional through a proxy that wraps the bean. External calls go through the proxy, but a call from one method of the bean to another (this.inner()) goes directly to the object, so the inner method's annotation is ignored.

@Service
public class ReportService {
    @Transactional
    public void outer() {
        inner();                        // ❌ REQUIRES_NEW is ignored: same transaction
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void inner() { … }
}

Fixes:

• Move inner() to another bean and inject it. This is the cleanest option.
• Self-inject the proxy: @Autowired @Lazy ReportService self; then call self.inner().
• Use TransactionTemplate for programmatic control:

transactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
transactionTemplate.executeWithoutResult(status -> doInner());

The same trap applies to @Async, @Cacheable and @Retryable.

Keyset (seek) pagination for large or deep datasets:

-- offset: the database reads and discards 1,000,000 rows
SELECT * FROM orders ORDER BY id LIMIT 50 OFFSET 1000000;

-- keyset: jumps straight to the position through the index
SELECT * FROM orders WHERE id > :lastSeenId ORDER BY id LIMIT 50;

• Use offset for small, bounded result sets or admin screens that need page numbers ("page 3 of 12").
• Use keyset for feeds, infinite scroll, APIs (cursor-based: ?after=eyJpZCI6MTIzfQ) and batch jobs.

The sort key must be unique and indexed. Use a tie-breaker: ORDER BY created_at, id with WHERE (created_at, id) > (?, ?). Spring Data supports this with ScrollPosition.keyset() and Window<T>. See Pagination & Sorting.

The client can't tell whether the request succeeded, so it retries, and a naive server performs the operation twice. Make the operation idempotent:

1. Idempotency key: the client sends a unique Idempotency-Key header (a UUID) per logical operation, and reuses it on retries.
2. Store the key in the same transaction as the business change, with a unique constraint:

CREATE TABLE idempotency_record (
    idem_key      VARCHAR(64) PRIMARY KEY,
    request_hash  VARCHAR(64) NOT NULL,
    response_body TEXT,
    status        VARCHAR(20) NOT NULL,   -- IN_PROGRESS / COMPLETED
    created_at    TIMESTAMP NOT NULL
);

1. On a retry with the same key: return the stored response without running the operation again. If the request body differs (a different hash), return 422. If the first request is still running, return 409 (or wait).
2. Natural idempotency where possible: PUT with the full state, DELETE, or a unique business key (order_ref) with a unique constraint, so a duplicate insert fails and you return the existing resource.

Expire old keys after 24–72 hours. Q45 covers storage and concurrency in more detail.