Mailing-List: contact tomcat-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <tomcat-user@jakarta.apache.org>
Message-Id: <sd63c63b.029@fin-rio01.finep.gov.br>
Date: Wed, 21 Aug 2002 16:56:05 -0300
From: "Christian J. Dechery" <christian@finep.gov.br>
To: <tomcat-user@jakarta.apache.org>
Subject: Re: problems with Connections
Mime-Version: 1.0
Content-Type: multipart/alternative; boundary="=_BDE1EE7B.187910FF"

--=_BDE1EE7B.187910FF
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

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...=20
=20
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...
=20
so I'd have a JSP like that
=20
<%
    BusinessObject bo =3D 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...
%>
=20
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...=20
=20
I agree with what u said about the finalize()... but what should I do =
then?
=20
=20
.:| Christian J. Dechery
.:| FINEP - Depto. de Sistemas
.:| christian@finep.gov.br
.:| (21) 2555-0332

>>> craigmcc@apache.org 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 <christian@finep.gov.br>
> Reply-To: Tomcat Users List <tomcat-user@jakarta.apache.org>
> To: tomcat-user@jakarta.apache.org=20
> 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 =3D ...;  // Acquire reference to connection pool
  Connection conn =3D null;
  try {
    conn =3D dataSource.getConnection();
    ... perform SQL operations as necessary ...
    conn.close();     // Return connection to the pool
    conn =3D null;
  } catch (SQLException e) {
    ... deal with problems ...
  } finally {
    if (conn !=3D null) {
      try {
        conn.close();  // Return to pool even if an exception occurred
      } catch (SQLException e) {
        ;
      }
      conn =3D 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
> .:| christian@finep.gov.br=20
> .:| (21) 2555-0332

Craig


--
To unsubscribe, e-mail:   <mailto:tomcat-user-unsubscribe@jakarta.apache.or=
g>
For additional commands, e-mail: <mailto:tomcat-user-help@jakarta.apache.or=
g>


--=_BDE1EE7B.187910FF--