Serializable transaction support in JDBI

[Steven Schlansker]

Hi,

JDBI has a great transaction abstraction with the Handle.inTransaction(TransactionCallback)

With serializable transactions, (http://www.postgresql.org/docs/9.1/static/transaction-iso.html) transactions
may spuriously fail due to other concurrent transactions. The common code path to handle this is by running
the transaction in a loop, where any failure due to a serialization failure is immediately retried.

I haven't seen any support in JDBI for this pattern, and I am wondering if this is an omission because nobody
has wanted it, or if the feature has been considered and rejected. I can see an argument that it is outside
the scope of the project, but on the other hand it'd be a nice feature to have.

I'm currently writing code like this:

dbi.withHandle(new HandleCallback<Void>() {
@Override
public Void withHandle(Handle handle) throws Exception
{
int tries = 0;
while (true)
{
try
{
return handle.inTransaction(TransactionIsolationLevel.SERIALIZABLE,
new TransactionCallback<Void>() {
@Override
public Void inTransaction(Handle conn, TransactionStatus status) throws Exception
{
conn.attach(InternalFlagDao.class).set(accountId, type, target);
return null;
}
});
} catch (UnableToExecuteStatementException e)
{
Throwable cause = e.getCause();
if (cause instanceof SQLException && "40001".equals(((SQLException) cause).getSQLState()))
{
if (tries++ > 50)
{
throw e;
}
LOG.trace(e, "serialization failure");
continue;
}
throw e;
}
}
}
});

and it would be fairly easy (I think) to generalize this and put it in JDBI. Would such a pull request be welcome, or
would I be wasting my time?

Thanks for any guidance,
Steven Schlansker

[Kevin Scaldeferri]

This pattern doesn't seem particularly JDBI (or database) specific.  It seems to me that you are talking about a fairly generic Retryable interface... something along the lines of:

abstract class Retryable<T> {

  public int maxRetries();

  public  T attempt() throws Exception;

  public boolean shouldRetry(Exception e);

  public void onRetry();

  public final T run() throws Exception {

    while (true) {

       ...

    }

  }

}


-kevin

[Steven Schlansker]

Sure, as I said I can see a very good argument that this is not "JDBI-enough" to be there.

But it is a convenient place to put it, and it is useful in this context. And it's not something most people writing a simple DAO would already have lying around -- it's a small addition of code that is tricky enough to write "just right" that it would save people writing their own buggy version. (Some variations I have seen involve grepping them message for "serializability failure" rather than checking the SQLState, for example)

[Brian McCallister]

I'd probably wrap it in something like:

public class Retry<T, E extends Exception> {
private final Exception retryOn;
private final Callable<T> to attempt;

public Retry(Class<E> retryOn, Callable<T> toAttempt) {
this.retryOn = retryOn;
this.toAttempt = toAttempt;
}

Optional<T> attempt(int times) throws Exception {
for (int i = times; i > 0; i--) {
try {
toAttempt.call();
}
catch (Exception e) {
if (! retryOn.assigableFrom(e.getClass()) { throw e; }
}
return Optional.absent();
}
}

public static <T, E extends Exception> Optional<T> retry(int times,
Class<? extends Exception> ec, Callable<T> c) throws Exception {
return new Retry<T, E>(ec, c).attempt(times);
}
}

Though I would probably noodle with inclusion/exclusion of what to
capture as it would make using it nicer.

If using sql object stuff, it would be nice to wrap it up an an
annotation based interceptor. Maybe we should support some generic
means of wrapping sql objects (pass them to something that returns
something that is actually returned) so you could let guice or
somethign else attach behavior.

-Brian

On Wed, May 16, 2012 at 3:04 PM, Steven Schlansker

[Brian McCallister]

That was a longwinded way of saying "I agree with Kevin" -- sorry for
the wordiness!

-Brian

[Steven Schlansker]

I definitely like the improvement of the code, but my core question is yet to find an answer:

"Should this be in JDBI?"

The number of improvements that you guys made to the code I originally posted seems to support my argument that a central "this is done the right way" will be better than everyone writing their own, which IMO is an argument that it should be.

[Brian McCallister]

I have never encountered this before, to be honest, so my initial
reaction is "not until we see it two more times." That said, I pretty
much never run serializable instead of MVCC :-)

Anyone else seeing this problem crop up?

-Brian

On Wed, May 16, 2012 at 3:20 PM, Steven Schlansker