You must apply a price change to roughly ten million rows in a live system that uses Hibernate with a second-level cache and concurrent users. How do you decide between one JPQL bulk UPDATE, chunked bulk statements, and loading entities in batches?

## Framing the decision There is no default answer; there are three axes and the workload picks a point. Expressiveness. A bulk statement can do anything SQL can do to columns. If the price change is price * 1.1, or a join against a rules table, SQL wins: one statement, no data over the wire, no objects on the heap. The moment the rule needs Java - a pricing service call, conditional logic across aggregates, encryption in application code, audit callbacks - the object layer is the only place that logic exists, and iteration is forced. Transaction footprint. Ten million rows in one statement means one transaction holding row locks for its whole duration, undo or WAL proportional to the change, a rollback as expensive as the work, and, on a replicated system, lag while followers apply it. It also blocks nothing until it does: writers touching the same rows queue behind it. Chunking - process by primary key range or by a natural partition, commit each chunk, sleep briefly between chunks - converts one long transaction into many short ones. You lose atomicity, so the job must be idempotent and resumable: a progress marker, a predicate that excludes already-processed rows, and safe re-runs. Concurrency and staleness. Two caches matter. The persistence context of any session running the job must be cleared after each chunk, or the job accumulates stale managed entities. The second-level cache is invalidated per HQL bulk statement for the affected regions - correct, but with a chunked job that means repeated region drops and a read spike against the database for every chunk. Options: accept it in a quiet window; disable the region for the duration; or drive the job through a channel that lets you invalidate once at the end. For rows that interactive users may be holding, the versioned form of the update bumps the version so their next write fails visibly instead of silently restoring old prices. ## The three candidate designs One bulk statement. Simplest, fastest in raw throughput, atomic. Right for maintenance windows, smaller tables, and systems where a multi-minute write transaction is acceptable. Wrong for a busy OLTP system at this row count. Chunked bulk statements. The usual production answer: near set-based speed, bounded lock time, throttleable, resumable, observable (rows per second, chunks remaining). Costs: not atomic, so the system must tolerate a partially applied change - which for a price update usually means agreeing with the business that the rollout is gradual. Chunk by an indexed key so each statement's WHERE clause is a range scan, not a full scan repeated per chunk. Entity iteration in batches. Needed when Java logic is unavoidable. Use a scrollable or keyset-paginated read, apply the change, flush and clear every N entities, and prefer a stateless session where the object-layer features are not needed, since it keeps no persistence context and no cache. Expect one or two orders of magnitude less throughput and plan the runtime accordingly. Batched JDBC writes help, but the ceiling is far below set-based SQL. ## What I would actually propose For a formula-based price change on ten million rows in a live system: chunked bulk UPDATE, keyed on the primary key, chunk size tuned so each transaction lasts well under a second, the versioned form if the catalogue is user-editable, a small pause between chunks, and metrics on rows processed plus database wait events. Ship it as a resumable job with a kill switch. Before it runs, agree with the owners of the second-level cache on invalidation, and warn any reporting consumers that the change is not instantaneous. And the standing caveat for the whole family: whichever bulk shape you choose, the persistence context is never reconciled. Any long-lived session in the job clears after each chunk, and application code that had entities loaded before the run must re-read them. ## Signals that you chose wrong Lock waits and timeouts on user transactions mean the chunks are too big or unthrottled. A database-wide read spike after each chunk means cache invalidation is dominating - reconsider region strategy. A job that cannot be restarted after a failure means the chunking is not idempotent. And a bulk statement that silently disagrees with what users see means version bumping or cache invalidation was skipped.