ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: AW: Transaction Time Out and Stale Connection Exception when using IBatis SQLMapper
Date Wed, 05 Mar 2008 12:38:48 GMT
You have to configure the ping quesry in WebSphere - not in iBATIS.  Here's
a link to the WebSphere documentation that shows how to do it:

http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/index.jsp?topic=/com.ibm.websphere.base.doc/info/aes/ae/tdat_pretestconn.html

Use the search string "pretest sql string" in the info center if the link
doesn't work.

Jeff Butler

On Wed, Mar 5, 2008 at 5:03 AM, IBATIS <venkatesh.madhavan@wipro.com> wrote:

>
> Hi Axel,
>
> Thank you very much for your reply. Is the ping query property is only
> applicable for SimpleDatasource?  I am using Websphere's datasource as per
> my project requirement. Could you please let me know if I can make use of
> the ping query in my sqlmapconfig even in this case.
>
>
>
> Leucht, Axel wrote:
> >
> > I wasn't following this thread, so bear with me if someone already
> > mentioned it...
> >
> > I suppose that you're connection pool is thinking the connection to the
> > database to be valid when in fact it isn't. You can setup a "ping query"
> > with almost every ConnectionPool to let the pool check whether the
> > connection is still valid before it delivers it to you're application.
> For
> > a SimpleDataSource with iBATIS you can setup such thing under a
> > Pool.PingQuery. Read the SQL Maps documentation (on page 68).
> >
> > Hope that helps.
> >
> > /Axel
> >
> >
> >>>-----Ursprüngliche Nachricht-----
> >>>Von: IBATIS [mailto:venkatesh.madhavan@wipro.com]
> >>>Gesendet: Mittwoch, 5. März 2008 11:13
> >>>An: user-java@ibatis.apache.org
> >>>Betreff: Re: Transaction Time Out and Stale Connection Exception when
> >>>using IBatis SQLMapper
> >>>
> >>>
> >>>
> >>>Hi,
> >>>
> >>>Thanks for the advise. I would like to state here that I have not
> >>>deliberately put my questions 6 times. It was happened due to
> >>>my lack of
> >>>knowledge to post the queries in this forum. Since my query was not
> >>>appearing on the forum, I thought that I can post directly
> >>>through mail.
> >>>Anyways sorry about that.
> >>>
> >>>Coming back to the problem, I am not using any container managed
> >>>persistence, I am using Bean managed persistence type of
> >>>stateless session
> >>>bean only. Hence in this case, I believe the JNDI Datasource
> >>>should hold
> >>>good (correct me if I am wrong).
> >>>
> >>>Clarification required regarding Stale Connection Exception.
> >>>
> >>>I am getting often this stale connection exception when my
> >>>application is
> >>>idle for 1 hour and for the very first request for database operation.
> >>>
> >>>I tried retrying for the very first db operation in case of
> >>>stale connection
> >>>exception. I could able to retry this but since this is
> >>>happening within a
> >>>single EJB's transaction, I am getting transaction rollback exception.
> >>>
> >>>Is there anyother way in which I can overcome this exception.
> >>>
> >>>See my below approach.
> >>>
> >>>1. My Ejb calls my dao.
> >>>2. In my DAO, I am starting the sqlmapper transaction
> >>>3. trying to invoke a db query
> >>>4. comiting in case of successful db execution.
> >>>5. In case of error, I am calling the same method from the
> >>>exception block
> >>>only once.
> >>>6. In my finally block I am ending my sqlmapper transaction.
> >>>
> >>>Code is as below.
> >>>
> >>>MyEJB (Stateless Session EJB)
> >>>     public String validateUserID(String userID)throws Exception
> >>>     {
> >>>             new MYDAO().validateUser(userID);
> >>>     }
> >>>
> >>>MyDAO
> >>>     public String validateUser ( String userID) throws
> >>>MYDAOException
> >>>     {
> >>>             try
> >>>             {
> >>>                     HashMap hshUser = new HashMap();
> >>>                     hshUser.put("userID", userID);
> >>>
> >>>                     getSqlMapper().startTransaction();
> >>>                     String usrStatus = (String)
> >>>getSqlMapper().queryForObject("validateUserID",
> >>>                                     hshUser);
> >>>                     getSqlMapper().commitTransaction();
> >>>                     return usrStatus;
> >>>
> >>>             }
> >>>             catch(Exception sqlMapEX)
> >>>             {
> >>>
> >>>                     if(isRetryAllowed())
> >>>                     {
> >>>                             retryAllowed = false;
> >>>                             return validateUser (userID);
> >>>                     }
> >>>
> >>>                     throw new MyDAOException(sqlMapEX);
> >>>             }
> >>>             finally
> >>>             {
> >>>                     try
> >>>                     {
> >>>                             getSqlMapper().endTransaction();
> >>>                     }
> >>>                     catch(Exception ex)
> >>>                     {
> >>>                             MyLogger.logFatal("Error while
> >>>ending Transaction "+ex.getMessage());
> >>>                     }
> >>>             }
> >>>     }
> >>>
> >>>
> >>>In the above you can see that I am retrying only once. For
> >>>the second time I
> >>>am not getting any exception from the method validateDeviceID of MyDAO
> >>>rather I am getting exception from Ejb container saying that
> >>>transaction is
> >>>rolledback.
> >>>
> >>>Please clarify what could be wrong.
> >>>
> >>>Thanks in advance.
> >>>
> >>>Regards
> >>>Venkat
> >>>
> >>>
> >>>
> >>>Christopher Lamey wrote:
> >>>>
> >>>> First, some etiquette advise is in order.  It is considered
> >>>a faux pas to
> >>>> send the same message to the list 6 times in a short amount of time.
> >>>> Posting something more will not get you more or quicker
> >>>responses.  Also,
> >>>> sending the same message directly to members of the list
> >>>immediately after
> >>>> posting to the list itself is not going to give you a boost
> >>>in help.  In
> >>>> fact, it will probably make people ignore you.
> >>>>
> >>>> As to your problem, you currently have your JNDI DataSource
> >>>setup as a
> >>>> JDBC
> >>>> source.  Is this true or are you using a container managed
> >>>DataSource?  If
> >>>> you are using something like a JTA provider through WAS,
> >>>you will need
> >>>> something like this:
> >>>>
> >>>> <transactionManager type="JTA" >
> >>>>     <property name="UserTransaction"
> >>>value="java:/comp/UserTransaction"/>
> >>>>     <dataSource type="JNDI">
> >>>>         <property name="DataSource"
> >>>value="java:comp/env/jdbc/jpetstore"/>
> >>>>     </dataSource>
> >>>> </transactionManager>
> >>>>
> >>>> Please see page 16 of the iBATIS PDF manual for details.
> >>>>
> >>>> Basically you're binding a DataSource into JNDI and you
> >>>need to configure
> >>>> the DataSource there, not in iBATIS.  So if you're binding
> >>>a one via DBCP,
> >>>> you could configure the idle time or a ping query or whatever.
> >>>>
> >>>> Cheers,
> >>>> topher
> >>>>
> >>>> On 2/15/08 6:38 AM, "venkatesh.madhavan@wipro.com"
> >>>> <venkatesh.madhavan@wipro.com> wrote:
> >>>>
> >>>>> Hi,
> >>>>>
> >>>>> I am using WAS 6.0 Datasource inside my Ibatis sql mapper.
> >>>The code in
> >>>>> my sqlmapconfig.xml is as follows.
> >>>>>
> >>>>>         <transactionManager type="JDBC" commitRequired="true">
> >>>>>                 <dataSource type="JNDI">
> >>>>>                         <property name="DataSource"
> >>>>>                                 value="jdbc/MyDataSrc" />
> >>>>>                 </dataSource>
> >>>>>         </transactionManager>
> >>>>>
> >>>>> I am calling the database operations from my EJB which is
> >>>stateless and
> >>>>> Bean Managed. The code inside my EJB is as follows.
> >>>>>
> >>>>> EJB:public MySOAPEnvelope processDownload ( MyReqEnvelope
> >>>>> reqEnvelope)throws MySessionBeanException
> >>>>> {
> >>>>> try
> >>>>> {
> >>>>>        MySQLConfig.getInstance().getSqlMapper().startTransaction();
> >>>>>
> >>>>> //Invoke business logic classes MyBlogicClass
> >>>>>
> >>>MySQLConfig.getInstance().getSqlMapper().commitTransaction();
> >>>>> return object;
> >>>>> }
> >>>>> catch(Exception ex)
> >>>>> {
> >>>>>  throw new MySessionBeanException(ex.getMessage());
> >>>>> }
> >>>>> finally
> >>>>> {
> >>>>>   try
> >>>>>   {
> >>>>>     MySQLConfig.getInstance().getSqlMapper().endTransaction(); }
> >>>>>   catch(Exception ex)
> >>>>>  {
> >>>>>    MyLogger.logError (" error while commiting and closing
> >>>connection
> >>>>> "+ex);
> >>>>> }
> >>>>> }
> >>>>>
> >>>>>         }
> >>>>>
> >>>>> MyBlogicClass calls MyDAO inside this DAO class I am invoking the
> >>>>> database query which are defined in the IBatis SQLMapper
> >>>xml files and
> >>>>> returned back the result to MyBlogicClass. Which inturn
> >>>return to MyEJB.
> >>>>>
> >>>>>
> >>>>>
> >>>>> Since I am using IBatis for the first time in my project.
> >>>I wanted to
> >>>>> know whether the way in which I am doing is correct with respect
> >>>>> datasource connection pool handling and other things. The
> >>>reason for
> >>>>> which I am asking is, at times I am getting transaction timed out
> >>>>> exception while executing some queries which normally does
> >>>not happens.
> >>>>> Also When I am getting the connection for the very first
> >>>time and not
> >>>>> utilising it for long then I am getting the Stale
> >>>connection exception.
> >>>>> I think this is somewhat obvious but please let me know if
> >>>I am doing
> >>>>> something wrong due to which I am getting this exception.
> >>>Also is there
> >>>>> anyway by which we can retry while I am getting this stale
> >>>connection
> >>>>> exception.
> >>>>>
> >>>>> My Doubts are Clarifications are as follows.
> >>>>>
> >>>>> 1. Whether the Datasource settings described in my
> >>>SQLMapConfig.xml is
> >>>>> correct or is there any other properties I need to set in
> >>>the same in
> >>>>> order to properly commit or rollback and close the
> >>>connection so that
> >>>>> the usage Connection Object from the datasource connection pool
is
> >>>>> utilised minimal.
> >>>>>
> >>>>> 2. Whether the code which I have put in my ejb is OK.
> >>>>>
> >>>>> To conclude, I would like to know whether the problem behind this
> >>>>> transaction timed out and stale connection exception is
> >>>due to way in
> >>>>> which I am handling the connection through IBatis or its
> >>>due to database
> >>>>> server or network issue.
> >>>>>
> >>>>> For your reference I am giving a sample log messages which
> >>>I get from
> >>>>> the IBatis (hoping that you could get some idea about the
> >>>connection
> >>>>> usage).
> >>>>>
> >>>>> 2008/02/13 12:40:21,156: <DEBUG> {conn-100000} Connection
> >>>>> 2008/02/13 12:40:21,203: <DEBUG> {conn-100000} Preparing Statement:
> >>>>> Query A
> >>>>> 2008/02/13 12:40:25,766: <DEBUG> {conn-100000} Preparing Statement:
> >>>>> Query B
> >>>>> 2008/02/13 12:40:43,031: <DEBUG> {conn-100000} Preparing Statement:
> >>>>> Query C
> >>>>> 2008/02/13 12:41:19,078: <DEBUG> {conn-100007} Connection
> >>>>> 2008/02/13 12:41:19,094: <DEBUG> {conn-100007} Preparing Statement:
> >>>>> Query A
> >>>>> 2008/02/13 12:41:19,812: <DEBUG> {conn-100007} Preparing Statement:
> >>>>> Query B
> >>>>> 2008/02/13 12:44:21,516: <DEBUG> {conn-100012} Connection
> >>>>> 2008/02/13 12:44:21,516: <DEBUG> {conn-100012} Preparing Statement:
> >>>>> Query A
> >>>>> 2008/02/13 12:44:21,812: <DEBUG> {conn-100012} Preparing Statement:
> >>>>> Query B
> >>>>> 2008/02/13 12:47:15,000: <DEBUG> {conn-100017} Connection
> >>>>> 2008/02/13 12:47:15,000: <DEBUG> {conn-100017} Preparing Statement:
> >>>>> Query A
> >>>>> 2008/02/13 12:47:15,281: <DEBUG> {conn-100017} Preparing Statement:
> >>>>> Query B
> >>>>> 2008/02/13 12:55:08,875: <DEBUG> {conn-100000} Connection
> >>>>> 2008/02/13 12:55:08,891: <DEBUG> {conn-100000} Preparing Statement:
> >>>>> Query A
> >>>>> 2008/02/13 12:55:09,406: <DEBUG> {conn-100000} Preparing Statement:
> >>>>> Query B
> >>>>> 2008/02/13 12:55:52,047: <DEBUG> {conn-100005} Connection
> >>>>> 2008/02/13 12:55:52,047: <DEBUG> {conn-100005} Preparing Statement:
> >>>>> Query A
> >>>>> 2008/02/13 12:55:52,344: <DEBUG> {conn-100005} Preparing Statement:
> >>>>> Query B
> >>>>> 2008/02/13 12:59:46,109: <DEBUG> {conn-100010} Connection
> >>>>> 2008/02/13 12:59:46,109: <DEBUG> {conn-100010} Preparing Statement:
> >>>>> Query A
> >>>>> 2008/02/13 12:59:47,172: <DEBUG> {conn-100010} Preparing Statement:
> >>>>> Query B
> >>>>>
> >>>>>
> >>>>>
> >>>>> Awating response at the earliest.
> >>>>>
> >>>>> Thanks in advance.
> >>>>> Venkat
> >>>>>
> >>>>>
> >>>>> The information contained in this electronic message and
> >>>any attachments
> >>>>> to
> >>>>> this message are intended for the exclusive use of the
> >>>addressee(s) and
> >>>>> may
> >>>>> contain proprietary, confidential or privileged
> >>>information. If you are
> >>>>> not
> >>>>> the intended recipient, you should not disseminate,
> >>>distribute or copy
> >>>>> this
> >>>>> e-mail. Please notify the sender immediately and destroy
> >>>all copies of
> >>>>> this
> >>>>> message and any attachments.
> >>>>>
> >>>>> WARNING: Computer viruses can be transmitted via email.
> >>>The recipient
> >>>>> should
> >>>>> check this email and any attachments for the presence of
> >>>viruses. The
> >>>>> company
> >>>>> accepts no liability for any damage caused by any virus
> >>>transmitted by
> >>>>> this
> >>>>> email.
> >>>>>
> >>>>> www.wipro.com
> >>>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>-----
> >>>Venkat
> >>>--
> >>>View this message in context:
> >
> http://www.nabble.com/Transaction-Time-Out-and-Stale-Connection-Exception-when-using-IBatis-SQLMapper-tp15496139p15846998.html
> > Sent from the iBATIS - User - Java mailing list archive at Nabble.com<http://nabble.com/>
> .
> >
> >
> >
>
>
> -----
> Venkat
> --
> View this message in context:
> http://www.nabble.com/Transaction-Time-Out-and-Stale-Connection-Exception-when-using-IBatis-SQLMapper-tp15496139p15847799.html
>  Sent from the iBATIS - User - Java mailing list archive at Nabble.com<http://nabble.com/>
> .
>
>

Mime
View raw message