ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From IBATIS <venkatesh.madha...@wipro.com>
Subject Re: AW: Transaction Time Out and Stale Connection Exception when using IBatis SQLMapper
Date Wed, 05 Mar 2008 12:51:40 GMT

Hi,

I configured the same in the websphere datasource properties. I am waiting
for the connection to become stale. Hopefully this should work.

Could you please let me know whether doing this shall decrease the
performance of my application.

Thanks
Venkat


Jeff Butler-2 wrote:
> 
> 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/>
>> .
>>
>>
> 
> 


-----
Venkat
-- 
View this message in context: http://www.nabble.com/Transaction-Time-Out-and-Stale-Connection-Exception-when-using-IBatis-SQLMapper-tp15496139p15849418.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


Mime
View raw message