Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <15496139.post@talk.nabble.com>
Date: Fri, 15 Feb 2008 00:16:36 -0800 (PST)
From: IBATIS <venkatesh.madhavan@wipro.com>
To: user-java@ibatis.apache.org
Subject: Transaction Time Out and Stale Connection Exception when using
 IBatis SQLMapper
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


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


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