Deadlock. Referential Integrity checks select for update? - Mailing list pgsql-general

Hi

First a simple question ...

I have a table "access_log" that has foreign keys "app_id" and
"app_user_id" that reference the "application_type" and "app_user"
tables.  When I insert into "access_log", the referential integrity
triggers generate these queries:

  SELECT 1 FROM ONLY "public"."application_type" x
  WHERE "app_id" = $1 FOR UPDATE OF x

  SELECT 1 FROM ONLY "public"."app_user" x
  WHERE "app_user_id" = $1 FOR UPDATE OF x

I don't understand why these two queries include "FOR UPDATE".  Can
anyone explain that?


That question may in fact be a red herring as far as my real problem
goes, so I'll describe that too ...

I have a web application in which transactions die with database
deadlocks under load.  I have isolated the problem to when two
different types of transaction occur in parallel:

  Transaction A:
    create a new record in "session" and get its ID then
    create a new record in "access_log" and get its ID

  Transaction B:
    retrieve an existing record from "session" using its ID then
    create a new record in "access_log" and get its ID

In practice, the initial select from session in transaction B doesn't
affect the deadlock, so I've omitted it here.

The theoretical parallel sequence of queries is:

  A1 insert_session         B1 insert_access_log
  A2 get_session_id         B2 get_access_log_id
  A3 insert_access_log      B3 commit
  A4 get_access_log_id
  A5 commit

The postgresql log show this is the sequence that causes deadlock:

  A1  insert_session
  riA  select from app_user for update
                            B1  insert_access_log
                            riB  select from app_type for update
                            riB  select from app_user for update
                            << Process B blocked >>
  A2  get_session_id
  A3  insert_access_log
  riA  select from app_type for update
                       << DEADLOCK >>
  << Process A killed >>

"riA" and "riB" refer to system-generated statements which check
referential integrity.

Neither transaction A nor transaction B ever updates either the
application_type or app_user tables and the deadlock only seems
to happen as a result of the referential integrity checks locking
the parent tables for update.  So it seems that even if I'm careful
to always do my updates in a consistent order to avoid deadlock,
the referential integrity constraints can cause deadlock themselves.


Here's the snippet from the postgresql log:

15:37:15 [20480] LOG:  statement: select getdatabaseencoding()
15:37:15 [20480] LOG:  statement: begin
15:37:15 [20481] LOG:  statement: select getdatabaseencoding()
15:37:15 [20481] LOG:  statement: begin
15:37:40 [20480] LOG:  statement: INSERT INTO session (app_user_id,
session_data) VALUES ('1', '')
15:37:40 [20480] LOG:  statement: SELECT 1 FROM ONLY "public"."app_user"
x WHERE "app_user_id" = $1 FOR UPDATE OF x

15:37:51 [20481] LOG:  statement:
  INSERT INTO access_log (app_id, app_user_id, log_data)
  VALUES ('1', '1', 'Log data here')

15:37:51 [20481] LOG:  statement: SELECT 1 FROM ONLY
"public"."application_type" x WHERE "app_id" = $1 FOR UPDATE OF x
15:37:51 [20481] LOG:  statement: SELECT 1 FROM ONLY "public"."app_user"
x WHERE "app_user_id" = $1 FOR UPDATE OF x

15:38:04 [20480] LOG:  statement: SELECT currval('seq_session_id')

15:38:17 [20480] LOG:  statement:
  INSERT INTO access_log (app_id, app_user_id, log_data)
  VALUES ('1', '1', 'Log data here')

15:38:17 [20480] LOG:  statement: SELECT 1 FROM ONLY
"public"."application_type" x WHERE "app_id" = $1 FOR UPDATE OF x

15:38:20 [20480] ERROR:  deadlock detected
DETAIL:  Process 20480 waits for ShareLock on transaction 74278; blocked
by process 20481.
    Process 20481 waits for ShareLock on transaction 74275; blocked by
process 20480.
15:38:20 [20480] LOG:  statement: rollback




And here's a short script to create the database:

#!/bin/sh

DBNAME=deadpool

if psql -l | grep -q $DBNAME
then
    dropdb $DBNAME || exit
fi

createdb $DBNAME

psql -d $DBNAME <<EOF

  create sequence seq_access_log_id;

  create sequence seq_session_id;

  create table app_user (
    app_user_id          INT4   not null,
    login_name           TEXT   not null,
    constraint PK_APP_USER primary key (app_user_id)
  );

  create table application_type (
    app_id               INT4   not null,
    app_desc             TEXT   not null,
    constraint PK_APPLICATION_TYPE primary key (app_id)
  );

  create table session (
    session_id           INT8   not null default
nextval('seq_session_id'),
    app_user_id          INT4   not null,
    session_data         TEXT   not null,
    constraint PK_SESSION primary key (session_id),
    constraint FK_SESS_REFERENCE_APP_ foreign key (app_user_id)
      references app_user (app_user_id)
      deferrable
  );

  create table access_log (
    access_log_id        INT8   not null default
nextval('seq_access_log_id'),
    app_id               INT4   not null,
    app_user_id          INT4   not null,
    log_data             TEXT   null,
    constraint PK_ACCESS_LOG primary key (access_log_id),
    constraint FK_ACCE_REFERENCE_APPL foreign key (app_id)
      references application_type (app_id)
      deferrable,
    constraint FK_ACCE_REFERENCE_APP_ foreign key (app_user_id)
      references app_user (app_user_id)
      deferrable
  );

  create index access_log_app_type_fk on access_log (
    app_id
  );

  create index access_log_app_user_id on access_log (
    app_user_id
  );

  insert into application_type values (1, 'Public Web App');

  insert into app_user values (1, 'anonymous');

EOF


I also have a Perl script that steps through the two transactions in the
appropriate order and causes the deadlock every time.


Any assistance anyone can offer would be appreciated.

Regards
Grant