Thread: Random Deadlock on DROP CONSTRAINT
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
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
Attachment
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