Using JOOQ 3.11.11 / Java 11

Creating a transaction to write to a few tables. Two of these are interacting in that I use the auto_inc row #s from one as fk column values for another.

DSLContext writeContext = DSL.using(sourceDestConnection.getDestination(), SQLDialect.POSTGRES_10);
writeContext.transaction(writeTransaction -> {
    try {
         ...

         Map returnMap = writeFn(dataToWrite, writeTransaction);
         secondWriteFn(moreDataToWrite, returnMap, writeTransaction);
         throw new RuntimeException();
     }
}

// this fn should write a series of records and save the auto_increment field in a map
public void writeFn(...) {
    Map<Long, Long> idMap = new HashMap<>();
    DSLContext context = DSL.using(configuration);
    for (Record record : importBits) {
        Record result = context.insertInto(IMPORT_TABLE).set(record)
                .returningResult(ID_FIELD).fetchOne();
        idMap.put((Long) record.get(ID_FIELD_LOOKUP), (Long) result.get(ID_FIELD));
    }
    return idMap;
}


// this fn should use the saved auto_inc fields from the previous fn as FK columns
public void secondWriteFn(...) throws IOException {
    DSLContext context = DSL.using(configuration);
    for (Map mergeMap : importTypes) {
        context.insertInto(MERGE_TYPE_TABLE)
                .set(buildMergeMap(mergeMap, idMap));
    }
}

// this just builds a map to insert
public ImmutableMap<Object, Object> buildMergeMap(Map mergeMap, Map idMap) {
    return ImmutableMap.builder()
      .put(... columns ...)
      .put(foreignKeyColumn, idMap.get(fkLookup_from_first_write_fn))
      .build();
}
     

The p-code is from memory (different PC) but the end result (expected) would be that both tables would be empty after the RuntimeException. What Im seeing is that the first table has data but the 2nd doesn't.

Follow up questions:

• do I need to use .execute() after each insert?
• is there a problem with using the putative return value from one insert in a subsequent insert if the whole process is transacted?

CodePudding user response：

It looks like you're not actually throwing your exception:

writeContext.transaction(writeTransaction -> {

    // Try here!
    try {
        ...

        Map returnMap = writeFn(dataToWrite, writeTransaction);
        secondWriteFn(moreDataToWrite, returnMap, writeTransaction);
        throw new RuntimeException();
    }

    // You omitted the catch, but if you catch the above RuntimeException, then
    // jOOQ does not know about it, and happily commit your transaction
}