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 :) ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
