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 11:03:43 GMT

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.
> 
> 
> 


-----
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.


Mime
View raw message