Deadlock. Referential Integrity checks select for update?

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