skip to content

You need per-item partial rollback in a large batch. Discuss the trade-offs of Propagation.NESTED (savepoints) versus alternatives at scale, including locking and failure semantics.

level: principalimportance: nice to knowfreq 22%

answer

  1. savepoint keeps pre-savepoint locks until final commit
  2. one long tx = lock + undo/WAL growth
  3. chunked commit = durable progress, no global atomicity
  4. REQUIRES_NEW loop = connection/pool exhaustion
  5. idempotent + retry + outbox = scale winner

basics

~20 s

NESTED gives cheap per-item rollback via savepoints within one transaction, but the whole batch is still one long transaction holding locks and one connection. Alternatives — chunked commits, REQUIRES_NEW per item, or idempotent retries — trade atomicity for shorter locks and independent durability.

solid answer

~50 s

NESTED lets you wrap each item in a savepoint so a single failure rolls back just that item while the batch continues, all within one physical transaction and one connection — efficient and simple. The cost is that it remains one long-lived transaction: locks acquired before a savepoint are held until the final commit, the undo/redo log grows, and everything commits or aborts as a unit, so a late fatal error discards the whole batch. Alternatives: chunked commits (commit every N items) cap lock duration and transaction size but lose all-or-nothing; REQUIRES_NEW per item makes each item independently durable but consumes a connection per active inner transaction and risks pool exhaustion; idempotent per-item processing with retries and an outbox/dead-letter queue scales best but needs careful design. Choose NESTED when the batch genuinely must be atomic overall yet tolerate skipping bad items, and the batch is bounded so lock/log growth is acceptable.

code

java · 18 lines
java
// NESTED: atomic-overall batch that skips bad items,
// but ONE long transaction (watch lock/log growth on large N)
@Transactional(transactionManager = "jdbcTxManager")
public BatchResult process(List<Item> items) {
    int skipped = 0;
    for (Item it : items) {
        try {
            itemService.handle(it); // @Transactional(propagation = NESTED)
        } catch (BusinessException ex) {
            skipped++;              // rolled back to this item's savepoint
        }
    }
    return new BatchResult(items.size() - skipped, skipped);
    // A fatal error here still discards the WHOLE batch — that's the trade-off.
}

// Contrast — chunked commit caps lock duration but loses global atomicity:
// for (var chunk : partition(items, 500)) { txTemplate.execute(s -> { ... }); }

go deeper

for a junior

Not expected to reason about this depth; can state NESTED = per-item savepoint.

for a middle

Should recognize NESTED is still one long transaction and mention chunking as an alternative.

for a senior

Should articulate lock-retention semantics and contrast NESTED/REQUIRES_NEW/chunking with connection and durability implications.

for a principal

Should drive the full trade-off analysis — locking, undo/WAL growth, pool exhaustion, atomicity vs. incremental durability — and pick per context, invoking Spring Batch/outbox where appropriate.

## The scenario Process N items; a bad item should be undone and skipped, but you want control over how the batch as a whole succeeds or fails. Several patterns exist; NESTED is one point in the trade-off space. ## Option A — NESTED savepoints (one transaction, per-item savepoint) **How**: outer `@Transactional`; each item processed via a `Propagation.NESTED` method so a savepoint is set per item and only the failing item rolls back. **Locking reality**: a savepoint does **not** release locks acquired *before* it. Rolling back to a savepoint releases locks taken *after* it (the failed item's), but every row/index lock the transaction accumulated across successful items is held until the **final commit**. For a large batch this is a long-lived, wide-locking transaction — a source of contention, lock escalation, and blocking. **Log/undo growth**: the DB's undo/rollback segment and redo/WAL keep growing for the whole transaction lifetime; very large batches can pressure the DB (e.g., Postgres bloat, Oracle ORA-01555 snapshot-too-old for concurrent readers, MySQL undo growth). **Failure semantics**: still atomic overall — a fatal error near the end (or an explicit outer rollback) discards **all** items, even the ones whose savepoints succeeded. Good if you want all-or-nothing-with-skips; bad if you want committed progress. **Connection**: one connection for the whole batch — low pool pressure. Requires a savepoint-capable manager (`DataSourceTransactionManager`). ## Option B — Chunked commits (Spring Batch style) Commit every N items in their own transaction. Caps lock duration and transaction/log size, gives incremental durable progress, enables restart-from-last-chunk. Cost: **loses global atomicity** — a later chunk failing doesn't undo earlier committed chunks; you need restartability/compensation. This is what Spring Batch's chunk-oriented processing does and is usually the right answer at true scale. ## Option C — REQUIRES_NEW per item Each item commits independently. Durable progress per item and clean isolation of failures. Cost: **a connection held per active inner transaction** (outer suspended + inner active), so looping REQUIRES_NEW over a large batch can **exhaust the pool / self-deadlock**; also more commit overhead. Fine for a handful of independent side effects, poor as a per-row loop over thousands. ## Option D — Idempotent processing + retry + DLQ Make each item's processing idempotent (natural keys, upserts), process independently, retry transient failures, route permanent failures to a dead-letter queue; use the **outbox pattern** for downstream effects. Scales horizontally, shortest locks, no giant transaction. Cost: most design effort and eventual-consistency reasoning. ## Choosing - Small/bounded batch that must be atomic-overall but tolerate skipping bad rows, on a JDBC manager → **NESTED**. - Large batch, want durable incremental progress and restart → **chunked commits / Spring Batch**. - A few independent must-persist side effects → **REQUIRES_NEW** (sparingly). - High throughput, distributed, resilience-first → **idempotent + retry + outbox**. ## Cross-cutting gotchas - **Manager support**: NESTED needs `DataSourceTransactionManager`/`JdbcTransactionManager`; fails on `JpaTransactionManager` by default. - **ORM state**: savepoint rollback reverts DB rows but not Hibernate's first-level cache — mixing NESTED with JPA risks stale entity state. - **Proxy self-invocation**: per-item NESTED/REQUIRES_NEW calls must cross a Spring proxy or propagation is ignored. - **Rollback rules**: only rollback-triggering exceptions actually trip the savepoint rollback; a caught exception leaves the savepoint dangling (released at method end). - **Isolation & concurrency**: one long NESTED transaction can block other writers on the locked rows for its entire duration — measure contention, not just correctness.

  • Does rolling back to a savepoint release the locks held by earlier successful items in the same transaction?
    No. Rollback-to-savepoint releases only locks acquired after that savepoint (the failed item's). Locks taken before it, across earlier successful items, are retained until the transaction's final commit or a full rollback.
  • For a 1-million-row import, why is a single NESTED transaction usually the wrong tool?
    It's one long-lived transaction holding accumulating locks and growing undo/redo the whole time, hurting concurrency and risking snapshot-too-old / bloat, and a late failure discards everything. Chunked commits or idempotent per-item processing scale far better.

saying these in an interview costs you the question

  • Claiming a savepoint rollback frees all locks taken so far
  • Using a REQUIRES_NEW loop over thousands of rows without considering pool exhaustion
  • Assuming NESTED gives durable per-item progress (it doesn't — outer rollback wipes all)
  • Treating NESTED as a scalability tool rather than a bounded-batch atomicity tool

context

resolving links…