Is there a mechanism to retry a query on SQLException

[Anthony Calce]

Hello,

My standard code for database calls looks like the following

try (KrJooqWrapper wrapper = new KrJooqWrapper(DbType.MAIN)) {

int rows = wrapper.getCreate()

.deleteFrom(TABLE)

.where(TABLE.ID.eq(123))

.execute();

return rows > 0;

}

catch (Exception e) {

Log.exception(e);

return false;

}

I am currently using a custom DefaultExecuteListener which does

• SQL query logging on exception
• SQL query logging if in debug mode
• On render end, block any UPDATE or DELETE that does not have a where clause
• timeout on long queries

My question is, is there a way I can retry a query/transaction on a SQLException (in case of deadlocks, for example) so that I do not have to re-write my application code (by wrapping it around a retry function).  I could probably rerun the query in the DefaultExecuteListener exception handler, but that would require another connection, probably.  Plus it would still throw the original exception to the application.

[Lukas Eder]

Hi Anthony,

There is currently no such feature in jOOQ, although the idea has been discussed before, and I really like it. The pending feature request is:

https://github.com/jOOQ/jOOQ/issues/5905

I'm not sure when this will ship, yet. There are a lot of "retryables" such as individual queries, query "groups" or batches, transactions, etc.

I think the retry function would be the most pragmatic approach here. You could create an API like:

// Retry this at most 3 times

retry(3, () -> {

  db.deleteFrom(TABLE).where(TABLE.ID.eq(123)).execute();

});

Hope this helps,

Lukas

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Anthony Calce]

Hey Lukas,

Thanks for the quick response. I'll take your suggestion and do it that way for now

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.