• Post Reply Bookmark Topic Watch Topic
  • New Topic

how to maintain transcation consistency between DB procedures and EJB

 
jayant shanker
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am involved in a project in which the sql procedures are called by the ejb components.We later on call these ejb components to complete one process.Now the problem is such that while completeing one process we have to call 5 or 6 ejb componnets which in itself are all transcation "Required" and call SQL procedures.Now if there is some problem arising in supoose 5th or the 6th component like some validation error.The Right thing would be to roll back all the previous transcation but in our case most of the previous trancation are rolled back but some are not ,inspite of all the ejb components are trancation "required".Can somebody guide me understanding and solving this problem..
 
Valentin Tanase
Ranch Hand
Posts: 704
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi jayant,

Please specify couple of more things:
  • Do you commit/rollback transactions in your stored procedures as well?
  • How do you rollback transactions in your ejb code? Do you throw application exceptions/runtime exceptions, or use ctx.setRollbackOnly()?

  •  
    Manav Arora
    Greenhorn
    Posts: 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi Valentin,
    I am Jayant's friend and working on the same project.so I m also facing the same problem.The answers to your questions are:
    1) We are not commiting in the strored procedure so the commit/rollback is handled in the java code itself.
    2)we are using ctx.rollbackonly(); in case of any error.

     
    Roger Chung-Wee
    Ranch Hand
    Posts: 1683
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    As you are using CMT, you must not commit! You must either let the container commit or rollback the transaction (if a system exception is thrown by the bean or container, or if you invoke EJBContext.setRollbackOnly()).
     
    Valentin Tanase
    Ranch Hand
    Posts: 704
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi Manav,

    Roger is right; with CMT you must not commit the transactions in your code at all. I would also expect the container to throw an exception if you do that. Actually the next calls are not allowed in CMT:
  • The commit, setAutoCommit, and rollback methods of java.sql.Connection
  • The getUserTransaction method of javax.ejb.EJBContext
  • Any method of javax.transaction.UserTransaction


  • Please clarify if you are calling any of these methods.
    Regards.
     
    Sachin Patil
    Ranch Hand
    Posts: 39
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    What if my 2nd call depends on 1st call.

    e.g.
    call 1 >> Create user
    call 2 >> associate group with user

    Now unless the 1st call is committed, 2nd call is useless.
    How the appserver will handles such situation?
    (I am talking about CMT)
     
    Valentin Tanase
    Ranch Hand
    Posts: 704
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    If both calls are executed within the same transaction, then it won�t be a problem at all. If they run in separate transactions then you probably should review your design, because if they both belong to the same unit of work, but run in different transaction then your design will defeat the whole purpose of transactions. However if you keep them in different transactions then you must make sure that the first transaction commits successfully before starting the second one.
    Regards.
     
    jayant shanker
    Greenhorn
    Posts: 5
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi Valentine..
    I have observed the replies above .We are not using commit or setAutoCommit(of java.sql.connection)in our ejb componenets but yes in the exception block we are using ctx.setRollback method.
    What happens if there is some problem in getting the sql procedure rightly excuted and it does not throw error or exception which therefore in the ejb component java code is not getting into Exception block.then ctx.setRollback won't be called ..
    Now the scenerio is like the 4th componenet call the sql procedure and it get excuted ,the ejb process moves into 5th or say 6th component. and there is some validation error.and therefore it goes into exception block and excutes setRollback .So my question is that Are the setRollback method called by container will make the database consistent and make our previous sql procedures which were rightly executed can also be roll backed by this way ...
     
    Khyati Rana
    Greenhorn
    Posts: 6
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi,

    Just a query, we are using a CMT Bean without an explicit Java con.commit(); or con.rollback(), and we are making a call to PL/SQL which internally does a commit, then are actually violating the CMT Spec?
    Or is it that commit() is allowed in PL/SQL irrespective from whether it is called from a CMT or a BMT?
    Also when we are deploying our application on Weblogic Server 8.1 SP2, it doesnt give any Runtime Error and things work fine.

    Any replies will be appreciated.

    Thanks,
    Khyati
     
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!