RE: problems with Connections
This is how it is being done now... and it works, but it's veery slow...
in some reports that uses a lot of classes, and of course a bunch of queries it takes
almost 3minutes to load the report page...
there's gotta be a better solution
.:| Christian J. Dechery
.:| FINEP - Depto. de Sistemas
.:| [EMAIL PROTECTED]
.:| (21) 2555-0332
[EMAIL PROTECTED] 21/08/02 17:02
Every method will have to get it's own connection and close it too.
-Original Message-
From: Christian J. Dechery [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 21, 2002 2:56 PM
To: [EMAIL PROTECTED]
Subject: Re: problems with Connections
That's exactly the problem... we dont't have any servlets (I
wish we had, but I didn't design, I only code)... only JSPs
and classes...
I'll give u an example of how are things here... let's say we
have a class called BusinessObject, and a class called
DAOBusinessObject...
so I'd have a JSP like that
%
BusinessObject bo = new BusinessObject(); // at this
point, DAOBusinessObject requested a connection
bo.method1(); // this method calls a method in DAO which
runs a query
bo.method2(); // this on too... and every other method as well...
%
so, as long as the DAOBusinessObject object lives the
Connection is there... how am I supposed to close it, since
every query needs it? Now I see, I should have a method to
close the used conn or something... but we have up to 50 DAO*
classes and more then 200 JSPs...
I agree with what u said about the finalize()... but what
should I do then?
.:| Christian J. Dechery
.:| FINEP - Depto. de Sistemas
.:| [EMAIL PROTECTED]
.:| (21) 2555-0332
[EMAIL PROTECTED] 21/08/02 16:37
On Wed, 21 Aug 2002, Christian J. Dechery wrote:
Date: Wed, 21 Aug 2002 16:13:23 -0300
From: Christian J. Dechery [EMAIL PROTECTED]
Reply-To: Tomcat Users List [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: problems with Connections
I don't think it is TOTALLY offtopic, since the problem
occurs within
Tomcat...
From your description of the problem and your design
approach, I'll bet it
would happen to you in a long-running non-servlet application as well.
and when I close tomcat all the connections and cursors are
released...
Exactly what you'd expect if your application is leaking open
connections,
statements, or result sets.
as I said in my email I close ALL ResultSets and Statements
in finally
blocks...
Fine, I'll take your word for it ... but missing a case would easily
account for what you are seeing. (In the particular case of Oracle, a
cursor is allocated for each result set, which is not
released until the
result set is closed).
as for closing the Connection... can I use the finalize()
in the DAO*
classes to use the method that returns the Connection to
the pool?? Cuz
I can't see anywhere else where I'd be able to do that...
The right design pattern is to acquire a connection, do whatever
processing is required, and immediately release it. For example:
DataSource dataSource = ...; // Acquire reference to
connection pool
Connection conn = null;
try {
conn = dataSource.getConnection();
... perform SQL operations as necessary ...
conn.close(); // Return connection to the pool
conn = null;
} catch (SQLException e) {
... deal with problems ...
} finally {
if (conn != null) {
try {
conn.close(); // Return to pool even if an exception occurred
} catch (SQLException e) {
;
}
conn = null;
}
}
Waiting for the finalize() method to clean up just occupies resources
needlessly until the garbage collector gets around to running -- this
by itself could easily exhaust your available connections in a busy
environment. It also assumes that your JDBC driver's
implementation of
the finalize() method knows that this Connection was stored in a pool.
That seems like a really shaky bet.
A primary goal of your designs should be to minimize the
amount of time
that you have a database connection allocated to the processing of a
particular request -- connections are expensive to create, and there's
always an upper limit on how many your database will support.
.:| Christian J. Dechery
.:| FINEP - Depto. de Sistemas
.:| [EMAIL PROTECTED]
.:| (21) 2555-0332
Craig
--
To unsubscribe, e-mail:
mailto:[EMAIL PROTECTED]
For additional commands, e-mail: mailto:[EMAIL PROTECTED]
--
To unsubscribe, e-mail: mailto:[EMAIL PROTECTED]
For additional commands, e-mail: mailto:[EMAIL PROTECTED]
Re: problems with Connections
I wish it was that simple to just DO-IT-ALL-OVER-AGAIN but we're already in production, I don't have the time to go trough all the classes again an rewrite code... I have a lot of other things to do along with providing maintenance to all these 100 classes... Isn't there a way a class can have a connection and properly close it after it has made use of it? Cuz that's the main problem here... the connections don't close... only if I call con.close() in the same method that opens it... but that makes it too slow... And what about that maximum cursors open thing?? I call rs.close() and stmt.close() in the finally block of EVERY query... how come I get maxium cursors??? .:| Christian J. Dechery .:| FINEP - Depto. de Sistemas .:| [EMAIL PROTECTED] .:| (21) 2555-0332 [EMAIL PROTECTED] 22/08/02 00:58 On Wed, 21 Aug 2002, Christian J. Dechery wrote: Date: Wed, 21 Aug 2002 16:56:05 -0300 From: Christian J. Dechery [EMAIL PROTECTED] Reply-To: Tomcat Users List [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: problems with Connections That's exactly the problem... we dont't have any servlets (I wish we had, but I didn't design, I only code)... only JSPs and classes... I'll give u an example of how are things here... let's say we have a class called BusinessObject, and a class called DAOBusinessObject... so I'd have a JSP like that % BusinessObject bo = new BusinessObject(); // at this point, DAOBusinessObject requested a connection bo.method1(); // this method calls a method in DAO which runs a query bo.method2(); // this on too... and every other method as well... % so, as long as the DAOBusinessObject object lives the Connection is there... how am I supposed to close it, since every query needs it? Now I see, I should have a method to close the used conn or something... but we have up to 50 DAO* classes and more then 200 JSPs... I agree with what u said about the finalize()... but what should I do then? Give up and start over. Seriously. This application design is totally hopeless from a scalability or reliability viewpoint, because it requires a connection for every business object, on every page that uses that BO class. To say nothing of the fact that JDBC drivers are not required to be thread safe, so you're undoubtedly causing yourself tremendous grief when the same page is accessed by more than one user. If you follow my advice, please follow one other piece -- get a book on design patterns (Core J2EE Design Patterns by Alur/Crupi/Malks is one of my most well-thumbed resources) and follow the prescribed patterns for doing this right. .:| Christian J. Dechery .:| FINEP - Depto. de Sistemas .:| [EMAIL PROTECTED] .:| (21) 2555-0332 Craig -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
RE: problems with Connections
Do your objects persist across requests from the user? -Original Message- From: Christian J. Dechery [mailto:[EMAIL PROTECTED]] Sent: 22 August, 2002 5:58 AM To: [EMAIL PROTECTED] Subject: Re: problems with Connections I wish it was that simple to just DO-IT-ALL-OVER-AGAIN but we're already in production, I don't have the time to go trough all the classes again an rewrite code... I have a lot of other things to do along with providing maintenance to all these 100 classes... Isn't there a way a class can have a connection and properly close it after it has made use of it? Cuz that's the main problem here... the connections don't close... only if I call con.close() in the same method that opens it... but that makes it too slow... And what about that maximum cursors open thing?? I call rs.close() and stmt.close() in the finally block of EVERY query... how come I get maxium cursors??? .:| Christian J. Dechery .:| FINEP - Depto. de Sistemas .:| [EMAIL PROTECTED] .:| (21) 2555-0332 [EMAIL PROTECTED] 22/08/02 00:58 On Wed, 21 Aug 2002, Christian J. Dechery wrote: Date: Wed, 21 Aug 2002 16:56:05 -0300 From: Christian J. Dechery [EMAIL PROTECTED] Reply-To: Tomcat Users List [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: problems with Connections That's exactly the problem... we dont't have any servlets (I wish we had, but I didn't design, I only code)... only JSPs and classes... I'll give u an example of how are things here... let's say we have a class called BusinessObject, and a class called DAOBusinessObject... so I'd have a JSP like that % BusinessObject bo = new BusinessObject(); // at this point, DAOBusinessObject requested a connection bo.method1(); // this method calls a method in DAO which runs a query bo.method2(); // this on too... and every other method as well... % so, as long as the DAOBusinessObject object lives the Connection is there... how am I supposed to close it, since every query needs it? Now I see, I should have a method to close the used conn or something... but we have up to 50 DAO* classes and more then 200 JSPs... I agree with what u said about the finalize()... but what should I do then? Give up and start over. Seriously. This application design is totally hopeless from a scalability or reliability viewpoint, because it requires a connection for every business object, on every page that uses that BO class. To say nothing of the fact that JDBC drivers are not required to be thread safe, so you're undoubtedly causing yourself tremendous grief when the same page is accessed by more than one user. If you follow my advice, please follow one other piece -- get a book on design patterns (Core J2EE Design Patterns by Alur/Crupi/Malks is one of my most well-thumbed resources) and follow the prescribed patterns for doing this right. .:| Christian J. Dechery .:| FINEP - Depto. de Sistemas .:| [EMAIL PROTECTED] .:| (21) 2555-0332 Craig -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED] -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
RE: problems with Connections
You need a connection pooler. The Oracle Drivers come with support for connection pooling. -Original Message- From: Christian J. Dechery [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 21, 2002 1:01 PM To: [EMAIL PROTECTED] Subject: problems with Connections I have this big problem handling Oracle Connecions... something goes wrong... I have tons of classes called DAOSomething... and a class called DAO which provides the Connection... the DAO*s requests the Connection from DAO and uses it, but there is no method to close the connection since it is used by several methods that run queries (selects and updates)... I don't know what goes wrong... but after some time of using the applicatiob the maximum number of cursors exceeds and the whole application stops, cuz no more queries will run... and I am closing ALL ResultSets and Statements on the finally block of EVERY query... this happend when I dispense one Connection to several classes, thus achieving some kind of sharing... if I turn that sharing off - every class will get an exclusive Connection, the problem changes... now instead of the maximum number of cursors exceeding, I get maximum number of processes (connections)... the connections (nor the cursors for that matter) are getting closed... I even tried placing a con.close() on the finalize() method of the DAO*s... but that didn't work... I get an IOException: socket closed... At the Oracle support service, I saw that a lot of people has the exact same problem.. does anyone knows how to solve this??? thanks btw: I have a solution working now... I changed the code of my DAO*s, so that every method that runs a query, requests a connection, than closes it... but that makes some parts of the application very slow... .:| Christian J. Dechery .:| FINEP - Depto. de Sistemas .:| [EMAIL PROTECTED] .:| (21) 2555-0332 -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
Re: problems with Connections
This is totally off topic for TOMCAT-USER, but ... The basic rules for successful use of a connection pool: * Always close the connection before the current request completes (which doesn't really close the underlying Connection; it just returns it to the pool. * Always close your ResultSet and Statement instances when you are through with them -- a finally clause is good for this. It sounds like you have missed some error cases. * Never try to share an individual Connection across more than one request -- Connection instances are *not* shareable. That's why you're using a connection pool in the first place. I suspect that your code is violating one or more of these principles -- probably on some rarely executed code path (because it takes some time for the problem to surface). Craig On Wed, 21 Aug 2002, Christian J. Dechery wrote: Date: Wed, 21 Aug 2002 15:01:04 -0300 From: Christian J. Dechery [EMAIL PROTECTED] Reply-To: Tomcat Users List [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: problems with Connections I have this big problem handling Oracle Connecions... something goes wrong... I have tons of classes called DAOSomething... and a class called DAO which provides the Connection... the DAO*s requests the Connection from DAO and uses it, but there is no method to close the connection since it is used by several methods that run queries (selects and updates)... I don't know what goes wrong... but after some time of using the applicatiob the maximum number of cursors exceeds and the whole application stops, cuz no more queries will run... and I am closing ALL ResultSets and Statements on the finally block of EVERY query... this happend when I dispense one Connection to several classes, thus achieving some kind of sharing... if I turn that sharing off - every class will get an exclusive Connection, the problem changes... now instead of the maximum number of cursors exceeding, I get maximum number of processes (connections)... the connections (nor the cursors for that matter) are getting closed... I even tried placing a con.close() on the finalize() method of the DAO*s... but that didn't work... I get an IOException: socket closed... At the Oracle support service, I saw that a lot of people has the exact same problem.. does anyone knows how to solve this??? thanks btw: I have a solution working now... I changed the code of my DAO*s, so that every method that runs a query, requests a connection, than closes it... but that makes some parts of the application very slow... .:| Christian J. Dechery .:| FINEP - Depto. de Sistemas .:| [EMAIL PROTECTED] .:| (21) 2555-0332 -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
RE: problems with Connections
already tried that... didn't work... as a matter of fact, the pool had no effect on the problem, it stayed the same... .:| Christian J. Dechery .:| FINEP - Depto. de Sistemas .:| [EMAIL PROTECTED] .:| (21) 2555-0332 [EMAIL PROTECTED] 21/08/02 15:12 You need a connection pooler. The Oracle Drivers come with support for connection pooling. -Original Message- From: Christian J. Dechery [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 21, 2002 1:01 PM To: [EMAIL PROTECTED] Subject: problems with Connections I have this big problem handling Oracle Connecions... something goes wrong... I have tons of classes called DAOSomething... and a class called DAO which provides the Connection... the DAO*s requests the Connection from DAO and uses it, but there is no method to close the connection since it is used by several methods that run queries (selects and updates)... I don't know what goes wrong... but after some time of using the applicatiob the maximum number of cursors exceeds and the whole application stops, cuz no more queries will run... and I am closing ALL ResultSets and Statements on the finally block of EVERY query... this happend when I dispense one Connection to several classes, thus achieving some kind of sharing... if I turn that sharing off - every class will get an exclusive Connection, the problem changes... now instead of the maximum number of cursors exceeding, I get maximum number of processes (connections)... the connections (nor the cursors for that matter) are getting closed... I even tried placing a con.close() on the finalize() method of the DAO*s... but that didn't work... I get an IOException: socket closed... At the Oracle support service, I saw that a lot of people has the exact same problem.. does anyone knows how to solve this??? thanks btw: I have a solution working now... I changed the code of my DAO*s, so that every method that runs a query, requests a connection, than closes it... but that makes some parts of the application very slow... .:| Christian J. Dechery .:| FINEP - Depto. de Sistemas .:| [EMAIL PROTECTED] .:| (21) 2555-0332 -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
Re: problems with Connections
On Wed, Aug 21, 2002 at 03:28:09PM -0300, Christian J. Dechery wrote: already tried that... didn't work... as a matter of fact, the pool had no effect on the problem, it stayed the same... re: using connection pooling did you get the connection from the pool or did you use the driver manager to create one yourself? -- Peter Abplanalp Email: [EMAIL PROTECTED] PGP: pgp.mit.edu msg63380/pgp0.pgp Description: PGP signature
Re: problems with Connections
from the pool, of course... .:| Christian J. Dechery .:| FINEP - Depto. de Sistemas .:| [EMAIL PROTECTED] .:| (21) 2555-0332 [EMAIL PROTECTED] 21/08/02 15:36 On Wed, Aug 21, 2002 at 03:28:09PM -0300, Christian J. Dechery wrote: already tried that... didn't work... as a matter of fact, the pool had no effect on the problem, it stayed the same... re: using connection pooling did you get the connection from the pool or did you use the driver manager to create one yourself? -- Peter Abplanalp Email: [EMAIL PROTECTED] PGP: pgp.mit.edu
Re: problems with Connections
I don't think it is TOTALLY offtopic, since the problem occurs within Tomcat... and when I close tomcat all the connections and cursors are released... as I said in my email I close ALL ResultSets and Statements in finally blocks... as for closing the Connection... can I use the finalize() in the DAO* classes to use the method that returns the Connection to the pool?? Cuz I can't see anywhere else where I'd be able to do that... .:| Christian J. Dechery .:| FINEP - Depto. de Sistemas .:| [EMAIL PROTECTED] .:| (21) 2555-0332 [EMAIL PROTECTED] 21/08/02 15:32 This is totally off topic for TOMCAT-USER, but ... The basic rules for successful use of a connection pool: * Always close the connection before the current request completes (which doesn't really close the underlying Connection; it just returns it to the pool. * Always close your ResultSet and Statement instances when you are through with them -- a finally clause is good for this. It sounds like you have missed some error cases. * Never try to share an individual Connection across more than one request -- Connection instances are *not* shareable. That's why you're using a connection pool in the first place. I suspect that your code is violating one or more of these principles -- probably on some rarely executed code path (because it takes some time for the problem to surface). Craig On Wed, 21 Aug 2002, Christian J. Dechery wrote: Date: Wed, 21 Aug 2002 15:01:04 -0300 From: Christian J. Dechery [EMAIL PROTECTED] Reply-To: Tomcat Users List [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: problems with Connections I have this big problem handling Oracle Connecions... something goes wrong... I have tons of classes called DAOSomething... and a class called DAO which provides the Connection... the DAO*s requests the Connection from DAO and uses it, but there is no method to close the connection since it is used by several methods that run queries (selects and updates)... I don't know what goes wrong... but after some time of using the applicatiob the maximum number of cursors exceeds and the whole application stops, cuz no more queries will run... and I am closing ALL ResultSets and Statements on the finally block of EVERY query... this happend when I dispense one Connection to several classes, thus achieving some kind of sharing... if I turn that sharing off - every class will get an exclusive Connection, the problem changes... now instead of the maximum number of cursors exceeding, I get maximum number of processes (connections)... the connections (nor the cursors for that matter) are getting closed... I even tried placing a con.close() on the finalize() method of the DAO*s... but that didn't work... I get an IOException: socket closed... At the Oracle support service, I saw that a lot of people has the exact same problem.. does anyone knows how to solve this??? thanks btw: I have a solution working now... I changed the code of my DAO*s, so that every method that runs a query, requests a connection, than closes it... but that makes some parts of the application very slow... .:| Christian J. Dechery .:| FINEP - Depto. de Sistemas .:| [EMAIL PROTECTED] .:| (21) 2555-0332 -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
Re: problems with Connections
On Wed, 21 Aug 2002, Christian J. Dechery wrote:
Date: Wed, 21 Aug 2002 16:13:23 -0300
From: Christian J. Dechery [EMAIL PROTECTED]
Reply-To: Tomcat Users List [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: problems with Connections
I don't think it is TOTALLY offtopic, since the problem occurs within
Tomcat...
From your description of the problem and your design approach, I'll bet it
would happen to you in a long-running non-servlet application as well.
and when I close tomcat all the connections and cursors are
released...
Exactly what you'd expect if your application is leaking open connections,
statements, or result sets.
as I said in my email I close ALL ResultSets and Statements in finally
blocks...
Fine, I'll take your word for it ... but missing a case would easily
account for what you are seeing. (In the particular case of Oracle, a
cursor is allocated for each result set, which is not released until the
result set is closed).
as for closing the Connection... can I use the finalize() in the DAO*
classes to use the method that returns the Connection to the pool?? Cuz
I can't see anywhere else where I'd be able to do that...
The right design pattern is to acquire a connection, do whatever
processing is required, and immediately release it. For example:
DataSource dataSource = ...; // Acquire reference to connection pool
Connection conn = null;
try {
conn = dataSource.getConnection();
... perform SQL operations as necessary ...
conn.close(); // Return connection to the pool
conn = null;
} catch (SQLException e) {
... deal with problems ...
} finally {
if (conn != null) {
try {
conn.close(); // Return to pool even if an exception occurred
} catch (SQLException e) {
;
}
conn = null;
}
}
Waiting for the finalize() method to clean up just occupies resources
needlessly until the garbage collector gets around to running -- this
by itself could easily exhaust your available connections in a busy
environment. It also assumes that your JDBC driver's implementation of
the finalize() method knows that this Connection was stored in a pool.
That seems like a really shaky bet.
A primary goal of your designs should be to minimize the amount of time
that you have a database connection allocated to the processing of a
particular request -- connections are expensive to create, and there's
always an upper limit on how many your database will support.
.:| Christian J. Dechery
.:| FINEP - Depto. de Sistemas
.:| [EMAIL PROTECTED]
.:| (21) 2555-0332
Craig
--
To unsubscribe, e-mail: mailto:[EMAIL PROTECTED]
For additional commands, e-mail: mailto:[EMAIL PROTECTED]
Re: problems with Connections
That's exactly the problem... we dont't have any servlets (I wish we had, but I didn't
design, I only code)... only JSPs and classes...
I'll give u an example of how are things here... let's say we have a class called
BusinessObject, and a class called DAOBusinessObject...
so I'd have a JSP like that
%
BusinessObject bo = new BusinessObject(); // at this point, DAOBusinessObject
requested a connection
bo.method1(); // this method calls a method in DAO which runs a query
bo.method2(); // this on too... and every other method as well...
%
so, as long as the DAOBusinessObject object lives the Connection is there... how am
I supposed to close it, since every query needs it? Now I see, I should have a method
to close the used conn or something... but we have up to 50 DAO* classes and more then
200 JSPs...
I agree with what u said about the finalize()... but what should I do then?
.:| Christian J. Dechery
.:| FINEP - Depto. de Sistemas
.:| [EMAIL PROTECTED]
.:| (21) 2555-0332
[EMAIL PROTECTED] 21/08/02 16:37
On Wed, 21 Aug 2002, Christian J. Dechery wrote:
Date: Wed, 21 Aug 2002 16:13:23 -0300
From: Christian J. Dechery [EMAIL PROTECTED]
Reply-To: Tomcat Users List [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: problems with Connections
I don't think it is TOTALLY offtopic, since the problem occurs within
Tomcat...
From your description of the problem and your design approach, I'll bet it
would happen to you in a long-running non-servlet application as well.
and when I close tomcat all the connections and cursors are
released...
Exactly what you'd expect if your application is leaking open connections,
statements, or result sets.
as I said in my email I close ALL ResultSets and Statements in finally
blocks...
Fine, I'll take your word for it ... but missing a case would easily
account for what you are seeing. (In the particular case of Oracle, a
cursor is allocated for each result set, which is not released until the
result set is closed).
as for closing the Connection... can I use the finalize() in the DAO*
classes to use the method that returns the Connection to the pool?? Cuz
I can't see anywhere else where I'd be able to do that...
The right design pattern is to acquire a connection, do whatever
processing is required, and immediately release it. For example:
DataSource dataSource = ...; // Acquire reference to connection pool
Connection conn = null;
try {
conn = dataSource.getConnection();
... perform SQL operations as necessary ...
conn.close(); // Return connection to the pool
conn = null;
} catch (SQLException e) {
... deal with problems ...
} finally {
if (conn != null) {
try {
conn.close(); // Return to pool even if an exception occurred
} catch (SQLException e) {
;
}
conn = null;
}
}
Waiting for the finalize() method to clean up just occupies resources
needlessly until the garbage collector gets around to running -- this
by itself could easily exhaust your available connections in a busy
environment. It also assumes that your JDBC driver's implementation of
the finalize() method knows that this Connection was stored in a pool.
That seems like a really shaky bet.
A primary goal of your designs should be to minimize the amount of time
that you have a database connection allocated to the processing of a
particular request -- connections are expensive to create, and there's
always an upper limit on how many your database will support.
.:| Christian J. Dechery
.:| FINEP - Depto. de Sistemas
.:| [EMAIL PROTECTED]
.:| (21) 2555-0332
Craig
--
To unsubscribe, e-mail: mailto:[EMAIL PROTECTED]
For additional commands, e-mail: mailto:[EMAIL PROTECTED]
RE: problems with Connections
Every method will have to get it's own connection and close it too.
-Original Message-
From: Christian J. Dechery [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 21, 2002 2:56 PM
To: [EMAIL PROTECTED]
Subject: Re: problems with Connections
That's exactly the problem... we dont't have any servlets (I
wish we had, but I didn't design, I only code)... only JSPs
and classes...
I'll give u an example of how are things here... let's say we
have a class called BusinessObject, and a class called
DAOBusinessObject...
so I'd have a JSP like that
%
BusinessObject bo = new BusinessObject(); // at this
point, DAOBusinessObject requested a connection
bo.method1(); // this method calls a method in DAO which
runs a query
bo.method2(); // this on too... and every other method as well...
%
so, as long as the DAOBusinessObject object lives the
Connection is there... how am I supposed to close it, since
every query needs it? Now I see, I should have a method to
close the used conn or something... but we have up to 50 DAO*
classes and more then 200 JSPs...
I agree with what u said about the finalize()... but what
should I do then?
.:| Christian J. Dechery
.:| FINEP - Depto. de Sistemas
.:| [EMAIL PROTECTED]
.:| (21) 2555-0332
[EMAIL PROTECTED] 21/08/02 16:37
On Wed, 21 Aug 2002, Christian J. Dechery wrote:
Date: Wed, 21 Aug 2002 16:13:23 -0300
From: Christian J. Dechery [EMAIL PROTECTED]
Reply-To: Tomcat Users List [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: problems with Connections
I don't think it is TOTALLY offtopic, since the problem
occurs within
Tomcat...
From your description of the problem and your design
approach, I'll bet it
would happen to you in a long-running non-servlet application as well.
and when I close tomcat all the connections and cursors are
released...
Exactly what you'd expect if your application is leaking open
connections,
statements, or result sets.
as I said in my email I close ALL ResultSets and Statements
in finally
blocks...
Fine, I'll take your word for it ... but missing a case would easily
account for what you are seeing. (In the particular case of Oracle, a
cursor is allocated for each result set, which is not
released until the
result set is closed).
as for closing the Connection... can I use the finalize()
in the DAO*
classes to use the method that returns the Connection to
the pool?? Cuz
I can't see anywhere else where I'd be able to do that...
The right design pattern is to acquire a connection, do whatever
processing is required, and immediately release it. For example:
DataSource dataSource = ...; // Acquire reference to
connection pool
Connection conn = null;
try {
conn = dataSource.getConnection();
... perform SQL operations as necessary ...
conn.close(); // Return connection to the pool
conn = null;
} catch (SQLException e) {
... deal with problems ...
} finally {
if (conn != null) {
try {
conn.close(); // Return to pool even if an exception occurred
} catch (SQLException e) {
;
}
conn = null;
}
}
Waiting for the finalize() method to clean up just occupies resources
needlessly until the garbage collector gets around to running -- this
by itself could easily exhaust your available connections in a busy
environment. It also assumes that your JDBC driver's
implementation of
the finalize() method knows that this Connection was stored in a pool.
That seems like a really shaky bet.
A primary goal of your designs should be to minimize the
amount of time
that you have a database connection allocated to the processing of a
particular request -- connections are expensive to create, and there's
always an upper limit on how many your database will support.
.:| Christian J. Dechery
.:| FINEP - Depto. de Sistemas
.:| [EMAIL PROTECTED]
.:| (21) 2555-0332
Craig
--
To unsubscribe, e-mail:
mailto:[EMAIL PROTECTED]
For additional commands, e-mail: mailto:[EMAIL PROTECTED]
--
To unsubscribe, e-mail: mailto:[EMAIL PROTECTED]
For additional commands, e-mail: mailto:[EMAIL PROTECTED]
Re: problems with Connections
Perhaps this is what you had in mind at the end of your last message, but
if all of your DAO objects inherit from a common base class (as they
should), why not add your connection opening logic to that class, add a
closeConnection() method as well, and then code like this in each of your
jsps:
%
BusinessObject bo = new BusinessObject(); // at this point,
DAOBusinessObject requested a connection
bo.method1(); // this method calls a method in DAO which runs a query
bo.method2(); // this on too... and every other method as well...
bo.closeConnection();
%
You'd still have to modify all your jsps, but at least you don't have to
modify the DAO's... don't see any way around it if you want to do it properly.
justin
At 12:56 PM 8/21/2002, you wrote:
That's exactly the problem... we dont't have any servlets (I wish we had,
but I didn't design, I only code)... only JSPs and classes...
I'll give u an example of how are things here... let's say we have a class
called BusinessObject, and a class called DAOBusinessObject...
so I'd have a JSP like that
%
BusinessObject bo = new BusinessObject(); // at this point,
DAOBusinessObject requested a connection
bo.method1(); // this method calls a method in DAO which runs a query
bo.method2(); // this on too... and every other method as well...
%
so, as long as the DAOBusinessObject object lives the Connection is
there... how am I supposed to close it, since every query needs it? Now I
see, I should have a method to close the used conn or something... but we
have up to 50 DAO* classes and more then 200 JSPs...
I agree with what u said about the finalize()... but what should I do then?
.:| Christian J. Dechery
.:| FINEP - Depto. de Sistemas
.:| [EMAIL PROTECTED]
.:| (21) 2555-0332
[EMAIL PROTECTED] 21/08/02 16:37
On Wed, 21 Aug 2002, Christian J. Dechery wrote:
Date: Wed, 21 Aug 2002 16:13:23 -0300
From: Christian J. Dechery [EMAIL PROTECTED]
Reply-To: Tomcat Users List [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: problems with Connections
I don't think it is TOTALLY offtopic, since the problem occurs within
Tomcat...
From your description of the problem and your design approach, I'll bet it
would happen to you in a long-running non-servlet application as well.
and when I close tomcat all the connections and cursors are
released...
Exactly what you'd expect if your application is leaking open connections,
statements, or result sets.
as I said in my email I close ALL ResultSets and Statements in finally
blocks...
Fine, I'll take your word for it ... but missing a case would easily
account for what you are seeing. (In the particular case of Oracle, a
cursor is allocated for each result set, which is not released until the
result set is closed).
as for closing the Connection... can I use the finalize() in the DAO*
classes to use the method that returns the Connection to the pool?? Cuz
I can't see anywhere else where I'd be able to do that...
The right design pattern is to acquire a connection, do whatever
processing is required, and immediately release it. For example:
DataSource dataSource = ...; // Acquire reference to connection pool
Connection conn = null;
try {
conn = dataSource.getConnection();
... perform SQL operations as necessary ...
conn.close(); // Return connection to the pool
conn = null;
} catch (SQLException e) {
... deal with problems ...
} finally {
if (conn != null) {
try {
conn.close(); // Return to pool even if an exception occurred
} catch (SQLException e) {
;
}
conn = null;
}
}
Waiting for the finalize() method to clean up just occupies resources
needlessly until the garbage collector gets around to running -- this
by itself could easily exhaust your available connections in a busy
environment. It also assumes that your JDBC driver's implementation of
the finalize() method knows that this Connection was stored in a pool.
That seems like a really shaky bet.
A primary goal of your designs should be to minimize the amount of time
that you have a database connection allocated to the processing of a
particular request -- connections are expensive to create, and there's
always an upper limit on how many your database will support.
.:| Christian J. Dechery
.:| FINEP - Depto. de Sistemas
.:| [EMAIL PROTECTED]
.:| (21) 2555-0332
Craig
--
To unsubscribe, e-mail: mailto:[EMAIL PROTECTED]
For additional commands, e-mail: mailto:[EMAIL PROTECTED]
--
To unsubscribe, e-mail: mailto:[EMAIL PROTECTED]
For additional commands, e-mail: mailto:[EMAIL PROTECTED]
Re: problems with Connections
On Wed, 21 Aug 2002, Christian J. Dechery wrote: Date: Wed, 21 Aug 2002 16:56:05 -0300 From: Christian J. Dechery [EMAIL PROTECTED] Reply-To: Tomcat Users List [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: problems with Connections That's exactly the problem... we dont't have any servlets (I wish we had, but I didn't design, I only code)... only JSPs and classes... I'll give u an example of how are things here... let's say we have a class called BusinessObject, and a class called DAOBusinessObject... so I'd have a JSP like that % BusinessObject bo = new BusinessObject(); // at this point, DAOBusinessObject requested a connection bo.method1(); // this method calls a method in DAO which runs a query bo.method2(); // this on too... and every other method as well... % so, as long as the DAOBusinessObject object lives the Connection is there... how am I supposed to close it, since every query needs it? Now I see, I should have a method to close the used conn or something... but we have up to 50 DAO* classes and more then 200 JSPs... I agree with what u said about the finalize()... but what should I do then? Give up and start over. Seriously. This application design is totally hopeless from a scalability or reliability viewpoint, because it requires a connection for every business object, on every page that uses that BO class. To say nothing of the fact that JDBC drivers are not required to be thread safe, so you're undoubtedly causing yourself tremendous grief when the same page is accessed by more than one user. If you follow my advice, please follow one other piece -- get a book on design patterns (Core J2EE Design Patterns by Alur/Crupi/Malks is one of my most well-thumbed resources) and follow the prescribed patterns for doing this right. .:| Christian J. Dechery .:| FINEP - Depto. de Sistemas .:| [EMAIL PROTECTED] .:| (21) 2555-0332 Craig -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
