Re: Deadlock problem

On Fri, 18 Nov 2005, Vit Timchishin wrote:

> I have an multithreaded java application using postgresql. I am using UR
> mode (handling locking internally) and wa shoping to have no problems
> with locks. But now I have locking porlbme where there is only one
> thread calling posgresql and locking in wait. Looking at pg_lock I can
> see a lot of locks each holding exclusive a transaction (other two
> fields are empty). And for one transaction there is one PID holding
> Exclusive Lock and another waiting for shared lock for same transaction.
> I suppose that this means that I've first used connection object from
> one thread and this thread pid (linux x86-64) took exclusive lock and
> now another thread tries to use same connection and is going into lock.
> Am I correct? If so, why this exclusive locks are help while there no
> other queries executed? Or does this mean I can't use same connection
> object from different threads?

I'm a little bit unclear on how you are using connections.  Does your
application have only one Connection object that it shares among various
threads?  If this is the case the postgresql jdbc driver will block a
thread when another thread is executing a statement from the same
connection.  The server does not support multiplexing queries so the
driver can only allow one to execute at any given time.  If this is the
case you may want to open more than one Connection in your application.
This situation cannot cause a block on the server side because a
Connection cannot block itself.  If you are blocked on the server side
(with only one Connection) then you must be waiting for another
connection's resources, perhaps from another application or maintenence
command.

Kris Jurka