Re: Deadlock problem

On Fri, 18 Nov 2005 13:31:23 -0500 (EST), Kris Jurka wrote:

>
>
>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.

I am not using one connection and at the time of block there is only one active query (that is locked) at the
whole database (in my test case). But for one transaction it may be used by different java threads (e.g. main
thread and finalizer) and it seems that this is producing problems because exclusive lock is held after
statement have finished. It is possible that I am still having open resultsets (did not check), but they are all
forward only and not updateable.

С уважением,
Виталий Валериевич Тимчишин,
Технический Директор
ООО "Голден Технолоджис"
http://www.gtech-ua.com