How to handle JDBC transaction the right way?

Thread view:

gtcc2009@yahoo.com - 22 Dec 2005 21:19 GMT

Suppose I have this code:
public void batchWork () throws BusinessProcessException {
try {
conn.setAutoCommit(false);
performUpdate1();
performUpdate2();
conn.commit();
} catch (SQLException sqle) {
try {conn.rollback();} catch (SQLException sqle2) {}
throw sqle;
}
}

The problem is that if performUpdate1() throws an unchecked exception
(say NullPointerException), then the catch (SQLException sqle) won't be
executed, thus no rollback() is called, and some JDBC drivers will
perform commit in the case a connection is closed with a pending
transaction (neither committed or rollbacked). As a result, the action
done in performUpdate1() is commited, while performUpdated2() is not,
which breaks the purpose of transaction.

Moreover, because the signature of the method is
BusinessProcessException, I cannot catch a Throwable, rollback, then
simply rethrow. Instead, I have to wrap Throwable into
BusinessProcessException and throw it. However it's a very bad coding
practice to "eat" unchecked exception like that.

Is there any workaround for this?

Reply to this Message

Andy - 23 Dec 2005 15:15 GMT

> Is there any workaround for this?

One way would be to set flags that indicate success or failure for each
call and then commit or rollback in a finally clause depending on the flags.

Reply to this Message

Simon OUALID - 23 Dec 2005 18:08 GMT

>> Is there any workaround for this?
>>
> One way would be to set flags that indicate success or failure for each
> call and then commit or rollback in a finally clause depending on the
> flags.

I prefer to use checked exception instead of flags :

Each method within a transaction should take a Connection as input
parameter, and can raise a SQLException.

The service method calls all this DAO transactionnal methods and then
commit, the catch block containing the conn.rollback() method call.

If you have lot of transaction in your application, or if you use
multiple database withing the same transaction, maybe you'd better to
use a JTA implementation.

Reply to this Message

gtcc2009@yahoo.com - 24 Dec 2005 16:47 GMT

Andy, that's a possible workaround. Thanks for your suggestion. But I
would love to know a more efficient "pattern" of handling this :-).

Simon, each method within a transaction does not have to only throw
SQLException, because they are business methods, they can throw more
than that (including some unchecked exceptions) and that's what makes
me asks the question in the first place.

This is really weird to me, all the samples I've found in the net &
books do not mention what happens if case like this happens. :-(

Reply to this Message

bugbear - 03 Jan 2006 16:17 GMT

> Suppose I have this code:
> public void batchWork () throws BusinessProcessException {
[quoted text clipped - 14 lines]
>
> Is there any workaround for this?

Does this take your fancy?

boolean commited = false;
try {
conn.setAutoCommit(false);
performUpdate1();
performUpdate2();
conn.commit();
commited = true;
} finally {
if(!committed)) {
try {conn.rollback();} catch (SQLException sqle2) {}
}
}

I mean, I hate booleans, but given the alternatives...

BugBear

Reply to this Message

Rob Mitchell - 27 Jan 2006 03:24 GMT

[posted and mailed]

gtcc2009@yahoo.com wrote in news:1135286391.061605.266530
@g44g2000cwa.googlegroups.com:

> Suppose I have this code:
> public void batchWork () throws BusinessProcessException {
[quoted text clipped - 24 lines]
>
> Is there any workaround for this?

How about this?

public void batchWork () throws BusinessProcessException {
try {
conn.setAutoCommit(false);
performUpdate1();
performUpdate2();
conn.commit();
} catch (SQLException sqle) {
try {conn.rollback();} catch (SQLException sqle2) {}
throw sqle;
} catch (Exception e) {
try {conn.rollback(); } catch (Exception ee) { /* ignore */ }
}
}

Either that or wrap each performXXX() call to handle catching their own
exceptions and provide a return value which you can check and exit the
method gracefully.

Rob

Reply to this Message

How to handle JDBC transaction the right way?

Free Magazines