Date: Tue, 28 Sep 2004 09:14:20 +0300 (EEST)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Subject: FOREIGN KEY and AccessExclusiveLock
In-Reply-To: <4121D55D.1040105@archonet.com>
Message-ID: <Pine.LNX.4.44.0409280842330.29178-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


Hi, all the below are for PostgreSQL 7.4.2.

I noticed that during 
ALTER TABLE kid ADD CONSTRAINT "parcon" FOREIGN KEY (parid) on parent(id)
pgsql tries to acquire an AccessExclusiveLock on *both* kid
(which is pretty natural since it adheres with the docs, and it is an 
alter command) *and* parent.

Whats the purpose of the AccessExclusiveLock on parent table?
Is there a way this alter command will affect parent's data or schema?

Lets see a simple example:

dynacom=# CREATE TABLE parent(id int PRIMARY KEY);
CREATE TABLE
dynacom=# CREATE TABLE kid(parid int);
CREATE TABLE
dynacom=#

Then in session 1:

dynacom=# BEGIN ;
BEGIN
dynacom=# SELECT * from parent ;
 id
----
(0 rows)

dynacom=# 

In Session 2:
dynacom=# BEGIN ;
BEGIN
dynacom=# ALTER TABLE kid ADD CONSTRAINT "parcon" FOREIGN KEY (parid) 
references parent(id);

*here Session 2 is deadlocked*

In Session 1:
dynacom=# SELECT c.relname,l.mode,l.granted from pg_locks l,pg_class c 
where l.relation=c.oid;
   relname    |        mode         | granted
--------------+---------------------+---------
 kid          | AccessExclusiveLock | t
 pg_locks     | AccessShareLock     | t
 pg_class     | AccessShareLock     | t
 parent       | AccessExclusiveLock | f
 parent       | AccessShareLock     | t
 pg_namespace | AccessShareLock     | t
(6 rows)

dynacom=# 

Again in Session 1:

dynacom=# end;
COMMIT
dynacom=#

In Session 2:
ALTER TABLE
dynacom=# 

Now imagine that session 2 is "called" by session 1, with commiting
after session 2 is done, we have clearly a deadlock situation.

The question is why an AccessExclusiveLock must be created
for the FK table?

Actually it puzzled me alot, since for me Session 1 is a java program
"executing" XML in various forms, one of them being plain UNIX (exec())
commands, which in turn sometimes are psql commands.

It was hard to imagine that an innocent select on the parent table in the 
java program
and an alter table on a child table as a pgsql UNIX command would cause a 
deadlock situation.

The natural workaround was to immediately commit in the java program
after select and before UNIX command (psql) is executed.

Thanx.

-- 
-Achilleus