You are viewing a plain text version of this content. The canonical link for it is here.
Posted to server-dev@james.apache.org by "Richard O. Hammer" <RO...@EarthLink.net> on 2004/04/14 19:39:56 UTC
keeping a JDBC connection active
In my mailserver (a cousin of James) I use one java.sql.Connection
throughout the life of the server. I call
java.sql.DriverManager.getConnection() once at server startup. I've
never suspected there was anything wrong with this, but I guess there
might be.
If this is of interest to anybody, several times I have gotten
"java.net.SocketException Connection reset by peer", indicating a dead
connection to the database and requiring me to restart the server.
This happens several hours after server startup, when I am running the
mailserver in a Windows 2000 desktop and the database (PostgreSQL) in
a RH7.3 Linux box on the LAN. I have found that I can work around
this by running both mailserver and database on the same Linux box;
then the Connection seems to last forever.
Rich Hammer
Noel J. Bergman wrote:
>>Do you have any reason to believe that there are legitimate
>>reasons for a JDBC connection to be active for several
>>minutes, much less an hour?
>
> I ran grep against the logs in my production environment. There have been
> 32 timeouts since August 2002. Getting them tends to indicate a need to
> repair tables, at least in our environment.
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: keeping a JDBC connection active
Posted by Joe Cheng <jm...@alum.mit.edu>.
> I generally don't critique design patterns (let he who is without bad
> design patterns cast the first stone), but permanently holding a
> single database connection open is a Bad Idea(tm). Some downsides:
> - your code is inherently single-threaded.
> - if you change to make it 1 thread = 1 db connection, you have a lot
> of unnecessarily open db connections.
> - if you restart your db server, you get a failure in your app.
> - if you have a network failure, you get a failure in your app.
I'll also add what Serge implied, that sharing a single connection among
multiple threads screws up transaction semantics and could easily lead
to data corruption.
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: keeping a JDBC connection active
Posted by "Richard O. Hammer" <RO...@EarthLink.net>.
Serge Knystautas wrote:
> ... permanently holding a single
> database connection open is a Bad Idea(tm). Some downsides:
> - your code is inherently single-threaded.
I have not noticed any single-thread limitation. I have a singleton
(only one instance) database class. It has the one Connection as an
instance variable which is set when the class is constructed at server
startup. Multiple threads call the methods in this database class.
Each call to one of the database methods prepares its own
PreparedStatement from the one Connection, and closes the
PreparedStatement before returning. (I would have avoided that
pattern of preparing a PreparedStatement on each method call, since it
seems wasteful in my limited knowledge. But if I recall correctly I
got the pattern from James, where I gather it works well enough, and I
have not had reason to change it in my code.)
Anyhow, I have the impression that the Connection keeps track of its
many individual PreparedStatements, like a mama knows her children
apart, and never mixes up their interactions. Do you think it might?
I've never noticed any jumbling of data.
I think I've got the necessary synchronization, pertaining to the
reading and setting of database data, handled higher up in my code.
But maybe there is some database programming issue here which I need
to learn.
> - if you restart your db server, you get a failure in your app.
Yes! This one has hit me over the head. You've helped me see which
direction I need to go to fix it.
> ... Database connection pools are pretty
> prevalent, even most JDBC3 drivers have something bundled. I would
> really encourage you to look into using one.
Thank you, Serge. I've got work to do.
Rich
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: keeping a JDBC connection active
Posted by Serge Knystautas <se...@lokitech.com>.
Richard O. Hammer wrote:
> In my mailserver (a cousin of James) I use one java.sql.Connection
> throughout the life of the server. I call
> java.sql.DriverManager.getConnection() once at server startup. I've
> never suspected there was anything wrong with this, but I guess there
> might be.
>
> If this is of interest to anybody, several times I have gotten
> "java.net.SocketException Connection reset by peer", indicating a dead
> connection to the database and requiring me to restart the server. This
> happens several hours after server startup, when I am running the
> mailserver in a Windows 2000 desktop and the database (PostgreSQL) in a
> RH7.3 Linux box on the LAN. I have found that I can work around this by
> running both mailserver and database on the same Linux box; then the
> Connection seems to last forever.
I generally don't critique design patterns (let he who is without bad
design patterns cast the first stone), but permanently holding a single
database connection open is a Bad Idea(tm). Some downsides:
- your code is inherently single-threaded.
- if you change to make it 1 thread = 1 db connection, you have a lot of
unnecessarily open db connections.
- if you restart your db server, you get a failure in your app.
- if you have a network failure, you get a failure in your app.
Your scenario of putting the mail server and database on the same box
addresses the fourth point. Database connection pools are pretty
prevalent, even most JDBC3 drivers have something bundled. I would
really encourage you to look into using one.
--
Serge Knystautas
President
Lokitech >> software . strategy . design >> http://www.lokitech.com
p. 301.656.5501
e. sergek@lokitech.com
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org