Обсуждение: Deadlock problem

Hello.

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?


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

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

On Fri, Nov 18, 2005 at 12:09:16PM +0200, Vit Timchishin wrote:

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

The answer to question (1) is "presumably", and the answer to
question (2) is "there is too another query being executed".  There
_must_ be a query being executed by the back end (whatever your
application thinks), because that's what's got the exlusive lock.
Moreover, an exclusive lock is pretty strong -- presumably a row is
being modified.  But I suspect that, whatever you're doing, you don't
really want to be using the same connection object from different
threads (unless you mean, serially -- like a connection pool which
hands out "the same connection" multiple times).  If this connection
is being actively used by more than one thread concurrently, things
are going to break in really surprising ways.

A
--
Andrew Sullivan  | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
        --H.W. Fowler

On Fri, 18 Nov 2005, Andrew Sullivan wrote:

> If this connection is being actively used by more than one thread
> concurrently, things are going to break in really surprising ways.
>

The JDBC driver is threadsafe so multiple threads may make calls
simultaneously, but internally only one is executed at a time.  Clearly
running with autocommit = false requires some coordination of
transactions, but multiple threads are not in and of themselves dangerous.

Kris Jurka

On Fri, Nov 18, 2005 at 02:49:53PM -0500, Kris Jurka wrote:
> simultaneously, but internally only one is executed at a time.  Clearly
> running with autocommit = false requires some coordination of
> transactions, but multiple threads are not in and of themselves dangerous.

I'd _really_ like to see an example of an application that did this
correctly and without any bugs.  I've seen people do it, yes; but I
haven't seen it working without fairly serious problems.  (Besides,
isn't solving this sort of pain what a pool is for?  Why reinvent the
wheel, and make it square to boot?)

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?
        --attr. John Maynard Keynes

There's also the option of using a queue that's accessed by various threads and a single thread with a single connection that actually talks with the DB. I've been sucessfull with this aproach, at least in a very particular and well defined scenario. As for reiventing the wheel, it's probably very pointless and prone to erros like Andrew mentions.  The DB has transactions, they work well, use them to your advantage.

Andres

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

On Mon, 21 Nov 2005, Vit Timchishin wrote:

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

Locks are always held until transaction commit, not the end of an
individual statement.  So, while you may only have one statement executing
you have more than one transaction in progress and this is causing your
deadlocks.  Consider a table with a primary key:

CREATE TABLE t(a int primary key);

Connection 1:
BEGIN;
INSERT INTO t VALUES (1);

Connection 2:
BEGIN;
INSERT INTO t VALUES (1);

Connection 2 must wait for connection 1 to either commit or rollback
before it knows whether it can legally insert its value.  This is true
even if connection 1 performed its insert a week ago, the transaction is
still in doubt even if the statement has completed running.

Kris Jurka

On Mon, 21 Nov 2005 18:06:37 -0500 (EST), Kris Jurka wrote:

>
>
>On Mon, 21 Nov 2005, Vit Timchishin wrote:
>
>> 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.
>>
>
>Locks are always held until transaction commit, not the end of an
>individual statement.  So, while you may only have one statement executing
>you have more than one transaction in progress and this is causing your
>deadlocks.

This is not the problem. I know good about locks, but this one:
 relation | database | transaction |  pid  |      mode       | granted
----------+----------+-------------+-------+-----------------+---------
          |          |     1109601 | 32172 | ExclusiveLock   | t

1) Not related to any relation. Relation and database fields are null
2) Intertransaction - transaction field of lock that is granted (example above) and that is trying to lock are
equal. The only difference is the pid column (and that the lock that is trying is SharedLock and granted = f).


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