How to handle JDBC transaction the right way?

Discussion in 'Java' started by gtcc2009@yahoo.com, Dec 22, 2005.

  1. Guest

    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?
     
    , Dec 22, 2005
    #1
    1. Advertising

  2. Andy Guest

    >
    > 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.
     
    Andy, Dec 23, 2005
    #2
    1. Advertising

  3. Simon OUALID Guest

    Andy wrote:
    >>
    >> 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.
     
    Simon OUALID, Dec 23, 2005
    #3
  4. Guest

    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. :-(
     
    , Dec 24, 2005
    #4
  5. bugbear Guest

    wrote:
    > 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
     
    bugbear, Jan 3, 2006
    #5
  6. Rob Mitchell Guest

    [posted and mailed]

    wrote in news:1135286391.061605.266530
    @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
     
    Rob Mitchell, Jan 27, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Keith Wansbrough

    Re: jdbc help:sun.jdbc.odbc.JdbcOdbcDriver

    Keith Wansbrough, Aug 16, 2004, in forum: Java
    Replies:
    0
    Views:
    781
    Keith Wansbrough
    Aug 16, 2004
  2. Betty
    Replies:
    1
    Views:
    9,901
    Juha Laiho
    May 21, 2005
  3. John_Woo
    Replies:
    0
    Views:
    326
    John_Woo
    Nov 17, 2006
  4. Vencz Istv?n
    Replies:
    2
    Views:
    304
  5. markspace

    JDBC transaction isolation

    markspace, May 3, 2012, in forum: Java
    Replies:
    16
    Views:
    1,278
Loading...

Share This Page