Re: Deadlock situation using foreign keys (reproduceable)

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 :)