Don't Open a Transaction Before You Need One

A common production failure mode in Spring services is not high CPU, a memory leak, or a broken deployment. Sometimes it is simply this: the service runs out of database connections because each request holds a connection for much longer than necessary.

In one incident pattern, an API endpoint received a higher-than-usual number of invalid adjustment requests. Many of them failed validation with a client error, but the service still started a database transaction before doing the checks that would eventually reject the request. The result was a saturated connection pool:

Connection is not available, request timed out after ...
(active connections exhausted, requests waiting)

The interesting part is that the failing requests were not supposed to write anything. They were mostly doing an idempotency lookup, fetching related data, and calling another internal service. The transaction was opened too early.

The Anti-pattern: Transactional Orchestration

The problematic shape looked roughly like this:

@Transactional
public AdjustmentResponse handleAdjustment(AdjustmentRequest request) {
    // Read-only idempotency check
    if (ledgerRepository.existsByExternalEventId(request.externalEventId())) {
        throw new DuplicateRequestException();
    }

    // Remote HTTP call
    SourceRecord sourceRecord = externalRecordClient.findSourceRecord(request.sourceRecordId());

    // Request validation
    validateAdjustment(request, sourceRecord);

    // Actual database writes happen only down here
    LedgerContext context = ledgerProcessor.applyAdjustment(request, sourceRecord);

    return mapper.toResponse(context);
}

This is attractive because it feels safe: "the whole use case is transactional". But the method is not just a database write. It is orchestration. It reads, calls other services, validates business rules, builds a model, then finally writes.

The Fix: Narrow the Write Transaction

A better shape is to keep pre-checks and downstream calls outside the write transaction, then enter a transactional method only when mutation starts.

public AdjustmentResponse handleAdjustment(AdjustmentRequest request) {
    if (ledgerRepository.existsByExternalEventId(request.externalEventId())) {
        throw new DuplicateRequestException();
    }

    SourceRecord sourceRecord = externalRecordClient.findSourceRecord(request.sourceRecordId());
    validateAdjustment(request, sourceRecord);

    AdjustmentCommand command = buildAdjustmentCommand(request, sourceRecord);

    // Narrow transactional boundary
    LedgerContext context = ledgerProcessor.applyAdjustment(command);

    return mapper.toResponse(context);
}

@Transactional
public LedgerContext applyAdjustment(AdjustmentCommand command) {
    LedgerContext context = calculator.calculate(command);
    UUID ledgerEntryId = ledgerRepository.insert(context.toLedgerEntry());
    ledgerContextRepository.insert(context.toContextLog(ledgerEntryId));
    return context;
}

Now the connection is held only during the actual write path. If the request is invalid, or if the remote source record lookup returns nothing, the service does not occupy a write transaction while waiting.

What About Fresh Reads?

Removing @Transactional does not mean reads become stale by default. In PostgreSQL's default READ COMMITTED isolation, each standalone query still runs in its own short transaction and sees data committed before that statement starts.

The real "freshness" question is usually about which datasource you read from. If an idempotency check reads from a read replica, replica lag can make the service miss a row that was just written to the primary. For critical idempotency checks, consider reading from the primary/write datasource even if the query is read-only.

public boolean existsByExternalEventId(UUID externalEventId) {
    return Boolean.TRUE.equals(writeJdbcTemplate.queryForObject(
        EXISTS_BY_EXTERNAL_EVENT_ID,
        Boolean.class,
        externalEventId
    ));
}

Indexes and Idempotency

Pre-checks need to be cheap. If an idempotency check searches by external_event_id, make sure the database has an index that supports that lookup.

CREATE INDEX CONCURRENTLY IF NOT EXISTS ledger_entries_external_event_id_idx
ON ledger_entries(external_event_id)
WHERE external_event_id IS NOT NULL;

A partial index is useful when external_event_id can be null. It keeps the index smaller and focused on the rows that can actually be found by the idempotency check.

Practical Rules

• Do not put @Transactional on a whole orchestration method by default.
• Do not call remote services while holding a database transaction unless there is a deliberate reason.
• Keep write transactions close to the writes they protect.
• Use @Transactional(readOnly = true) intentionally for short, consistent read operations.
• Use the primary datasource for idempotency checks when replica lag would be unsafe.
• Back every frequent lookup with an index that matches the actual query shape.
• Use database constraints for correctness; use pre-checks for better user errors and cheaper happy paths.