Thread: Deadlock situation using foreign keys (reproduceable)
As promised here's an example of deadlock using foreign keys. create table lang ( id integer not null primary key, name text ); insert into lang values (1, 'English'); insert into lang values (2, 'German'); create table country ( id integer not null primary key, name text ); insert into country values (10, 'USA'); insert into country values (11, 'Austria'); create table entry ( id integer not null primary key, lang_id integer not null references lang(id), country integer not nullreferences country(id), txt text ); insert into entry values (100, 1, 10, 'Entry 1'); insert into entry values (101, 2, 11, 'Entry 2'); insert into entry values (102, 1, 11, 'Entry 3'); transaction A:begin; transaction A:update entry set txt='Entry 1.1' where id=100; transaction B:begin; transaction B:update entry set txt='Entry 3.1' where id=102; transaction A:update entry set txt='Entry 2.1' where id=101; transaction A:deadlock detected My application has around 100 tables with a few central tables like "languages", "users", "types".... , and it deadlocked a lot before I patched the postmaster (I added a test to ignore some special, central tables like "languages", and not use "select ... for update" on these tables, as they're nearly static and only changed during maintaince, where I'm the only user and nothing bad may happen) I still think that this behaviour is wrong, I asked my collegue to check what oracle does in this case, it seems that oracle simply makes some sort of "read lock" on the referenced tables, but no such strong lock as in postgres. Best regards,Mario Weilguni
On Thu, 11 Apr 2002, Mario Weilguni wrote: > As promised here's an example of deadlock using foreign keys. > > create table lang ( > id integer not null primary key, > name text > ); > insert into lang values (1, 'English'); > insert into lang values (2, 'German'); > > create table country ( > id integer not null primary key, > name text > ); > insert into country values (10, 'USA'); > insert into country values (11, 'Austria'); > > create table entry ( > id integer not null primary key, > lang_id integer not null references lang(id), > country integer not null references country(id), > txt text > ); > insert into entry values (100, 1, 10, 'Entry 1'); > insert into entry values (101, 2, 11, 'Entry 2'); > insert into entry values (102, 1, 11, 'Entry 3'); > > transaction A:begin; > transaction A:update entry set txt='Entry 1.1' where id=100; > transaction B:begin; > transaction B:update entry set txt='Entry 3.1' where id=102; > transaction A:update entry set txt='Entry 2.1' where id=101; > transaction A:deadlock detected Please see past disussions on the fact that the lock grabbed is too strong. I'm going to (when I get time to work on it) try out a lower strength lock that Alex Hayward made a patch for that should limit/prevent these cases. Thanks for sending a nice simple test case to try against :)
Hi all, I've been having a lot of trouble with deadlocks in 7.2.1 because of foreign keys. I dug out a couple of messages from the list archives which cover this topic. One particular message indicates a fix was being worked on. -------- Date: Thu, 11 Apr 2002 09:03:06 -0700 (PDT) From: Stephan Szabo Please see past disussions on the fact that the lock grabbed is too strong. I'm going to (when I get time to work on it) try out a lower strength lock that Alex Hayward made a patch for that should limit/prevent these cases. Thanks for sending a nice simple test case to try against :) ---------- My question is if this is now resolved for 7.3? I'm trying to figure out what I can do with my 7.2.1 problems... Has anyone got a back-ported patch, perhaps against 7.2.2? Any help appreciated, thanks! Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com tom@nooper.com - Testing - http://nooper.co.jp/labs
On 26 Aug 2002, Thomas O'Dowd wrote: > Hi all, > > I've been having a lot of trouble with deadlocks in 7.2.1 because of > foreign keys. I dug out a couple of messages from the list archives > which cover this topic. > > One particular message indicates a fix was being worked on. > -------- > Date: Thu, 11 Apr 2002 09:03:06 -0700 (PDT) > From: Stephan Szabo > > Please see past disussions on the fact that the lock grabbed is too > strong. I'm going to (when I get time to work on it) try out a lower > strength lock that Alex Hayward made a patch for that should > limit/prevent these cases. Thanks for sending a nice simple test case > to try against :) > ---------- > > My question is if this is now resolved for 7.3? I'm trying to figure out > what I can do with my 7.2.1 problems... Has anyone got a back-ported > patch, perhaps against 7.2.2? It's actually not resolved because my attempts at the lower strength lock opened up holes and fixing those opened up new deadlock situations, and I ran out of time in the 7.3 cycle to try to make it work.
Thanks for your feedback Stephan. Seems like a tough fix. Pitty it won't make it into 7.3. I presume there are other folk out there suffering from the same problems that I'm having. What approaches if any have people taken to work around this problem? I read in the list that one user patched his postmaster to explictly ignore the RI "FOR UPDATE" on tables he knew weren't changing. Can't find the detailed message in the archives right now, but I read it earlier. Any other work-arounds? Tom. On Mon, 2002-08-26 at 12:46, Stephan Szabo wrote: > > My question is if this is now resolved for 7.3? I'm trying to figure out > > what I can do with my 7.2.1 problems... Has anyone got a back-ported > > patch, perhaps against 7.2.2? > > It's actually not resolved because my attempts at the lower strength lock > opened up holes and fixing those opened up new deadlock situations, and > I ran out of time in the 7.3 cycle to try to make it work. -- Thomas O'Dowd. - Nooping - http://nooper.com tom@nooper.com - Testing - http://nooper.co.jp/labs
On 26 Aug 2002, Thomas O'Dowd wrote: > Thanks for your feedback Stephan. Seems like a tough fix. Pitty it won't > make it into 7.3. I presume there are other folk out there suffering > from the same problems that I'm having. What approaches if any have > people taken to work around this problem? I read in the list that one > user patched his postmaster to explictly ignore the RI "FOR UPDATE" on > tables he knew weren't changing. Can't find the detailed message in the > archives right now, but I read it earlier. Any other work-arounds? For many schemas setting the constraints to deferrable makes the problem go away in practice (due to the fact the constraint locks are held for less time). Removing for update really only does work if you know that one side of the constraint never changes concurrently with the other side.
I wrote this patch for my system, and it works fine. However, it's a really ugly workaround. I can publish the source if anybody is interested. Am Montag, 26. August 2002 06:33 schrieb Thomas O'Dowd: > Thanks for your feedback Stephan. Seems like a tough fix. Pitty it won't > make it into 7.3. I presume there are other folk out there suffering > from the same problems that I'm having. What approaches if any have > people taken to work around this problem? I read in the list that one > user patched his postmaster to explictly ignore the RI "FOR UPDATE" on > tables he knew weren't changing. Can't find the detailed message in the > archives right now, but I read it earlier. Any other work-arounds? > > Tom. > > On Mon, 2002-08-26 at 12:46, Stephan Szabo wrote: > > > My question is if this is now resolved for 7.3? I'm trying to figure > > > out what I can do with my 7.2.1 problems... Has anyone got a > > > back-ported patch, perhaps against 7.2.2? > > > > It's actually not resolved because my attempts at the lower strength lock > > opened up holes and fixing those opened up new deadlock situations, and > > I ran out of time in the 7.3 cycle to try to make it work.
On Mon, 26 Aug 2002, Mario Weilguni wrote: > I wrote this patch for my system, and it works fine. However, it's a > really ugly workaround. I can publish the source if anybody is > interested. Which, the for update one or a lower strength lock? In either case, certainly.
Not a lower strength lock, I would, but I'm not so familiar with the postgres internals. I modified ri-triggers.c to exclude certain tables from the locking itself (because I know the tables are not updated). -----Ursprüngliche Nachricht----- Von: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] Gesendet: Montag, 26. August 2002 17:29 An: Mario Weilguni Cc: pgsql-hackers@postgresql.org Betreff: Re: [HACKERS] Deadlock situation using foreign keys (reproduceable) On Mon, 26 Aug 2002, Mario Weilguni wrote: > I wrote this patch for my system, and it works fine. However, it's a > really ugly workaround. I can publish the source if anybody is > interested. Which, the for update one or a lower strength lock? In either case, certainly. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
On Mon, 26 Aug 2002, Mario Weilguni wrote: > Not a lower strength lock, I would, but I'm not so familiar with the postgres > internals. I modified ri-triggers.c to exclude certain tables from the > locking itself (because I know the tables are not updated). It might help the op of this thread, so it's probably worth posting. :)