ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Leucht, Axel" <Axel.Leu...@prodv.de>
Subject AW: Transaction Time Out and Stale Connection Exception when using IBatis SQLMapper
Date Wed, 05 Mar 2008 10:50:14 GMT
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.


Mime
View raw message