How to handle JDBC transaction the right way?

[gtcc2009]

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?

 

[Andy]

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.

 

[Simon OUALID]

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.

 

[gtcc2009]

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. :-(

 

[bugbear]

Suppose I have this code:
public void batchWork () throws BusinessProcessException {

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?

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

 

[Rob Mitchell]

[posted and mailed]

(e-mail address removed) wrote in @g44g2000cwa.googlegroups.com:

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?

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