> I've been doing database programming a long time now and there is a
> issue that I've never seen in a book or anywhere that I'm curious
> about.
> Is it ok to share a database connection and just create statements off
> of it whenever you need one? Or is it "better" to create a new
> connection for each statement. I've always just used one and have
> never had a problem. Mostly this is with servlets though. I could
> have each session have it's own connection but then I have to be
> concerned with how many licenses there are. Connection pooling is
> interesting. I do understand what pooling is for but could someone
> explain why sharing a single connection is not a good idea.
> gkelly
Hi. It depends on what you're doing. If you're not highly multi-threaded,
and you're not doing anything transactional, ideally only doing simple
read-only queries, and not altering the default operating mode of the
statements (such as setMaxRows() etc), then you should be OK sharing a
connection.
If any of these descriptions are not like your code, then you are
maybe better off using a separate connection per logical thread.
1 - Multi-threading: The DBMS is going to see a single connection as
a simple user with a single series of queries/updates. The driver will
certainly serialize access to the connection to make it look that way,
so if you have lots of threads doing separate queries, they may be faster
with their own connection.
2 - If you are doing anything transactional, it is enacted at the
connection level, and all statements will afect and be affected. You
don't want one thread's transaction to hold all the locks from some other
thread's innocent 'select * from the universe' query.
3 - Some DBMSes only implement functionality such as max rows per query
on a connection level, so if you ask for that with one statement, and
the driver isn't clever (it happens), this setting could silently affect
all statements.
Making a connection is slow, so you should use pooling. The same is
true for PreparedStatements, so you should ideally use a pooling
system that also pools PreparedStatements with it's pooled connections.
Joe Weinstein at B.E.A.
by