Thread: Tracking down deadlocks
I'm doing a bunch of data mining against a postgres database and have run into an interesting problem with deadlocks. The problem is, postgres is detecting them and then wacking the offending process, and I can't figure out what's causing them. I have a ton of select queries (but none for update), and then a single query to insert into a table. Nothing selects from that table. So where could the deadlock be? pg_stat_activity has a column named current_query, which would seem useful in tracking this down, but it's not being populated. Oh, I'm running 7.4.2.
Hi Ben, Check this mailing list for "foreign keys" and "deadlock". Short info: Postgres exclusively locks the referenced records of a foreign key relationship when the child record is updated, so multiple runs (in different transactions) of one insert query could cause deadlock if they update rows which reference the same parent keys in reverse order. Check your foreign keys... HTH, Csaba. On Wed, 2004-06-16 at 17:33, Ben wrote: > I'm doing a bunch of data mining against a postgres database and have > run into an interesting problem with deadlocks. The problem is, > postgres is detecting them and then wacking the offending process, and > I can't figure out what's causing them. I have a ton of select queries > (but none for update), and then a single query to insert into a table. > Nothing selects from that table. So where could the deadlock be? > > pg_stat_activity has a column named current_query, which would seem > useful in tracking this down, but it's not being populated. > > Oh, I'm running 7.4.2. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
I hate to reply so quickly to my own post, but I found out how to turn on the current_query (I somehow missed that config file line - doh!). It turns out that my insert statements are blocking each other. (An insert in one thread blocks the same insert with different values in another thread.) That's curious to me, as I thought that inserts were atomic? It doesn't happen for all inserts, just some of them, and the problem gets worse as the threads increase in number. I compiled my client libs with thread safety, and am running on linux. On Jun 16, 2004, at 8:33 AM, Ben wrote: > I'm doing a bunch of data mining against a postgres database and have > run into an interesting problem with deadlocks. The problem is, > postgres is detecting them and then wacking the offending process, and > I can't figure out what's causing them. I have a ton of select queries > (but none for update), and then a single query to insert into a table. > Nothing selects from that table. So where could the deadlock be? > > pg_stat_activity has a column named current_query, which would seem > useful in tracking this down, but it's not being populated. > > Oh, I'm running 7.4.2. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
Ben <bench@silentmedia.com> writes:
> I'm doing a bunch of data mining against a postgres database and have
> run into an interesting problem with deadlocks. The problem is,
> postgres is detecting them and then wacking the offending process, and
> I can't figure out what's causing them.
The processes involved in the deadlock should be identified in the
DETAIL message for the deadlock error. Perhaps you are using client
code that doesn't show you the DETAIL? If so, look in the postmaster
log. You can correlate the process PIDs mentioned in the message to
pg_stat_activity.procpid.
regards, tom lane
See my previous post, there I mentioned updates, but it applies to inserts too. On Wed, 2004-06-16 at 17:53, Ben wrote: > I hate to reply so quickly to my own post, but I found out how to turn > on the current_query (I somehow missed that config file line - doh!). > > It turns out that my insert statements are blocking each other. (An > insert in one thread blocks the same insert with different values in > another thread.) That's curious to me, as I thought that inserts were > atomic? It doesn't happen for all inserts, just some of them, and the > problem gets worse as the threads increase in number. > > I compiled my client libs with thread safety, and am running on linux. > > On Jun 16, 2004, at 8:33 AM, Ben wrote: > > > I'm doing a bunch of data mining against a postgres database and have > > run into an interesting problem with deadlocks. The problem is, > > postgres is detecting them and then wacking the offending process, and > > I can't figure out what's causing them. I have a ton of select queries > > (but none for update), and then a single query to insert into a table. > > Nothing selects from that table. So where could the deadlock be? > > > > pg_stat_activity has a column named current_query, which would seem > > useful in tracking this down, but it's not being populated. > > > > Oh, I'm running 7.4.2. > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
Thanks for the quick reply (and summary!). According to the messages I've found on the list, basically the answer seems to be, "don't do this." On the other hand, pretty much every message on the subject is pre-7.4. There is some mention of using deferred foreign keys to reduce the chance for a deadlock, but nothing says doing that actually eliminates the chance. Is this just a known limitation? In this particular instance, I probably could get rid of my foreign keys and if things go bad it wouldn't hurt anything.... but I make heavy use of foreign keys throughout the rest of my schema, which are useful for the programs that aren't doing data mining. I wouldn't want to get rid of those foreign keys. On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote: > Hi Ben, > > Check this mailing list for "foreign keys" and "deadlock". > Short info: > Postgres exclusively locks the referenced records of a foreign key > relationship when the child record is updated, so multiple runs (in > different transactions) of one insert query could cause deadlock if > they > update rows which reference the same parent keys in reverse order. > Check your foreign keys... > > HTH, > Csaba. > > On Wed, 2004-06-16 at 17:33, Ben wrote: >> I'm doing a bunch of data mining against a postgres database and have >> run into an interesting problem with deadlocks. The problem is, >> postgres is detecting them and then wacking the offending process, and >> I can't figure out what's causing them. I have a ton of select queries >> (but none for update), and then a single query to insert into a table. >> Nothing selects from that table. So where could the deadlock be? >> >> pg_stat_activity has a column named current_query, which would seem >> useful in tracking this down, but it's not being populated. >> >> Oh, I'm running 7.4.2. >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 5: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faqs/FAQ.html >
Hello, Deferred checks can greatly reduce the deadlock chance because of the timing of the foreign key check. I won't say it can eliminate them, and I don't think anyone here would suggest that you don't use Foreign keys. Sincerely, Joshua D. Drake Ben wrote: > Thanks for the quick reply (and summary!). > > According to the messages I've found on the list, basically the answer > seems to be, "don't do this." On the other hand, pretty much every > message on the subject is pre-7.4. There is some mention of using > deferred foreign keys to reduce the chance for a deadlock, but nothing > says doing that actually eliminates the chance. > > Is this just a known limitation? In this particular instance, I probably > could get rid of my foreign keys and if things go bad it wouldn't hurt > anything.... but I make heavy use of foreign keys throughout the rest of > my schema, which are useful for the programs that aren't doing data > mining. I wouldn't want to get rid of those foreign keys. > > On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote: > >> Hi Ben, >> >> Check this mailing list for "foreign keys" and "deadlock". >> Short info: >> Postgres exclusively locks the referenced records of a foreign key >> relationship when the child record is updated, so multiple runs (in >> different transactions) of one insert query could cause deadlock if they >> update rows which reference the same parent keys in reverse order. >> Check your foreign keys... >> >> HTH, >> Csaba. >> >> On Wed, 2004-06-16 at 17:33, Ben wrote: >> >>> I'm doing a bunch of data mining against a postgres database and have >>> run into an interesting problem with deadlocks. The problem is, >>> postgres is detecting them and then wacking the offending process, and >>> I can't figure out what's causing them. I have a ton of select queries >>> (but none for update), and then a single query to insert into a table. >>> Nothing selects from that table. So where could the deadlock be? >>> >>> pg_stat_activity has a column named current_query, which would seem >>> useful in tracking this down, but it's not being populated. >>> >>> Oh, I'm running 7.4.2. >>> >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 5: Have you checked our extensive FAQ? >>> >>> http://www.postgresql.org/docs/faqs/FAQ.html >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Attachment
So is everybody simply accepting the chance of deadlocks, thanks to their foreign keys? Given what I know about why this problem exists, it doesn't seem to have an easy solution.... but from my naive perspective it seems like something that we shouldn't have to just live with, either. On Jun 16, 2004, at 10:50 AM, Joshua D. Drake wrote: > Hello, > > Deferred checks can greatly reduce the deadlock chance because of the > timing of the foreign key check. I won't say it can eliminate them, > and I don't think anyone here would suggest that you don't use Foreign > keys. > > Sincerely, > > Joshua D. Drake > > Ben wrote: >> Thanks for the quick reply (and summary!). >> According to the messages I've found on the list, basically the >> answer seems to be, "don't do this." On the other hand, pretty much >> every message on the subject is pre-7.4. There is some mention of >> using deferred foreign keys to reduce the chance for a deadlock, but >> nothing says doing that actually eliminates the chance. >> Is this just a known limitation? In this particular instance, I >> probably could get rid of my foreign keys and if things go bad it >> wouldn't hurt anything.... but I make heavy use of foreign keys >> throughout the rest of my schema, which are useful for the programs >> that aren't doing data mining. I wouldn't want to get rid of those >> foreign keys. >> On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote: >>> Hi Ben, >>> >>> Check this mailing list for "foreign keys" and "deadlock". >>> Short info: >>> Postgres exclusively locks the referenced records of a foreign key >>> relationship when the child record is updated, so multiple runs (in >>> different transactions) of one insert query could cause deadlock if >>> they >>> update rows which reference the same parent keys in reverse order. >>> Check your foreign keys... >>> >>> HTH, >>> Csaba. >>> >>> On Wed, 2004-06-16 at 17:33, Ben wrote: >>> >>>> I'm doing a bunch of data mining against a postgres database and >>>> have >>>> run into an interesting problem with deadlocks. The problem is, >>>> postgres is detecting them and then wacking the offending process, >>>> and >>>> I can't figure out what's causing them. I have a ton of select >>>> queries >>>> (but none for update), and then a single query to insert into a >>>> table. >>>> Nothing selects from that table. So where could the deadlock be? >>>> >>>> pg_stat_activity has a column named current_query, which would seem >>>> useful in tracking this down, but it's not being populated. >>>> >>>> Oh, I'm running 7.4.2. >>>> >>>> >>>> ---------------------------(end of >>>> broadcast)--------------------------- >>>> TIP 5: Have you checked our extensive FAQ? >>>> >>>> http://www.postgresql.org/docs/faqs/FAQ.html >>> >>> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 9: the planner will ignore your desire to choose an index scan if >> your >> joining column's datatypes do not match > > > -- > Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC > Postgresql support, programming shared hosting and dedicated hosting. > +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com > Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL > <jd.vcf>
Ben, My personal solution is to patch the postgres sources so the foreign key check does NOT lock the parent record. This has the disadvantage that in some situations the foreign key semantics are not guarrantied, but for my application works just fine. I think there was a patch posted to do exactly this, but I didn't use that one. Note that the postgres regression test was still running through fine after disabling the parent record locking, so the basic FK functionality is not affected by that. Now this patch worked for me, but I won't post it, cause it's just too dangerous, and I only have it for one postgres version and won't maintain it. If you want to take your data at danger, patch for yourself ;-) Hint: take a look at src/backend/utils/adt/ri_triggers.c in the postgres sources, that file contains the foreign key logic. Cheers, Csaba. On Wed, 2004-06-16 at 20:11, Ben wrote: > So is everybody simply accepting the chance of deadlocks, thanks to > their foreign keys? Given what I know about why this problem exists, it > doesn't seem to have an easy solution.... but from my naive perspective > it seems like something that we shouldn't have to just live with, > either. > > On Jun 16, 2004, at 10:50 AM, Joshua D. Drake wrote: > > > Hello, > > > > Deferred checks can greatly reduce the deadlock chance because of the > > timing of the foreign key check. I won't say it can eliminate them, > > and I don't think anyone here would suggest that you don't use Foreign > > keys. > > > > Sincerely, > > > > Joshua D. Drake > > > > Ben wrote: > >> Thanks for the quick reply (and summary!). > >> According to the messages I've found on the list, basically the > >> answer seems to be, "don't do this." On the other hand, pretty much > >> every message on the subject is pre-7.4. There is some mention of > >> using deferred foreign keys to reduce the chance for a deadlock, but > >> nothing says doing that actually eliminates the chance. > >> Is this just a known limitation? In this particular instance, I > >> probably could get rid of my foreign keys and if things go bad it > >> wouldn't hurt anything.... but I make heavy use of foreign keys > >> throughout the rest of my schema, which are useful for the programs > >> that aren't doing data mining. I wouldn't want to get rid of those > >> foreign keys. > >> On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote: > >>> Hi Ben, > >>> > >>> Check this mailing list for "foreign keys" and "deadlock". > >>> Short info: > >>> Postgres exclusively locks the referenced records of a foreign key > >>> relationship when the child record is updated, so multiple runs (in > >>> different transactions) of one insert query could cause deadlock if > >>> they > >>> update rows which reference the same parent keys in reverse order. > >>> Check your foreign keys... > >>> > >>> HTH, > >>> Csaba. > >>> > >>> On Wed, 2004-06-16 at 17:33, Ben wrote: > >>> > >>>> I'm doing a bunch of data mining against a postgres database and > >>>> have > >>>> run into an interesting problem with deadlocks. The problem is, > >>>> postgres is detecting them and then wacking the offending process, > >>>> and > >>>> I can't figure out what's causing them. I have a ton of select > >>>> queries > >>>> (but none for update), and then a single query to insert into a > >>>> table. > >>>> Nothing selects from that table. So where could the deadlock be? > >>>> > >>>> pg_stat_activity has a column named current_query, which would seem > >>>> useful in tracking this down, but it's not being populated. > >>>> > >>>> Oh, I'm running 7.4.2. > >>>> > >>>> > >>>> ---------------------------(end of > >>>> broadcast)--------------------------- > >>>> TIP 5: Have you checked our extensive FAQ? > >>>> > >>>> http://www.postgresql.org/docs/faqs/FAQ.html > >>> > >>> > >> ---------------------------(end of > >> broadcast)--------------------------- > >> TIP 9: the planner will ignore your desire to choose an index scan if > >> your > >> joining column's datatypes do not match > > > > > > -- > > Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC > > Postgresql support, programming shared hosting and dedicated hosting. > > +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com > > Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL > > <jd.vcf> > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match