Random Deadlock on DROP CONSTRAINT

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.

Thanks in advance.

Mark

On Sun, Mar 8, 2009 at 11:39 AM, Mark Mandel <mark.mandel@gmail.com> wrote:

All,

I seem to have a weird one here.

I'm using Hibernate on my application to generate my DDL for postGres
whenever it starts up, so when doing automated testing, the database
tends to get tables and constrains dropped quite regularly, so as to
have stable test data to work from in each test.

Only occasionally, I get a deadlock on the command:
   alter table Doctor
       drop constraint FK7A547D3FE8829FE8

Which means I have to restart my unit tests all over again.

Looking into the postgres statistics tables I can see that:

The statement:
"alter table Doctor drop constraint FK7A547D3FE8829FE8" Is 'waiting' to be true.

(from pg_stat_activity)

Delving deeper, in pg_locks, there are several records for the database,

The ones that jump out at me are:
On table: "doctor" there is a "AccessExclusiveLock" for "alter table
Doctor drop constraint", which has not been granted.

Everything else is 'AccessShareLock', or Exclusive locks that don't
seem to match up to a table.

(Going through locking tables in a new one to me, so bare with me on this one)

This is all on my local machine, so there is noone else accessing the
database at the time I'm writing these tests.

Any help on this would be greatly appreciated.

Mark

--
E: mark.mandel@gmail.com
W: www.compoundtheory.com

--
E: mark.mandel@gmail.com
W: www.compoundtheory.com

On Sun, Mar 8, 2009 at 2:54 PM, Mark Mandel <mark.mandel@gmail.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.

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.marlowe@gmail.com> wrote:

On Sun, Mar 8, 2009 at 2:54 PM, Mark Mandel <mark.mandel@gmail.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.mandel@gmail.com
W: www.compoundtheory.com

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.mandel@gmail.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.marlowe@gmail.com> wrote:

On Sun, Mar 8, 2009 at 2:54 PM, Mark Mandel <mark.mandel@gmail.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.mandel@gmail.com
W: www.compoundtheory.com

--
E: mark.mandel@gmail.com
W: www.compoundtheory.com

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.mandel@gmail.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.mandel@gmail.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.marlowe@gmail.com> wrote:

On Sun, Mar 8, 2009 at 2:54 PM, Mark Mandel <mark.mandel@gmail.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.mandel@gmail.com
W: www.compoundtheory.com

--
E: mark.mandel@gmail.com
W: www.compoundtheory.com

--
E: mark.mandel@gmail.com
W: www.compoundtheory.com

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.mandel@gmail.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.mandel@gmail.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.mandel@gmail.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.marlowe@gmail.com> wrote:

On Sun, Mar 8, 2009 at 2:54 PM, Mark Mandel <mark.mandel@gmail.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.mandel@gmail.com
W: www.compoundtheory.com

--
E: mark.mandel@gmail.com
W: www.compoundtheory.com

--
E: mark.mandel@gmail.com
W: www.compoundtheory.com

--
E: mark.mandel@gmail.com
W: www.compoundtheory.com