I had two very similar yet slightly different use cases. Both can be summarized as “finding potentially many entities with incorrect state amongst many more, and fixing them one by one”. Each entity was a non-trivial aggregate object and already mapped using Hibernate annotations to 3-4 tables, wired up using Spring Data JPA. Fixing their state would require running complicated business logic implemented in Java. In one situation, a very large number of records were affected. So many that I ideally didn’t want to have to load all their IDs into memory. At the same time, finding the affected records required a really complex query that I had to write using jOOQ. So, this is what I tried to do:
-
Outer transaction: Lazy-fetch the IDs of affected entities using ResultSet.stream(), backed by a Postgres cursor (which requires a transaction). This transaction is read-only and has an isolation level of “read committed”.
- Inner transaction (run for every affected entity): R