Dwight Shih wrote:
> A word of caution, it will be hard to share the connection if you're
> planning on writing to the database. A commit in one thread may have
> some unplanned side-effects in other threads.
>
> But I've given this some thought, and it seems to me that if you're
> only reading from the DB and you're religous about closing RecordSets
> and Statements immediately after extracting the data then you ought to
> be able to share a Connection. Whether the Database and JDBC drivers
> will actually let you share a long-lived connection is another question
> entirely. Anyone have any experience with this?
>
You are pretty much at the mercy of your JDBC driver implementation on whether this
works or not, even for read-only use. For read-write use on a transaction-supporting
database (Oracle/Sybase/Informix/MSSQL/Postgres/etc.), you will have all sorts of
problems.
The usual approach is to use a connection pool, instead of a single connection. When
you have a servlet or JSP page that needs to use a connection, it allocates one from
the pool, uses it, and then releases it. There is no competition for the connection
while you are using it, so you are guaranteed to have transactional integrity. And it
minimizes database resource usage in between times, because you don't have a
transaction (even a read-only one) that lasts "forever".
>
> Dwight Shih
>
Craig McClanahan
>
> > -----Original Message-----
> > From: On Behalf Of SERVE'EM - Dan Coggeshall
> > Sent: Tuesday, June 13, 2000 3:54 PM
> > Subject: Re: Database connection problems
> >
> >
> > Create a Database connection, in the constructor of the servelt. Since it is a
> > servlet, when the servlet is first initialized, the constructor is called and
> > the DB connection is created. Then do not close the connection at the end of
> > the servlet execution. The servelt stays in memory which is one of its main
> > reasons for its speed. There is no over head for loading into memory every time
> > there is a request. After the initial request and the servlet is loaded, and
> > the DBconnection created. The connection will remain open as long as the
> > servlet is in memory.
> >
> > A word of caution. During testing, be sure to close the conneciton. If the
> > servlet is repeatedly reloaded into memory, and creating a new connection, the
> > Oracle server will become laden with previous/useless connections. However,
> > once in production, this will increase your performance, because of the overhead
> > in creating a new DB connection each time.
> >
> > DanC
> >
> > Jarec Basham wrote:
> >
> > > Hi all,
> > >
> > > I've just inherited a servlet based system that makes a great use of an
> > > Oracle database. To be more precise every servlet inherits a base servlet
> > > which opens a database connection at the start of the servlets run and
> > > closes it when the run finished. This looks to me like a real system killer
> > > as load increases and I am currently looking at solutions to implement
> > > connection pooling.
> > >
> > > What I need to try to find out in the short term is how many connections
> > > Oracle can handle before it chokes on the load and what sort of magnitude of
> > > performance degradation we are likely to see as we approach this figure. If
> > > anyone has any experience of this kind of issue any input would be greatly
> > > appreciated.
> > >
> > > Thanks in advance
> > >
> > > Jarec
> > >
> > > ___________________________________________________________________________
> > > To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
> > > of the message "signoff SERVLET-INTEREST".
> > >
> > > Archives: http://archives.java.sun.com/archives/servlet-interest.html
> > > Resources: http://java.sun.com/products/servlet/external-resources.html
> > > LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
> >
> > ___________________________________________________________________________
> > To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
> > of the message "signoff SERVLET-INTEREST".
> >
> > Archives: http://archives.java.sun.com/archives/servlet-interest.html
> > Resources: http://java.sun.com/products/servlet/external-resources.html
> > LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
> >
>
> ___________________________________________________________________________
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
> of the message "signoff SERVLET-INTEREST".
>
> Archives: http://archives.java.sun.com/archives/servlet-interest.html
> Resources: http://java.sun.com/products/servlet/external-resources.html
> LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
