Batching stores transparently in a Spring + jOOQ application

[FP]

(For the record, this same question was posted on SO: https://stackoverflow.com/questions/64027819/batching-stores-transparently. I will update the question there to reflect any information gleaned on this user group conversation.)

We are using the following frameworks and versions:

• jOOQ 3.11.1
• Spring Boot 2.3.1.RELEASE
• Spring 5.2.7.RELEASE

I have an issue where some of our business logic is divided into logical units that look as follows:

• Request containing a user transaction is received
• This request contains various information, such as the type of transaction, which products are part of this transaction, what kind of payments were done, etc.
• These attributes are then stored individually in the database.

In code, this looks approximately as follows:

TransactionRecord transaction = transactionRepository.create();
transaction.create(creationCommand);`

In Transaction#create (which runs transactionally), something like the following occurs:

storeTransaction();
storePayments();
storeProducts();
// ... other relevant information

A given transaction can have many different types of products and attributes, all of which are stored. Many of these attributes result in UPDATE statements, while some may result in INSERT statements - it is difficult to fully know in advance.

For example, the storeProducts method looks approximately as follows:

products.forEach(product -> {
    ProductRecord record = productRepository.findProductByX(...);
    if (record == null) {
        record = productRepository.create();
        record.setX(...);
        record.store();
    } else {
      // do something else
    }
});

If the products are new, they are INSERTed. Otherwise, other calculations may take place. Depending on the size of the transaction, this single user transaction could obviously result in up to O(n) database calls/roundtrips, and even more depending on what other attributes are present. In transactions where a large number of attributes are present, this may result in upwards of hundreds of database calls for a single request (!). I would like to bring this down as close as possible to O(1) so as to have more predictable load on our database.

Naturally, batch and bulk inserts/updates come to mind here. What I would like to do is to batch all of these statements into a single batch using jOOQ, and execute after successful method invocation prior to commit. I have found several (SO Post, jOOQ API, jOOQ GitHub Feature Request) posts where this topic is implicitly mentioned, and one user groups post that seemed explicitly related to my issue.

Since I am using Spring together with jOOQ, I believe my ideal solution (preferably declarative) would look something like the following:

@Batched(100) // batch size as parameter, potentially
@Transactional
public void createTransaction(CreationCommand creationCommand) {
    // all inserts/updates above are added to a batch and executed on successful invocation
}

Or perhaps set batching as the default behaviour with the option for non-batched stores. For this to work, I imagine I’d need to manage a scoped (ThreadLocal/Transactional/Session scope) resource which can keep track of the current batch such that:

1. Prior to entering the method, an empty batch is created if the method is @Batched,
2. A custom DSLContext (perhaps extending DefaultDSLContext) that is made available via DI has a ThreadLocal flag which keeps track of whether any current statements should be batched or not, and if so
3. Intercept the calls and add them to the current batch instead of executing them immediatelly.

However, step 3 would currently necessitate having to rewrite a large portion of our code from the (IMO) relatively readable:

records.forEach(record -> {
    record.setX(...);
    // ...
    record.store();
}

to:

userObjects.forEach(userObject -> {
    dslContext.insertInto(...).values(userObject.getX(), ...).execute();
}

which would defeat the purpose of having this abstraction in the first place, since the second form can also be rewritten using DSLContext#batchStore or DSLContext#batchInsert. IMO however, batching and bulk insertion should not be up to the individual developer and should be able to be handled transparently at a higher level (e.g. by the framework).

I find the readability of the jOOQ API to be an amazing benefit of using it, however it seems that it does not lend itself (as far as I can tell) to interception/extension very well for cases such as these. Is it possible, with the jOOQ 3.11.1 (or even current) API, to get behaviour similar to the former with transparent batch/bulk handling? What would this entail?

---

One possible but extremely hacky solution that comes to mind for enabling transparent batching of stores would be something like the following:

1. Create a RecordListener and add it as a default to the Configuration whenever batching is enabled.
2. In RecordListener#storeStart, add the query to the current Transaction’s batch (e.g. in a ThreadLocal<List>)
3. The AbstractRecord has a changed flag which is checked (org.jooq.impl.UpdatableRecordImpl#store0, org.jooq.impl.TableRecordImpl#addChangedValues) prior to storing. Resetting this (and saving it for later use) makes the store operation a no-op.
4. Lastly, upon successful method invocation but prior to commit:
   • Reset the changes flags of the respective records to the correct values
   • Invoke org.jooq.UpdatableRecord#store, this time without the RecordListener or while skipping the storeStart method (perhaps using another ThreadLocal flag to check whether batching has already been performed).

As far as I can tell, this approach should work, in theory. Obviously, it’s extremely hacky and prone to breaking as the library internals may change at any time if the code depends on Reflection to work.

Is there currently a better way, using only the public jOOQ API?

Thanks!

​

[Lukas Eder]

Thanks, I have replied to the stack overflow question directly on there

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/03d3fa96-be8f-442f-bb43-6bd949a38bban%40googlegroups.com.