• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem in Two-phase Commit

 
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
We have done an enterprise application (we use IBM WAS 5.1) which has MDBs which reads messages from various Queues (IBM Websphere 5.3 with CSD0011). MDBs then calls a local stateless session bean (CMT) which indeed calls Oracle 9i stored procedure based on message type.Based on the output of the stored procedure (0 - for unsuccess & 1 - success) we are rollbacking the transaction.

We faced problem when we rollback. We used ojdbc14.jar. I set Maximum Messages as 150 and Maximum session as 1 for listeners in WAS. I created XA enabled Queue Connection Factory by selected the checkbox Enable XA. I made several changes in various areas but still the same status.I don't know how to tackle this issue. Sometimes I am getting exceptions like :

[11/12/05 17:13:55:297 AST] 5edb3e99 ExceptionUtil E CNTR0019E: Non-application exception occurred while processing method "callStoredProc". Exception data: com.ibm.websphere.csi.CSITransactionRolledbackException: Transaction rolled back
at com.ibm.ejs.csi.TransactionControlImpl.getCurrentTransactionalUOW(TransactionControlImpl.java(Compiled Code))
at com.ibm.ejs.csi.TransactionControlImpl.preInvoke(TransactionControlImpl.java(Compiled Code))
at com.ibm.ejs.container.EJSContainer.preInvoke_internal(EJSContainer.java(Compiled Code))
at com.ibm.ejs.container.EJSContainer.preInvoke(EJSContainer.java(Inlined Compiled Code))
at fix.procedure.EJSLocalStatelessStoredProcSession_ef2f36b3.callStoredProc(Unknown Source)
at exe.rpt.Group05MDBBean.onMessage(Unknown Source)
at com.ibm.ejs.jms.listener.MDBWrapper$PriviledgedOnMessage.run(MDBWrapper.java(Compiled Code))
at com.ibm.ejs.jms.listener.MDBWrapper.callOnMessage(MDBWrapper.java(Compiled Code))
at com.ibm.ejs.jms.listener.MDBWrapper.onMessage(MDBWrapper.java(Compiled Code))
at com.ibm.mq.jms.MQSession.run(MQSession.java(Compiled Code))
at com.ibm.ejs.jms.JMSSessionHandle.run(JMSSessionHandle.java:923)
at com.ibm.ejs.jms.listener.ServerSession.connectionConsumerOnMessage(ServerSession.java:697)
at com.ibm.ejs.jms.listener.ServerSession.onMessage(ServerSession.java:482)
at com.ibm.ejs.jms.listener.ServerSession.dispatch(ServerSession.java:449)
at sun.reflect.GeneratedMethodAccessor57.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java(Compiled Code))
at java.lang.reflect.Method.invoke(Method.java(Compiled Code))
at com.ibm.ejs.jms.listener.ServerSessionDispatcher.dispatch(ServerSessionDispatcher.java:37)
at com.ibm.ejs.container.MDBWrapper.onMessage(MDBWrapper.java:91)
at com.ibm.ejs.container.MDBWrapper.onMessage(MDBWrapper.java:127)
at com.ibm.ejs.jms.listener.ServerSession.run(ServerSession.java:372)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)


1) How to check that Oracle 9i is XA Enabled?
2) Is there anything to do in Oracle Stored Procedure to support distributed Transaction?
3) Do I need to change anything in the ejb-jar.xml file to make MDBs onMessage() method as Required or NotSupported and session bean's StoredProcSessionBean as RequiresNew.
4) Or else do I need to go for Bean Managed Transaction.

PLS. Give me the Solution.


My MDB Code (Some Portions) :
==============================

public void onMessage(javax.jms.Message msg)
{
String txtMsg = null;
try
{
if (msg instanceof BytesMessage)
{
BytesMessage bytesMessage = (BytesMessage)msg;
byte[] buff = new byte[5000];

int totBytesRead = bytesMessage.readBytes(buff);
txtMsg = new String(buff,0,totBytesRead);

MsgParser parser = new MsgParser(txtMsg);
String [] msgPart = parser.getParsedMsg();

if (txtMsg !=null)
{
StoredProcSessionLocal storedProcBean = storedProcHome.create();
storedProcBean.callStoredProc("Group01", msgPart);
}
else
{
logger.info("MsgPart is null. Stored Procedure is not called "+txtMsg);
this.getMessageDrivenContext().setRollbackOnly();
}
}
catch(JMSException jmse)
{
logger.error("JMSException in Group01MDB: " + jmse + " ~~~ "+txtMsg);
if (!this.getMessageDrivenContext.getRollbackOnly()) {
this.getMessageDrivenContext.setRollbackOnly();
}
}
catch(Exception e)
{
logger.error("Exception in Group01MDB: " + e+" ~~~ "+txtMsg);
if (!this.getMessageDrivenContext.getRollbackOnly()) {
this.getMessageDrivenContext.setRollbackOnly();
}
}
}
}


My Stateless Session Bean Code (Some Portions) :
================================================

public void ejbCreate() throws javax.ejb.CreateException {
logger = Logger.getLogger("StoredProcSessionBean");
try
{
InitialContext initCtx = new InitialContext();
ds = (DataSource) initCtx.lookup("jdbc/fix");
}
catch(Exception e)
{
logger.error("StoredProcSessoionBean : Exception while Creating DataSource "+e);
}
}


public void ejbRemove() {
try
{
if(cStmt != null)
cStmt.close();
}
catch(SQLException e)
{
logger.error("StoredProcSessoionBean : Exception while closing callable statement " + e);
}
}


public void callStoredProc(String queueName, String[] msgPart) throws Exception
{
try {

if ((ds == null) || (msgPart.length == 0))
{
logger.fatal("StoredProcSessoionBean : Datasource is null or Message is null");
this.getSessionContext().setRollbackOnly();
return;
}

long msgId = new Long(msgPart[0]).longValue();
String msgType = msgPart[1];
String message = msgPart[2];

try
{
conn = ds.getXAConnection();
conn.setAutoCommit(false);
}
catch(SQLException e)
{
logger.error("StoredProcSessoionBean : Exception while getting DB Connection " + e
this.getSessionContext().setRollbackOnly();
return;
}

try
{
if (msgType.equals("8"))
{
cStmt = conn.prepareCall("{call PROC_APP_IW_EXECUTION_RPT(?,?,?,?)}");
}
else if (msgType.equals("9"))
{
cStmt = conn.prepareCall("{call PROC_APP_IW_ORD_CANCEL_REJ(?,?,?,?)}");
}
else if (msgType.equals("B"))
{
cStmt = conn.prepareCall("{call PROC_APP_IW_NEWS(?,?,?,?)}");
}
else
{
this.getSessionContext().setRollbackOnly();
return;
}

if (cStmt != null)
{
cStmt.registerOutParameter(1, Types.INTEGER);
cStmt.registerOutParameter(2, Types.VARCHAR);
cStmt.setLong(3, msgId);
cStmt.setString(4, msgBuf);
cStmt.execute();
}

if(cStmt.getInt(1) == 1)
{
logger.info("SP Returns Value " + cStmt.getInt(1) + " Message "+ cStmt.getString(2));
return;
}
else
{
logger.info("SP Returns Value " + cStmt.getInt(1) + " Message "+ cStmt.getString(2));
this.getSessionContext().setRollbackOnly();
return;
}
}
catch (Exception e) {
logger.fatal("StoredProcSessoionBean : Exception : " + e);
this.getSessionContext().setRollbackOnly();
return;
}
}
finally
{
try
{
if(conn != null) conn.close();
}
catch(SQLException e)
{
logger.error("StoredProcSessoionBean : Exception while closing DB Connection " + e);
this.getSessionContext().setRollbackOnly();
return;
}
}
}
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Are you sure the databases that are defined as XA data sources can actually support two phase commits? On some db's such as MS SQL Server this requires a patch.
 
Ranch Hand
Posts: 704
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Mohamed,

I�m not a WAS expert but I�ll try to answer to your questions the best I can, since they are mostly J2EE related:


1) How to check that Oracle 9i is XA Enabled?
2) Is there anything to do in Oracle Stored Procedure to support distributed Transaction?
3) Do I need to change anything in the ejb-jar.xml file to make MDBs onMessage() method as Required or NotSupported and session bean's StoredProcSessionBean as RequiresNew.
4) Or else do I need to go for Bean Managed Transaction.


1) You need to check that the driver you�re using (not the database itself) is XA-compatible and is properly configured. For this you need to check WAS documentation and find out how the connection pool and the associate DataSource are defined. You also need to check your driver�s documentation (after you find out which driver you�re using) if it is XA-compatible. If not then you are at your container mercy. Weblogic for example provides a way to allow non-XA drivers to participate in XA transactions. I assume that WAS allows similar settings.
2) This might be a problem. Your CMT session bean uses JTA/JTS transactions in order to support global transactions and the 2PC protocol. If on the other hand you manage transactions inside your SPs (which from your container perspective could be perceived as JDBC transactions), then you are only looking for trouble. The result of such mixture is highly unpredictable. Getting bunch of error messages might make sense though.
3) Because they are all part of the global transaction they should support transactions. The RequiresNew setting for your session bean I don�t really believe is going to fix your problem.
4) If you decide that your SPs should manage the transactions, then you might go with BMP. However in this case you�ll be limited to using JDBC transactions and you won�t be able to have global transactions anymore. Because in the current design your JMS service is part of the global transaction, I�ll assume that you cannot do this anyway, unless you have the power (which I hope you don�t :-))to change the current requirements.
Regards.
 
Scott Selikoff
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Valentin Tanase:

1) You need to check that the driver you�re using (not the database itself) is XA-compatible and is properly configured.



This isn't true, both the driver and the database *must* support 2PC. It is very possible to have a driver that is XA and a database that does not support 2PC.
 
Valentin Tanase
Ranch Hand
Posts: 704
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


This isn't true, both the driver and the database *must* support 2PC. It is very possible to have a driver that is XA and a database that does not support 2PC.


Mea culpa then. My two cents bet however is that Oracle 9i definitely supports XA transaction and this is enabled by default. Mohamed should probably contact the db administrator and make sure this is true, although I�m pretty sure that nobody will intentionally turn this feature off.
Regards.
 
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't know about Oracle 9i, but it's my understanding that XA is not usually enabled for most DBs. Typically, the DBA needs to run a script or GUI to do the XA enabling.
 
Mohsin Kayal
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Many many thanks indeed to all.

I checked the Database and my driver file (ojdbc14.jar) and I find both are supporting XA Transaction.

Still I am getting the same exception. Do I need to do anything in my coding?

If anybody knows how to write Oracle 9i Stored Procedure that supports Distributed Transaction? Pls. let me know.
 
Valentin Tanase
Ranch Hand
Posts: 704
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Mohsin,

Can you please confirm whether you�re managing transactions inside your stored procedures or not?
Regards.
 
Mohsin Kayal
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Mr. Valentine,

I don't know how to manage transactions inside the stored procedure.

Our Stored Procedure is doing lot of DML operations, call some other stored procedures for business process.It won't commit or rollback at any point and it simply returns the value 1 for success and 0 for failure. Based on that value i am doing rollback or commit.

I do not know there is any thing that needs to be done on the stored procedure or in my EJB code itself.

Pls. let me know the link that depicting how to write oracle 9i stored procedure that supports distributed transaction.
 
Valentin Tanase
Ranch Hand
Posts: 704
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Moshin,

Your database should be fine though. However there is couple of more things you should check:
  • Check if your stored procedures execute any DDL operation. As far as I remember, Oracle always commits the current transaction after issuing every DDL operation and they can never be rolled back.
  • Make sure your JMS code is not using transacted sessions.
  • Make sure once more that your connection pool and JMS connection factory are XA-enabled. I�m not a WAS expert, but I know that WebLogic requires certain options to be checked in order to provide XA compatible connections. If they are not set properly, the 2PC protocol won�t be used although your driver & database are XA-enabled.


  • On the other hand, looking through your error stack there is nothing there showing that the failure is a result of a wrongly configured XA resource. It doesn�t say anything about not being able to execute a global transaction at all. The booboo is mostly inside of the stored procedure and it could be even a result of the way the parameters are passed (some certain parameters Java types don�t map very well to their Oracle counterparts). It could also be the reason similar to the one I explain you in 1 too. In my opinion only someone that had a similar problem could tell you exactly what it is.
    If I were you I would try to check if a simple Java client application can call that SP from outside of the container. I�ll do some testing using JDBC transactions and latter JTA transactions. If everything works fine, then I�ll test the same code from within the container (maybe using a servlet first) and so on. Of course it will take some of your time, but if there is no answer or help from anyone else, then what else can you do? Following a scientific approach, one has some input data to which s/he applies some testing procedures and s/he gets a set of knowledge. Next s/he changes the input, repeats the testing and improves the set of knowledge, and so on :-)
    Regards.
     
    Mohsin Kayal
    Greenhorn
    Posts: 23
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Mr. Valentine.

    Finally I have succeeded.

    As per your advice I developed a small enterprise application that calls Oracle SP which contains 2 DML operations inside it. It works fine without having error.

    Now I realised that our business process in Oracle SP may contains some DDL operations. I asked my D2K collegue to dig SP.

    Thanks a lot for ur advice.
     
    Valentin Tanase
    Ranch Hand
    Posts: 704
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    You're very welcome Mohsin. I'm glad I could help.
     
    Don't get me started about those stupid light bulbs.
    reply
      Bookmark Topic Watch Topic
    • New Topic