Part 046 — jOOQ Transactions and Batching

jOOQ memberi kontrol SQL yang sangat eksplisit.

Tetapi kontrol eksplisit berarti kamu harus disiplin terhadap:

• transaction boundary;
• rollback;
• update count;
• batch count;
• generated keys;
• upsert semantics;
• duplicate conflict;
• retry;
• connection management;
• mixing dengan JPA/JDBC;
• performance trade-offs.

jOOQ tidak menyembunyikan transaction. Itu kekuatannya sekaligus tanggung jawabnya.

Part ini membahas transaction dan batching jOOQ untuk production.

1. Core Thesis

Dengan jOOQ, correctness transaction berasal dari desain eksplisit:

One business operation = one transaction boundary.
Every DML checks affected rows.
Every batch defines partial failure semantics.
Every upsert defines conflict semantics.
Every retry retries whole transaction if safe.

jOOQ memudahkan SQL, tetapi tidak otomatis membuat operasi atomic jika kamu tidak menaruhnya dalam transaction yang benar.

2. Transaction Boundary

Use case:

Approve case:
- command dedup start;
- update case status/version;
- insert audit;
- insert outbox;
- command dedup complete.

Semua harus atomic.

Spring style:

@Transactional
public ApproveCaseResult approve(ApproveCaseCommand command) {
    commandDedupDao.start(command);
    caseDao.approve(command);
    auditDao.append(...);
    outboxDao.append(...);
    commandDedupDao.complete(command.id(), result);
    return result;
}

Requirement:

All DAOs use same transaction-bound DSLContext/connection.

3. jOOQ Transaction API

jOOQ native transaction:

dsl.transaction(configuration -> {
    DSLContext tx = DSL.using(configuration);

    tx.update(CASE_FILE)
      .set(CASE_FILE.STATUS, "APPROVED")
      .where(CASE_FILE.ID.eq(caseId))
      .execute();

    tx.insertInto(CASE_AUDIT)
      .set(...)
      .execute();
});

If exception thrown, transaction rolls back.

This is useful in standalone jOOQ apps.

In Spring apps, choose either Spring @Transactional or jOOQ transaction API as primary convention. Avoid accidental nested/separate transaction confusion.

4. Spring Transaction Integration

Typical Spring setup uses transaction-aware DataSource.

Injected DSLContext participates in current transaction.

@Service
public class ApproveCaseUseCase {
    private final DSLContext dsl;

    @Transactional
    public void approve(...) {
        dsl.update(...).execute();
        dsl.insertInto(...).execute();
    }
}

Test rollback across multiple statements.

If rollback test fails, transaction integration is misconfigured.

5. Rollback Test

@Test
void approveRollsBackCaseUpdateWhenOutboxInsertFails() {
    assertThatThrownBy(() ->
            tx.execute(() -> {
                caseDao.approve(caseId, expectedVersion, now);
                outboxDao.insert(invalidDuplicateEvent());
                return null;
            })
    ).isInstanceOf(DataAccessException.class);

    CaseFileRow row = caseQuery.find(caseId).orElseThrow();

    assertThat(row.status()).isEqualTo(UNDER_REVIEW);
    assertThat(auditQuery.findByCase(caseId)).isEmpty();
}

This proves atomicity.

6. Update Count Discipline

int updated = dsl.update(CASE_FILE)
        .set(CASE_FILE.STATUS, "APPROVED")
        .set(CASE_FILE.VERSION, CASE_FILE.VERSION.plus(1))
        .where(CASE_FILE.ID.eq(caseId.value()))
        .and(CASE_FILE.VERSION.eq(expectedVersion))
        .execute();

if (updated == 0) {
    throw new OptimisticConflict(caseId, expectedVersion);
}

if (updated != 1) {
    throw new DataAccessInvariantViolation("Expected 1 update, got " + updated);
}

Never ignore DML result count for critical writes.

7. Insert Count Discipline

int inserted = dsl.insertInto(CASE_AUDIT)
        .set(CASE_AUDIT.ID, audit.id())
        .set(CASE_AUDIT.CASE_ID, audit.caseId())
        .set(CASE_AUDIT.ACTION, audit.action())
        .execute();

if (inserted != 1) {
    throw new DataAccessInvariantViolation("Audit insert affected " + inserted);
}

Most insert should affect one row. Batch/multi-row insert differs.

8. Generated Keys

If DB generates key:

Record1<Long> key =
        dsl.insertInto(CASE_FILE)
           .set(CASE_FILE.CASE_NUMBER, number)
           .returningResult(CASE_FILE.ID)
           .fetchOne();

or:

CaseFileRecord record =
        dsl.insertInto(CASE_FILE)
           .set(...)
           .returning()
           .fetchOne();

Dialect support varies.

For idempotent create, app-generated IDs are often simpler.

9. App-Generated ID

CaseFileId id = CaseFileId.newId();

dsl.insertInto(CASE_FILE)
   .set(CASE_FILE.ID, id.value())
   .set(CASE_FILE.CASE_NUMBER, number.value())
   .execute();

Pros:

• ID known before insert;
• outbox event can include ID;
• retry semantics easier;
• batch insert easier.

Cons:

• ID generation strategy and index locality matter;
• ID uniqueness responsibility in app.

10. Returning Updated Version

Record2<UUID, Long> updated =
        dsl.update(CASE_FILE)
           .set(CASE_FILE.STATUS, "APPROVED")
           .set(CASE_FILE.VERSION, CASE_FILE.VERSION.plus(1))
           .where(CASE_FILE.ID.eq(caseId.value()))
           .and(CASE_FILE.VERSION.eq(expectedVersion))
           .returningResult(CASE_FILE.ID, CASE_FILE.VERSION)
           .fetchOne();

if (updated == null) {
    throw new OptimisticConflict(caseId, expectedVersion);
}

long newVersion = updated.get(CASE_FILE.VERSION);

If dialect supports, this avoids follow-up select.

If not supported/emulated, verify performance/behavior.

11. Upsert

Upsert means insert or update on conflict.

Example read model:

int applied = dsl.insertInto(CASE_DASHBOARD_READ_MODEL)
        .set(CASE_DASHBOARD_READ_MODEL.CASE_ID, snapshot.caseId())
        .set(CASE_DASHBOARD_READ_MODEL.STATUS, snapshot.status().dbCode())
        .set(CASE_DASHBOARD_READ_MODEL.SOURCE_VERSION, snapshot.version())
        .onConflict(CASE_DASHBOARD_READ_MODEL.CASE_ID)
        .doUpdate()
        .set(CASE_DASHBOARD_READ_MODEL.STATUS, snapshot.status().dbCode())
        .set(CASE_DASHBOARD_READ_MODEL.SOURCE_VERSION, snapshot.version())
        .where(CASE_DASHBOARD_READ_MODEL.SOURCE_VERSION.lt(snapshot.version()))
        .execute();

Semantics:

• insert if missing;
• update if newer;
• ignore duplicate/old if where false.

Test each branch.

12. Upsert Is Not Always Safe

Upsert can hide bugs.

Bad for aggregate create:

create case with id X
if conflict update existing

This may overwrite old aggregate.

Use upsert for:

• read model projection;
• idempotent inbox state;
• counters/snapshots where semantics clear;
• reference cache table.

Avoid upsert for domain create unless operation is truly idempotent and payload conflict checked.

13. Idempotency Upsert

Command dedup insert:

int inserted = dsl.insertInto(COMMAND_DEDUP)
        .set(COMMAND_DEDUP.COMMAND_ID, command.id())
        .set(COMMAND_DEDUP.PAYLOAD_HASH, command.payloadHash())
        .set(COMMAND_DEDUP.STATUS, "PROCESSING")
        .set(COMMAND_DEDUP.CREATED_AT, now)
        .onConflict(COMMAND_DEDUP.COMMAND_ID)
        .doNothing()
        .execute();

if (inserted == 1) {
    return CommandStartResult.started();
}

CommandDedupRecord existing =
        dsl.selectFrom(COMMAND_DEDUP)
           .where(COMMAND_DEDUP.COMMAND_ID.eq(command.id()))
           .fetchOne();

if (!existing.getPayloadHash().equals(command.payloadHash())) {
    throw new IdempotencyKeyConflict(command.id());
}

return CommandStartResult.duplicate(existing);

This is explicit and retry-safe.

14. Batch Execution Types

jOOQ supports several batch styles:

1. batch of different queries;
2. batch bind same query with different values;
3. multi-row insert;
4. loader/import style;
5. plain JDBC batch via underlying connection if needed.

Choose based on SQL shape and size.

15. Batch Bind

BatchBindStep batch = dsl.batch(
        dsl.insertInto(CASE_AUDIT)
           .columns(
                   CASE_AUDIT.ID,
                   CASE_AUDIT.CASE_ID,
                   CASE_AUDIT.ACTION,
                   CASE_AUDIT.CREATED_AT
           )
           .values((UUID) null, null, null, null)
);

for (AuditRow row : rows) {
    batch.bind(
            row.id(),
            row.caseId(),
            row.action(),
            row.createdAt()
    );
}

int[] counts = batch.execute();

Good when same statement repeats many times.

16. Batch Count Handling

JDBC batch counts can include special values depending driver.

You need helper:

BatchCounts.requireOnePerItem("CaseAuditDao.insertBatch", counts, rows.size());

Contract should define:

• expected count per item;
• partial failure behavior;
• transaction rollback;
• duplicate handling.

Do not ignore int[].

17. Multi-Row Insert

var insert = dsl.insertInto(
        CASE_AUDIT,
        CASE_AUDIT.ID,
        CASE_AUDIT.CASE_ID,
        CASE_AUDIT.ACTION,
        CASE_AUDIT.CREATED_AT
);

for (AuditRow row : rows) {
    insert = insert.values(
            row.id(),
            row.caseId(),
            row.action(),
            row.createdAt()
    );
}

int inserted = insert.execute();

Pros:

• one SQL statement;
• may be fast.

Cons:

• huge SQL if many rows;
• DB parameter limit;
• failure affects entire statement;
• generated SQL size;
• memory building query.

Chunk large lists.

18. Chunking

public void insertAuditRows(List<AuditRow> rows) {
    for (List<AuditRow> chunk : Lists.partition(rows, 500)) {
        insertAuditChunk(chunk);
    }
}

Chunk size depends:

• DB parameter limit;
• row width;
• network;
• transaction duration;
• failure retry cost.

For critical batch, measure.

19. Batch Transaction Boundary

Option A: one transaction for all chunks.

all-or-nothing

Good when atomicity required and data size moderate.

Option B: one transaction per chunk.

durable progress

Good for backfill/repair/export where resume is needed.

Document semantics.

Do not accidentally use one huge transaction for million-row batch.

20. Bulk Insert With Generated IDs

If app generates IDs, batch insert is easier.

If DB generates IDs for each row, retrieving all generated keys can be dialect/driver-dependent.

For high-volume insert, prefer app-generated IDs or database sequence strategy that works with batch.

21. Batch Update

Same query, many binds:

BatchBindStep batch = dsl.batch(
        dsl.update(CASE_FILE)
           .set(CASE_FILE.STATUS, (String) null)
           .set(CASE_FILE.UPDATED_AT, (OffsetDateTime) null)
           .where(CASE_FILE.ID.eq((UUID) null))
           .and(CASE_FILE.VERSION.eq((Long) null))
);

for (CaseStatusUpdate row : updates) {
    batch.bind(
            row.status().dbCode(),
            row.updatedAt(),
            row.caseId().value(),
            row.expectedVersion()
    );
}

int[] counts = batch.execute();

But conflict handling per row becomes harder.

For versioned update, you may need inspect counts and map conflicts.

22. Bulk Conditional Update

If all rows share same condition:

int updated = dsl.update(CASE_FILE)
        .set(CASE_FILE.STATUS, "EXPIRED")
        .set(CASE_FILE.VERSION, CASE_FILE.VERSION.plus(1))
        .where(CASE_FILE.STATUS.eq("OPEN"))
        .and(CASE_FILE.EXPIRES_AT.lt(now))
        .execute();

Fast, but no per-row domain event/audit unless separately handled.

Use for technical/batch state change with explicit audit strategy.

23. Audit for Bulk Operation

If bulk update is domain-significant, you need audit/outbox.

Options:

1. select affected IDs, then update per chunk with audit/outbox;
2. use RETURNING to get affected rows and insert audit/outbox;
3. write bulk audit summary only if acceptable;
4. create repair/report record.

Do not bulk-update domain state silently.

24. RETURNING for Bulk Update

On supporting DB:

Result<Record2<UUID, Long>> changed =
        dsl.update(CASE_FILE)
           .set(CASE_FILE.STATUS, "EXPIRED")
           .set(CASE_FILE.VERSION, CASE_FILE.VERSION.plus(1))
           .where(CASE_FILE.STATUS.eq("OPEN"))
           .and(CASE_FILE.EXPIRES_AT.lt(now))
           .returningResult(CASE_FILE.ID, CASE_FILE.VERSION)
           .fetch();

for (Record2<UUID, Long> row : changed) {
    outboxDao.append(CaseExpiredEvent.from(row.value1(), row.value2()));
}

Ensure all inside same transaction.

Be careful with huge returning result size.

25. Batch vs Bulk Decision

26. Conflict Handling

Conflict types:

• unique key duplicate;
• optimistic version count 0;
• FK violation;
• check constraint;
• idempotency key mismatch;
• serialization failure;
• deadlock;
• lock timeout.

jOOQ exceptions should be translated.

Do not just catch Exception.

27. Unique Constraint Mapping

try {
    dsl.insertInto(CASE_FILE)
       .set(...)
       .execute();
} catch (DataAccessException e) {
    if (sqlErrorClassifier.isUniqueViolation(e, "uq_case_file_tenant_case_number")) {
        throw new DuplicateCaseNumber(caseNumber, e);
    }

    throw e;
}

Constraint name must be stable in migrations.

28. FK Violation Mapping

FK violation may mean:

• user referenced non-existent entity;
• race deleted referenced row;
• application bug;
• migration/data integrity problem.

Map carefully.

Example:

fk_case_assignment_case -> CaseNotFound or invariant violation depending context
fk_case_assignment_officer -> OfficerNotFound

If application should have checked existence, decide whether FK violation becomes user-facing or internal error.

29. Retryable Errors

Retryable candidates:

• deadlock;
• serialization failure;
• transient connection issue;
• lock timeout for worker maybe.

Retry whole transaction:

transactionRetrier.execute("ExpireCases", () ->
        txTemplate.execute(status -> {
            expireChunk();
            return null;
        })
);

Do not retry only one SQL after partial transaction error.

After serious SQL exception, transaction may be rollback-only.

30. Transaction Retry and Idempotency

If retrying whole transaction, ensure:

• command ID dedup;
• deterministic IDs/event keys;
• no external side effect inside transaction;
• outbox event unique key;
• batch chunk checkpoint safe;
• operation safe to re-run.

Retry without idempotency can duplicate audit/outbox/work.

31. Timeout

Set timeouts at:

• transaction level;
• query/statement level;
• lock wait level;
• worker job deadline.

jOOQ execution should not run unbounded.

But timeout should trigger rollback and possibly retry/mark failed according to operation.

32. Savepoint

For advanced batch, savepoint can isolate partial failure inside transaction.

But savepoint complexity is high.

Often better:

• chunk transactions;
• validate before batch;
• idempotent retry;
• dead-letter failed item.

Use savepoint only when all-or-nothing outer transaction plus partial sub-operation recovery is truly needed.

33. Mixing jOOQ and JPA in Transaction

If JPA entity is managed and jOOQ updates same table:

CaseFileEntity entity = entityManager.find(CaseFileEntity.class, id);

dsl.update(CASE_FILE)
   .set(CASE_FILE.STATUS, "CLOSED")
   .where(CASE_FILE.ID.eq(id))
   .execute();

entity.getStatus(); // stale

Fix:

• avoid mixing same table in same transaction;
• flush JPA before jOOQ reads;
• clear/refresh after jOOQ writes;
• separate transaction boundaries.

34. Mixing jOOQ and JDBC

jOOQ uses JDBC underneath.

If using raw JDBC in same transaction, ensure same transaction-bound connection.

In Spring, use transaction-aware DataSource/JdbcTemplate/jOOQ config.

Rollback test proves setup.

35. Connection Management

Do not manually open independent connection inside @Transactional use case unless intended.

Bad:

try (Connection c = dataSource.getConnection()) {
    DSL.using(c).update(...).execute();
}

This may not participate in Spring transaction.

Use injected transaction-aware DSLContext.

36. Batch Performance Observability

Metrics:

batch.rows.count{operation}
batch.duration{operation}
batch.chunk.size{operation}
batch.failure.count{operation, reason}
bulk.update.affected_rows{operation}
transaction.retry.count{operation}

Log summary:

{
  "operation": "BackfillCaseDashboard",
  "chunkSize": 500,
  "updated": 500,
  "durationMs": 320
}

Avoid logging every row.

37. Generated Keys and Unknown Commit

If insert with DB-generated ID succeeds but client times out before response, retry can create duplicate unless idempotency table stores result.

For retry-safe create:

• command ID;
• app-generated aggregate ID;
• unique business key;
• stored command result.

Do not rely only on generated key if client may retry.

38. Outbox Insert Atomicity

caseDao.approve(...);
auditDao.insert(...);
outboxDao.insert(...);

If outbox insert fails, case update must rollback.

If case update commits but outbox missing, integration event lost.

Rollback test required.

Outbox event key should be unique for idempotency:

case-approved:{commandId}

39. Batch Outbox Publish Marking

After publish:

int updated = dsl.update(OUTBOX_EVENT)
        .set(OUTBOX_EVENT.PUBLISHED_AT, now)
        .where(OUTBOX_EVENT.ID.eq(eventId))
        .and(OUTBOX_EVENT.CLAIMED_BY.eq(workerId))
        .and(OUTBOX_EVENT.PUBLISHED_AT.isNull())
        .execute();

if (updated != 1) {
    throw new OutboxClaimLost(eventId);
}

Ownership check prevents worker from marking event it no longer owns.

40. Batch Claim With Lease

Claim:

dsl.update(OUTBOX_EVENT)
   .set(OUTBOX_EVENT.CLAIMED_BY, workerId)
   .set(OUTBOX_EVENT.CLAIMED_AT, now)
   .where(OUTBOX_EVENT.PUBLISHED_AT.isNull())
   .and(OUTBOX_EVENT.CLAIMED_AT.isNull()
        .or(OUTBOX_EVENT.CLAIMED_AT.lt(staleBefore)))
   ...

Contract:

• stale claims can be reclaimed;
• publish operation idempotent;
• consumer idempotent;
• marking published checks owner.

jOOQ makes these predicates explicit.

41. Batch Partial Failure

If batch insert 100 rows and row 57 violates constraint:

• driver may stop early;
• some rows may have executed inside transaction;
• if transaction rolls back, none durable;
• counts may be partial/special.

Therefore:

• run inside transaction;
• rollback on error;
• log failed chunk;
• if needed, binary search/bisect bad row in retry process;
• validate data before batch.

Do not assume batch failure leaves clean state unless transaction rolled back.

42. Chunk Retry Strategy

Backfill chunk:

read next 500 rows
transaction:
  update read model for 500 rows
  save cursor
commit

If fails:

• rollback chunk;
• retry chunk;
• if permanent bad row, isolate and mark failed;
• do not advance cursor before commit.

Cursor save must be in same transaction as chunk writes.

43. Transaction Boundary for Cursor

@Transactional
public void processChunk() {
    Cursor cursor = cursorDao.loadForUpdate(jobName);

    List<SourceRow> rows = sourceDao.readAfter(cursor, 500);

    readModelDao.upsertBatch(rows);

    cursorDao.save(jobName, rows.lastCursor());
}

If upsert fails, cursor does not advance.

44. Testing Batch

Test:

• empty list no-op;
• single row;
• multiple rows;
• duplicate row rollback;
• partial failure rollback;
• generated key mapping;
• update counts;
• chunk cursor not advanced on failure;
• idempotent re-run;
• transaction rollback across audit/outbox.

45. Testing Upsert

@Test
void projectionUpsertIgnoresOlderVersion() {
    projectionDao.apply(snapshot(version(8), status(APPROVED)));
    projectionDao.apply(snapshot(version(7), status(UNDER_REVIEW)));

    ProjectionRow row = projectionDao.find(caseId).orElseThrow();

    assertThat(row.status()).isEqualTo(APPROVED);
    assertThat(row.sourceVersion()).isEqualTo(8);
}

46. Testing Batch Counts

@Test
void insertBatchInsertsAllRows() {
    List<AuditRow> rows = fixture.auditRows(10);

    auditDao.insertBatch(rows);

    assertThat(auditQuery.count()).isEqualTo(10);
}

Also test duplicate fails and transaction rollback.

47. Testing Transaction Integration

@Test
void jooqUsesSpringTransaction() {
    assertThatThrownBy(() ->
            service.doTwoWritesThenFail()
    ).isInstanceOf(RuntimeException.class);

    assertThat(query.firstWriteExists()).isFalse();
    assertThat(query.secondWriteExists()).isFalse();
}

This catches wrong connection/transaction setup.

48. Review Checklist

• Business operation transaction boundary explicit.
• jOOQ uses transaction-aware DSLContext.
• Rollback test exists.
• Update counts checked.
• Insert counts checked.
• Generated keys strategy understood.
• Upsert semantics documented.
• Unique/FK/check errors translated.
• Batch counts checked.
• Batch chunk size bounded.
• Partial failure rollback behavior tested.
• Retry retries whole transaction.
• Idempotency exists for retryable commands.
• Outbox insert atomic with state change.
• Claim/mark published checks ownership.
• Cursor checkpoint saved with chunk transaction.
• jOOQ/JPA mixing handled carefully.
• Metrics for batch/bulk exist.

49. Anti-Pattern: Ignoring .execute() Result

Affected rows are correctness signal.

50. Anti-Pattern: Upsert Domain Aggregate Without Payload Conflict Check

Can overwrite existing domain state.

51. Anti-Pattern: Batch Without Transaction

Partial durable writes on failure.

52. Anti-Pattern: Advancing Cursor Before Commit

Backfill can skip rows.

53. Anti-Pattern: New Connection Inside Transaction

May bypass transaction manager.

54. Anti-Pattern: Retry Single Statement After Transaction Error

Rollback and retry whole transaction if safe.

55. Mini Lab

Design jOOQ implementation for:

Backfill case_dashboard_read_model:
- read source case rows by ID cursor;
- map to dashboard snapshot;
- upsert read model only if source version newer;
- save cursor;
- chunk size 500;
- retry deadlock;
- isolate permanent bad row;
- metrics.

Questions:

1. What transaction boundary?
2. Is cursor row locked?
3. How is source chunk read?
4. How is upsert written?
5. How is old version ignored?
6. How is cursor saved?
7. What if upsert row 312 fails?
8. How is retry safe?
9. What metrics are emitted?
10. What tests prove resume correctness?

56. Summary

jOOQ transaction and batching give explicit power.

You must master:

• transaction boundary;
• Spring vs jOOQ transaction API;
• rollback testing;
• update/insert count discipline;
• generated keys;
• app-generated IDs;
• returning;
• upsert;
• idempotency table;
• batch bind;
• multi-row insert;
• chunking;
• bulk update;
• audit/outbox implications;
• conflict translation;
• retry whole transaction;
• timeout;
• savepoint caution;
• jOOQ/JPA mixing;
• connection management;
• outbox claim/mark;
• cursor checkpoint;
• batch tests and metrics.

Part berikutnya membahas MyBatis Mapper Pattern: XML vs annotation, dynamic SQL, result map, nested mapping, and how MyBatis fits SQL ownership with explicit mapping.

57. References

• jOOQ Transactions: https://www.jooq.org/doc/latest/manual/sql-execution/transaction-management/
• jOOQ Batch Execution: https://www.jooq.org/doc/latest/manual/sql-execution/batch-execution/
• jOOQ INSERT Statement: https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/insert-statement/
• jOOQ UPDATE Statement: https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/update-statement/
• jOOQ SQL Building: https://www.jooq.org/doc/latest/manual/sql-building/
• Spring Framework Transaction Management: https://docs.spring.io/spring-framework/reference/data-access/transaction.html