Re: deadlock problems with foreign keys - Mailing list pgsql-hackers

Mario Weilguni wrote:
> I've a severe problem with deadlocks in postgres, when using referential integrity it's quite easy to trigger
deadlocks.I think the may be a bug in ri_trigger.c (discussed later). Here's some short example:
 
>
> create table languages (
>   id        integer not null,
>   name      text    not null,
>   primary key(id)
> );
>
> create table entry (
>   id        integer not null,
>   lang_id   integer,
>   sometext  text,
>   primary key (id),
>   foreign key ( lang_id ) references languages (id)
> );
>
> insert into languages values (1, 'english');
> insert into languages values (2, 'german');
>
> insert into entry values (1, 1, 'text 1');
> insert into entry values (2, 1, 'text 2');
>
>
> transaction A: begin;
> transaction A: update entry set sometext='text 1.1' where id=1;
> transaction A: .... do more time-consuming processing here...
> meanwhile, B: begin;
>            B: update entry set sometext='text 2.1' where id=2;
>
> -- both processes hang now
   Cannot  reproduce that problem in v7.2. Only B blocks until A   either commits or rolls back. So what exactly is
your "more   time-consuming processing"?
 

>
> I think this is too much locking here, because the logfile show's something like this:
> 'select 1 from "languages" where id=$1 for update' (2 times).
>
> Now I've a lot of tables (around 30) and use referential integrity a lot on ~10 columns (language, country....) , and
withmore fields it's very easy to deadlock the whole system (it happens a lot in my web applicaiton with ~20 concorrent
users).
>
> IMHO the "select ... for update" on languages is not necessary, since I do not want to update "lang_id", but I might
bewrong. The other problem is, that this will make postgres in benchmarks very slow (with many concurrent connections),
atleast if the application is not trivial.
 
>
> IMO the problem is in ri_trigger.c around line 390:
>         /* ----------
>          * The query string built is
>          *   SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...]
>          * The type id's for the $ parameters are those of the
>          * corresponding FK attributes. Thus, SPI_prepare could
>          * eventually fail if the parser cannot identify some way
>          * how to compare these two types by '='.
>          * ----------
>          */
>
> Any ideas if this is a bug or simply strict SQL standard?
   It  does  a  SELECT  ...  FOR  UPDATE because we don't have a   SELECT ... AND PLEASE DO NOT REMOVE.
   If we would only check  if  the  PK  is  there  now,  another   concurrent  transaction  could  delete the PK, it's
owncheck   cannot see our uncommitted row yet  and  we  end  up  with  a   violation.  And if you look at the comment a
fewlines up, it   explains why we cannot skip the check even if the  key  value   doesn't change.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com