Multiple "DB operations" within iBatis [+Spring] Transaction

Question

How do I achieve the Transaction involving multiple DB operations to >1 tables using iBatis & Spring?

Let me explain in detail:
I have 2 tables A & B with Master-details relationship. [Both tables in single database].

/* Table A: */

a_id [Primary Key]
[plus other columns]

/* Table B: */

b_id [Primary Key]  
a_id [Foreign Key = PK of table A]  
[plus other columns]

In my Dao I have following methods (I am using iBatis sqlMap toperform DB operations):

insertA();  
insertB();  
updateA();  
updateB();  
deleteA();  
deleteB();

Each of the above operations are Atomic (& can be called by client & commited in database -via Spring/iBatis).

Up to this point everything WORKS OK! [i.e. I am able to perform INDIVIDUAL insert/update/delete on each table.]

-- NEXT, I need to perform a combination of two of above DB operations as an ATOMIC operation;
Here is what I want to achieve from SVC layer:

start Tranaction  
    operation on Table-A (via method of Dao class) - op #1  
    operation on Table-B (via method of Dao class) - op #2  
end Transaction

Example1:

start Tranaction  
    insertA();  
    insertB();  
end Transaction

Example2:

start Tranaction  
    updateA();  
    updateB();  
end Transaction

Here, if op#2 Fails, I want op#1 also to be Rolled back. i.e. Complete Rollback.

So, I wrote additional method within the Service layer, which calls above DAO methods. Before running the (Svc) code, I manually [via cmd-line] change some data On database, so that 2nd operation FAILS due to DB Constraints.

Now, op #2 [Table-B] FAILS, but op #1 is commited in DB. i.e. there is NO complete rollback, ONLY PARTIAL rollback.

If op #2 Fails, shouldn't op#1 also Roll back?

Here is what I am using in ApplicationContext.xml:

"DataSourceTransactionManager" [Spring] for Transaction.
iBatis 2.3.x [SqlMapClient]
Spring 3.0
DefaultAutoCommit is set to FALSE.
In "tx:method": [service method from where ATOMIC operation is to be performed)
propagation="REQUIRED" [Tried with other values also, but no use]
rollback-for=Exception-Name-for-which-to-rollback

Is there anything else that needs to be done?
Am I doing something wrong?
Is this correct way or is there a better option?

score 0 · Answer 1 · answered Jun 26 '12 at 07:47

<

In my opinion, you should consider the data integrity, if op #2 make the system loose data integrity, then it should roll back according to op #1.

To achieve what you want, just make a call to op #1 and #2, wrapper #2 on try/catch block, something like:

try {

    start Tranaction ;
    //pkA is primary key of A
    Object pkA = insertA();  
    updateA(pkA);  

    try {
        Object pkB = insertB(pkA); 
        updateB(pkB);  
    }
    catch(Exception e) {
        logger.ERROR("Error when inserting and updating B.Ignore. ",e);
    }
    commit transaction;
}
catch(Exception e) {
   logger.ERROR(e);
   rollback Transaction;
}

HTH.

Multiple "DB operations" within iBatis [+Spring] Transaction

1 Answers1