A problem with many JDBC drivers is that only
one thread can use a Connection at any one
time -- otherwise a thread could send a query while another one is
receiving results, and this would be a bad thing for the database
engine.
The PostgreSQL JDBC Driver
is thread safe.
Consequently, if your application uses multiple threads then you do
not have to worry about complex algorithms to ensure that only one
uses the database at any time.
If a thread attempts to use the connection while another one is
using it, it will wait until the other thread has finished its
current operation. If it is a regular SQL
statement, then the operation consists of sending the statement and
retrieving any ResultSet (in full). If it
is a Fastpath call (e.g., reading a block
from a LargeObject) then it is the time to
send and retrieve that block.
This is fine for applications and applets but can cause a
performance problem with servlets. With servlets you can have a
heavy load on the connection. If you have several threads
performing queries then each but one will pause, which may not be what
you are after.
To solve this, you would be advised to create a pool of
connections. When ever a thread needs to use the database, it asks
a manager class for a Connection. The
manager hands a free connection to the thread and marks it as
busy. If a free connection is not available, it opens one. Once
the thread has finished with it, it returns it to the manager who
can then either close it or add it to the pool. The manager would
also check that the connection is still alive and remove it from
the pool if it is dead.
So, with servlets, it is up to you to use either a single
connection, or a pool. The plus side for a pool is that threads
will not be hit by the bottle neck caused by a single network
connection. The down side is that it increases the load on the
server, as a backend process is created for each
Connection. It is up to you and your
applications requirements.
