You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Co...@courts.mo.gov on 2005/02/14 21:16:16 UTC
StaleConnectionException
Hello,
I am using websphere 5.1.2, oracle 9i and ibatis 2.0.8. I was using the
following
example from the jpetstore demo as a template for setting up my ibatis
transactions
but crazy things happen when the database has gone down and come back up.
public Order getOrder(int orderId) {
Order order = null;
try {
daoManager.startTransaction();
order = orderDao.getOrder(orderId);
for (int i = 0; i < order.getLineItems().size(); i++) {
LineItem lineItem = (LineItem) order.getLineItems().get(i);
lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
}
daoManager.commitTransaction();
} finally {
daoManager.endTransaction();
}
return order;
}
Websphere likes to throw a StaleConnectionException when a connection in
the pool has gone
bad. At the websphere site ibm shows how to use a construct something
similar
to the code below that retries if the StaleConnectionException is thrown.
// Set retryCount to the number of times you would like to retry after a
StaleConnectionException
int retryCount = 5;
// If the Database code processes successfully, we will set error = false
boolean error = true;
do {
try {
conn = ds.getConnection();
stmt = conn.createStatement();
String query = "Select FirstNme, MidInit, LastName from Employee ORDER
BY LastName";
rs = stmt.executeQuery(query);
while (rs.next()) { employeeList.addElement(rs.getString(3) + ", " +
rs.getString(1) + " " + rs.getString(2)); }
//Set error to false to indicate successful completion of the database
work
error=false;
} catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
// This exception is thrown if a connection can not be obtained from
the
// pool within a configurable amount of time. Frequent occurrences of
// this exception indicate an incorrectly tuned connection pool
System.out.println("Connection Wait Timeout Exception during get
connection or process SQL: " + c.getMessage());
//In general, we do not want to retry after this exception, so set
retry count to 0
retryCount = 0;
} catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
// This exception indicates that the connection to the database is no
longer valid.
// Retry several times to attempt to obtain a valid
//connection, display an error message if the connection still can not
be obtained.
System.out.println("Stale Connection Exception during get connection or
process SQL: " + sc.getMessage());
if (--retryCount == 0) {
System.out.println("Five stale connection exceptions, displaying
error page.");
}
}
} while ( error==true && retryCount > 0 );
And now my question, is there a way to have ibatis automatically retry when
it gets a StaleConnectionException?
Or should each method implement something like below?
public Order getOrder(int orderId) {
Order order = null;
int retryCount = 5;
boolean error = true;
do {
try {
daoManager.startTransaction();
order = orderDao.getOrder(orderId);
for (int i = 0; i < order.getLineItems().size(); i++) {
LineItem lineItem = (LineItem) order.getLineItems().get(i);
lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
}
daoManager.commitTransaction();
error = false;
} catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
System.out.println("Connection Wait Timeout Exception during get
connection or process SQL: " + c.getMessage());
retryCount = 0;
} catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
System.out.println("Stale Connection Exception during get connection
or process SQL: " + sc.getMessage());
if (--retryCount == 0) {
System.out.println("Five stale connection exceptions, displaying
error page.");
}
} finally {
daoManager.endTransaction();
}
} while ( error==true && retryCount > 0);
return order;
}
I am new to websphere and haven't seen a topic like this covered on the
list yet.
thanks
Cory Bestgen
Computer Information Technologist II
Information Technology Division
Office of State Courts Administrator
(573) 522 - 5455
cory.bestgen@courts.mo.gov
Re: StaleConnectionException
Posted by Brice Ruth <bd...@gmail.com>.
Does Websphere have a mechanism for validating connections, much like
Tomcat's DataSource? We simply supply a validation query that either
gets executed *every* time a DataSource is provided to a client -OR-
after a certain timeout (idle time).
This helped us when our iSeries was bounced regularly Sunday mornings.
Brice
On Mon, 14 Feb 2005 14:16:16 -0600, Cory.Bestgen@courts.mo.gov
<Co...@courts.mo.gov> wrote:
>
>
> Hello,
> I am using websphere 5.1.2, oracle 9i and ibatis 2.0.8. I was using the
> following
> example from the jpetstore demo as a template for setting up my ibatis
> transactions
> but crazy things happen when the database has gone down and come back up.
>
> public Order getOrder(int orderId) {
> Order order = null;
> try {
> daoManager.startTransaction();
> order = orderDao.getOrder(orderId);
> for (int i = 0; i < order.getLineItems().size(); i++) {
> LineItem lineItem = (LineItem) order.getLineItems().get(i);
> lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
> }
> daoManager.commitTransaction();
> } finally {
> daoManager.endTransaction();
> }
> return order;
> }
>
> Websphere likes to throw a StaleConnectionException when a connection in
> the pool has gone
> bad. At the websphere site ibm shows how to use a construct something
> similar
> to the code below that retries if the StaleConnectionException is thrown.
>
> // Set retryCount to the number of times you would like to retry after a
> StaleConnectionException
> int retryCount = 5;
> // If the Database code processes successfully, we will set error = false
> boolean error = true;
> do {
> try {
> conn = ds.getConnection();
> stmt = conn.createStatement();
> String query = "Select FirstNme, MidInit, LastName from Employee ORDER
> BY LastName";
> rs = stmt.executeQuery(query);
> while (rs.next()) { employeeList.addElement(rs.getString(3) + ", " +
> rs.getString(1) + " " + rs.getString(2)); }
> //Set error to false to indicate successful completion of the database
> work
> error=false;
> } catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
> // This exception is thrown if a connection can not be obtained from
> the
> // pool within a configurable amount of time. Frequent occurrences of
> // this exception indicate an incorrectly tuned connection pool
> System.out.println("Connection Wait Timeout Exception during get
> connection or process SQL: " + c.getMessage());
> //In general, we do not want to retry after this exception, so set
> retry count to 0
> retryCount = 0;
> } catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
> // This exception indicates that the connection to the database is no
> longer valid.
> // Retry several times to attempt to obtain a valid
> //connection, display an error message if the connection still can not
> be obtained.
> System.out.println("Stale Connection Exception during get connection or
> process SQL: " + sc.getMessage());
> if (--retryCount == 0) {
> System.out.println("Five stale connection exceptions, displaying
> error page.");
> }
> }
> } while ( error==true && retryCount > 0 );
>
> And now my question, is there a way to have ibatis automatically retry when
> it gets a StaleConnectionException?
> Or should each method implement something like below?
>
> public Order getOrder(int orderId) {
> Order order = null;
> int retryCount = 5;
> boolean error = true;
> do {
> try {
> daoManager.startTransaction();
> order = orderDao.getOrder(orderId);
> for (int i = 0; i < order.getLineItems().size(); i++) {
> LineItem lineItem = (LineItem) order.getLineItems().get(i);
> lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
> }
> daoManager.commitTransaction();
> error = false;
> } catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
> System.out.println("Connection Wait Timeout Exception during get
> connection or process SQL: " + c.getMessage());
> retryCount = 0;
> } catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
> System.out.println("Stale Connection Exception during get connection
> or process SQL: " + sc.getMessage());
> if (--retryCount == 0) {
> System.out.println("Five stale connection exceptions, displaying
> error page.");
> }
> } finally {
> daoManager.endTransaction();
> }
> } while ( error==true && retryCount > 0);
> return order;
> }
>
> I am new to websphere and haven't seen a topic like this covered on the
> list yet.
>
> thanks
>
> Cory Bestgen
> Computer Information Technologist II
> Information Technology Division
> Office of State Courts Administrator
> (573) 522 - 5455
> cory.bestgen@courts.mo.gov
>
>
Re: StaleConnectionException
Posted by Brice Ruth <bd...@gmail.com>.
I think Cory is sitting back in his chair with smoke comin' off the
top of his hair. Whew .. that was scorchin'! :)
And absolutely deserved (by WebSphere).
As I indicated - this (very common) problem is something that the
DataSource implementation provided by Tomcat (gratis) handles very
elegantly. If I setup my DataSource correctly, I can rest assured that
as long as the database is up and authentication is successful, my app
will get a valid connection - it (nor iBATIS) needs to know about
anything else, which is how it ought to be!
Brice
On Mon, 14 Feb 2005 13:29:38 -0700, Clinton Begin
<cl...@gmail.com> wrote:
> This is not a criticism of you, but of WebSphere and that "example"....
>
> That is the single most ridiculous thing I have ever seend. That code
> and approach is absolutely stupid. The entire point behind the
> DataSource interface is to hide code like that and deal with
> infrastructure issues in a single location, so you don't have to
> everywhere.
>
> That DataSource is flawed. I suggest you call IBM and tell them this
> exactly. Tell them to fix their crappy datasource and their crappy
> app server.
>
> If they refuse (which they probably will if you've already paid them
> their annual fees), then you could always wrap the datasource with
> your own implementation that will deal with this.
>
> ....phew. Glad to get that off my chest. ;-)
>
> Cheers,
> Clinton
>
>
> On Mon, 14 Feb 2005 14:16:16 -0600, Cory.Bestgen@courts.mo.gov
> <Co...@courts.mo.gov> wrote:
> >
> >
> > Hello,
> > I am using websphere 5.1.2, oracle 9i and ibatis 2.0.8. I was using the
> > following
> > example from the jpetstore demo as a template for setting up my ibatis
> > transactions
> > but crazy things happen when the database has gone down and come back up.
> >
> > public Order getOrder(int orderId) {
> > Order order = null;
> > try {
> > daoManager.startTransaction();
> > order = orderDao.getOrder(orderId);
> > for (int i = 0; i < order.getLineItems().size(); i++) {
> > LineItem lineItem = (LineItem) order.getLineItems().get(i);
> > lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
> > }
> > daoManager.commitTransaction();
> > } finally {
> > daoManager.endTransaction();
> > }
> > return order;
> > }
> >
> > Websphere likes to throw a StaleConnectionException when a connection in
> > the pool has gone
> > bad. At the websphere site ibm shows how to use a construct something
> > similar
> > to the code below that retries if the StaleConnectionException is thrown.
> >
> > // Set retryCount to the number of times you would like to retry after a
> > StaleConnectionException
> > int retryCount = 5;
> > // If the Database code processes successfully, we will set error = false
> > boolean error = true;
> > do {
> > try {
> > conn = ds.getConnection();
> > stmt = conn.createStatement();
> > String query = "Select FirstNme, MidInit, LastName from Employee ORDER
> > BY LastName";
> > rs = stmt.executeQuery(query);
> > while (rs.next()) { employeeList.addElement(rs.getString(3) + ", " +
> > rs.getString(1) + " " + rs.getString(2)); }
> > //Set error to false to indicate successful completion of the database
> > work
> > error=false;
> > } catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
> > // This exception is thrown if a connection can not be obtained from
> > the
> > // pool within a configurable amount of time. Frequent occurrences of
> > // this exception indicate an incorrectly tuned connection pool
> > System.out.println("Connection Wait Timeout Exception during get
> > connection or process SQL: " + c.getMessage());
> > //In general, we do not want to retry after this exception, so set
> > retry count to 0
> > retryCount = 0;
> > } catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
> > // This exception indicates that the connection to the database is no
> > longer valid.
> > // Retry several times to attempt to obtain a valid
> > //connection, display an error message if the connection still can not
> > be obtained.
> > System.out.println("Stale Connection Exception during get connection or
> > process SQL: " + sc.getMessage());
> > if (--retryCount == 0) {
> > System.out.println("Five stale connection exceptions, displaying
> > error page.");
> > }
> > }
> > } while ( error==true && retryCount > 0 );
> >
> > And now my question, is there a way to have ibatis automatically retry when
> > it gets a StaleConnectionException?
> > Or should each method implement something like below?
> >
> > public Order getOrder(int orderId) {
> > Order order = null;
> > int retryCount = 5;
> > boolean error = true;
> > do {
> > try {
> > daoManager.startTransaction();
> > order = orderDao.getOrder(orderId);
> > for (int i = 0; i < order.getLineItems().size(); i++) {
> > LineItem lineItem = (LineItem) order.getLineItems().get(i);
> > lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
> > }
> > daoManager.commitTransaction();
> > error = false;
> > } catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
> > System.out.println("Connection Wait Timeout Exception during get
> > connection or process SQL: " + c.getMessage());
> > retryCount = 0;
> > } catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
> > System.out.println("Stale Connection Exception during get connection
> > or process SQL: " + sc.getMessage());
> > if (--retryCount == 0) {
> > System.out.println("Five stale connection exceptions, displaying
> > error page.");
> > }
> > } finally {
> > daoManager.endTransaction();
> > }
> > } while ( error==true && retryCount > 0);
> > return order;
> > }
> >
> > I am new to websphere and haven't seen a topic like this covered on the
> > list yet.
> >
> > thanks
> >
> > Cory Bestgen
> > Computer Information Technologist II
> > Information Technology Division
> > Office of State Courts Administrator
> > (573) 522 - 5455
> > cory.bestgen@courts.mo.gov
> >
> >
>
Re: StaleConnectionException
Posted by Jerry Jalenak <je...@gmail.com>.
Clinton,
How do you *really* feel?
8-)
Jerry
On Mon, 14 Feb 2005 13:29:38 -0700, Clinton Begin
<cl...@gmail.com> wrote:
> This is not a criticism of you, but of WebSphere and that "example"....
>
> That is the single most ridiculous thing I have ever seend. That code
> and approach is absolutely stupid. The entire point behind the
> DataSource interface is to hide code like that and deal with
> infrastructure issues in a single location, so you don't have to
> everywhere.
>
> That DataSource is flawed. I suggest you call IBM and tell them this
> exactly. Tell them to fix their crappy datasource and their crappy
> app server.
>
> If they refuse (which they probably will if you've already paid them
> their annual fees), then you could always wrap the datasource with
> your own implementation that will deal with this.
>
> ....phew. Glad to get that off my chest. ;-)
>
> Cheers,
> Clinton
>
>
> On Mon, 14 Feb 2005 14:16:16 -0600, Cory.Bestgen@courts.mo.gov
> <Co...@courts.mo.gov> wrote:
> >
> >
> > Hello,
> > I am using websphere 5.1.2, oracle 9i and ibatis 2.0.8. I was using the
> > following
> > example from the jpetstore demo as a template for setting up my ibatis
> > transactions
> > but crazy things happen when the database has gone down and come back up.
> >
> > public Order getOrder(int orderId) {
> > Order order = null;
> > try {
> > daoManager.startTransaction();
> > order = orderDao.getOrder(orderId);
> > for (int i = 0; i < order.getLineItems().size(); i++) {
> > LineItem lineItem = (LineItem) order.getLineItems().get(i);
> > lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
> > }
> > daoManager.commitTransaction();
> > } finally {
> > daoManager.endTransaction();
> > }
> > return order;
> > }
> >
> > Websphere likes to throw a StaleConnectionException when a connection in
> > the pool has gone
> > bad. At the websphere site ibm shows how to use a construct something
> > similar
> > to the code below that retries if the StaleConnectionException is thrown.
> >
> > // Set retryCount to the number of times you would like to retry after a
> > StaleConnectionException
> > int retryCount = 5;
> > // If the Database code processes successfully, we will set error = false
> > boolean error = true;
> > do {
> > try {
> > conn = ds.getConnection();
> > stmt = conn.createStatement();
> > String query = "Select FirstNme, MidInit, LastName from Employee ORDER
> > BY LastName";
> > rs = stmt.executeQuery(query);
> > while (rs.next()) { employeeList.addElement(rs.getString(3) + ", " +
> > rs.getString(1) + " " + rs.getString(2)); }
> > //Set error to false to indicate successful completion of the database
> > work
> > error=false;
> > } catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
> > // This exception is thrown if a connection can not be obtained from
> > the
> > // pool within a configurable amount of time. Frequent occurrences of
> > // this exception indicate an incorrectly tuned connection pool
> > System.out.println("Connection Wait Timeout Exception during get
> > connection or process SQL: " + c.getMessage());
> > //In general, we do not want to retry after this exception, so set
> > retry count to 0
> > retryCount = 0;
> > } catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
> > // This exception indicates that the connection to the database is no
> > longer valid.
> > // Retry several times to attempt to obtain a valid
> > //connection, display an error message if the connection still can not
> > be obtained.
> > System.out.println("Stale Connection Exception during get connection or
> > process SQL: " + sc.getMessage());
> > if (--retryCount == 0) {
> > System.out.println("Five stale connection exceptions, displaying
> > error page.");
> > }
> > }
> > } while ( error==true && retryCount > 0 );
> >
> > And now my question, is there a way to have ibatis automatically retry when
> > it gets a StaleConnectionException?
> > Or should each method implement something like below?
> >
> > public Order getOrder(int orderId) {
> > Order order = null;
> > int retryCount = 5;
> > boolean error = true;
> > do {
> > try {
> > daoManager.startTransaction();
> > order = orderDao.getOrder(orderId);
> > for (int i = 0; i < order.getLineItems().size(); i++) {
> > LineItem lineItem = (LineItem) order.getLineItems().get(i);
> > lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
> > }
> > daoManager.commitTransaction();
> > error = false;
> > } catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
> > System.out.println("Connection Wait Timeout Exception during get
> > connection or process SQL: " + c.getMessage());
> > retryCount = 0;
> > } catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
> > System.out.println("Stale Connection Exception during get connection
> > or process SQL: " + sc.getMessage());
> > if (--retryCount == 0) {
> > System.out.println("Five stale connection exceptions, displaying
> > error page.");
> > }
> > } finally {
> > daoManager.endTransaction();
> > }
> > } while ( error==true && retryCount > 0);
> > return order;
> > }
> >
> > I am new to websphere and haven't seen a topic like this covered on the
> > list yet.
> >
> > thanks
> >
> > Cory Bestgen
> > Computer Information Technologist II
> > Information Technology Division
> > Office of State Courts Administrator
> > (573) 522 - 5455
> > cory.bestgen@courts.mo.gov
> >
> >
>
Re: StaleConnectionException
Posted by Clinton Begin <cl...@gmail.com>.
This is not a criticism of you, but of WebSphere and that "example"....
That is the single most ridiculous thing I have ever seend. That code
and approach is absolutely stupid. The entire point behind the
DataSource interface is to hide code like that and deal with
infrastructure issues in a single location, so you don't have to
everywhere.
That DataSource is flawed. I suggest you call IBM and tell them this
exactly. Tell them to fix their crappy datasource and their crappy
app server.
If they refuse (which they probably will if you've already paid them
their annual fees), then you could always wrap the datasource with
your own implementation that will deal with this.
....phew. Glad to get that off my chest. ;-)
Cheers,
Clinton
On Mon, 14 Feb 2005 14:16:16 -0600, Cory.Bestgen@courts.mo.gov
<Co...@courts.mo.gov> wrote:
>
>
> Hello,
> I am using websphere 5.1.2, oracle 9i and ibatis 2.0.8. I was using the
> following
> example from the jpetstore demo as a template for setting up my ibatis
> transactions
> but crazy things happen when the database has gone down and come back up.
>
> public Order getOrder(int orderId) {
> Order order = null;
> try {
> daoManager.startTransaction();
> order = orderDao.getOrder(orderId);
> for (int i = 0; i < order.getLineItems().size(); i++) {
> LineItem lineItem = (LineItem) order.getLineItems().get(i);
> lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
> }
> daoManager.commitTransaction();
> } finally {
> daoManager.endTransaction();
> }
> return order;
> }
>
> Websphere likes to throw a StaleConnectionException when a connection in
> the pool has gone
> bad. At the websphere site ibm shows how to use a construct something
> similar
> to the code below that retries if the StaleConnectionException is thrown.
>
> // Set retryCount to the number of times you would like to retry after a
> StaleConnectionException
> int retryCount = 5;
> // If the Database code processes successfully, we will set error = false
> boolean error = true;
> do {
> try {
> conn = ds.getConnection();
> stmt = conn.createStatement();
> String query = "Select FirstNme, MidInit, LastName from Employee ORDER
> BY LastName";
> rs = stmt.executeQuery(query);
> while (rs.next()) { employeeList.addElement(rs.getString(3) + ", " +
> rs.getString(1) + " " + rs.getString(2)); }
> //Set error to false to indicate successful completion of the database
> work
> error=false;
> } catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
> // This exception is thrown if a connection can not be obtained from
> the
> // pool within a configurable amount of time. Frequent occurrences of
> // this exception indicate an incorrectly tuned connection pool
> System.out.println("Connection Wait Timeout Exception during get
> connection or process SQL: " + c.getMessage());
> //In general, we do not want to retry after this exception, so set
> retry count to 0
> retryCount = 0;
> } catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
> // This exception indicates that the connection to the database is no
> longer valid.
> // Retry several times to attempt to obtain a valid
> //connection, display an error message if the connection still can not
> be obtained.
> System.out.println("Stale Connection Exception during get connection or
> process SQL: " + sc.getMessage());
> if (--retryCount == 0) {
> System.out.println("Five stale connection exceptions, displaying
> error page.");
> }
> }
> } while ( error==true && retryCount > 0 );
>
> And now my question, is there a way to have ibatis automatically retry when
> it gets a StaleConnectionException?
> Or should each method implement something like below?
>
> public Order getOrder(int orderId) {
> Order order = null;
> int retryCount = 5;
> boolean error = true;
> do {
> try {
> daoManager.startTransaction();
> order = orderDao.getOrder(orderId);
> for (int i = 0; i < order.getLineItems().size(); i++) {
> LineItem lineItem = (LineItem) order.getLineItems().get(i);
> lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
> }
> daoManager.commitTransaction();
> error = false;
> } catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
> System.out.println("Connection Wait Timeout Exception during get
> connection or process SQL: " + c.getMessage());
> retryCount = 0;
> } catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
> System.out.println("Stale Connection Exception during get connection
> or process SQL: " + sc.getMessage());
> if (--retryCount == 0) {
> System.out.println("Five stale connection exceptions, displaying
> error page.");
> }
> } finally {
> daoManager.endTransaction();
> }
> } while ( error==true && retryCount > 0);
> return order;
> }
>
> I am new to websphere and haven't seen a topic like this covered on the
> list yet.
>
> thanks
>
> Cory Bestgen
> Computer Information Technologist II
> Information Technology Division
> Office of State Courts Administrator
> (573) 522 - 5455
> cory.bestgen@courts.mo.gov
>
>