Re: Random Deadlock on DROP CONSTRAINT

From: Mark Mandel <mark(dot)mandel(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Random Deadlock on DROP CONSTRAINT
Date: 2009-03-09 06:04:19
Message-ID: 4153ee230903082304v81f0aa8g7f527f2d6e41122f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Final words -

If you use PostGres and Hibernate, * everything * has to be in a
transaction, ALWAYS.

That and remember to close your sessions.

Thanks for listening to my rambling.

Mark

On Mon, Mar 9, 2009 at 10:31 AM, Mark Mandel <mark(dot)mandel(at)gmail(dot)com> wrote:
> Well, there was an issue there, in that I wasn't closing my
> connection, but I'm still getting the same log as I did before.
>
> It seems to be when Hibernate lazy loads some data.  The lazy loading
> seems to happen within the session (connection), but the transaction
> doesn't get closed?
>
> Mark
>
> On Mon, Mar 9, 2009 at 10:05 AM, Mark Mandel <mark(dot)mandel(at)gmail(dot)com> wrote:
>> Omg... i think this one was all on me....
>>
>> What I thought was closing the connection at the end of my
>> processing... doesn't look like it was.
>>
>> Pretty sure this has fixed it. Will let you know.
>>
>> Mark
>>
>> On Mon, Mar 9, 2009 at 9:52 AM, Mark Mandel <mark(dot)mandel(at)gmail(dot)com> wrote:
>>> Thanks for this -
>>>
>>> I've attached the results of the following query -
>>> select pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid,
>>> pg_locks.mode, pg_locks.granted,pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30),
>>> pg_stat_activity.query_start, age(now(),pg_stat_activity.query_start)
>>> as "age", pg_stat_activity.procpid from pg_stat_activity,pg_locks left
>>> outer join pg_class on (pg_locks.relation = pg_class.oid)  where
>>> pg_locks.pid=pg_stat_activity.procpid order by query_start;
>>>
>>> It looks to me like I have to many items that are 'idle in transaction'...
>>>
>>> I am using JDBC connection pooling, but I was sure I was closing them
>>> when I was done.
>>>
>>> Mark
>>>
>>> On Mon, Mar 9, 2009 at 8:29 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>>>> On Sun, Mar 8, 2009 at 2:54 PM, Mark Mandel <mark(dot)mandel(at)gmail(dot)com> wrote:
>>>>> Just a note on this -
>>>>>
>>>>> I have tried it on both 8.2 and 8.3
>>>>>
>>>>> Maybe 'deadlock' isn't the right word, so much as 'hang'.. in that
>>>>> once it decided to stop at that point, it goes no further, unless I
>>>>> restart the app server that has the connecton to the database.
>>>>>
>>>>> It seems to be completely random, and I'm got no idea what is going on.
>>>>
>>>> Well, pg_locks can tell you, and us, a lot.  Next time it's happening
>>>> grab the contents of pg_locks and attach it to an email here.  Most
>>>> likely it's some long running transaction or something blocking the
>>>> access you need.
>>>>
>>>
>>>
>>>
>>> --
>>> E: mark(dot)mandel(at)gmail(dot)com
>>> W: www.compoundtheory.com
>>>
>>
>>
>>
>> --
>> E: mark(dot)mandel(at)gmail(dot)com
>> W: www.compoundtheory.com
>>
>
>
>
> --
> E: mark(dot)mandel(at)gmail(dot)com
> W: www.compoundtheory.com
>

--
E: mark(dot)mandel(at)gmail(dot)com
W: www.compoundtheory.com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2009-03-09 07:11:31 Re: Date/time of last commit
Previous Message Tom Lane 2009-03-09 01:22:54 Re: Enable user access from remote host