FOREIGN KEY and AccessExclusiveLock

Поиск
Список
Период
Сортировка
От Achilleus Mantzios
Тема FOREIGN KEY and AccessExclusiveLock
Дата
Msg-id Pine.LNX.4.44.0409280842330.29178-100000@matrix.gatewaynet.com
обсуждение исходный текст
Ответ на Re: multi column foreign key for implicitly unique columns  (Richard Huxton <dev@archonet.com>)
Ответы Re: FOREIGN KEY and AccessExclusiveLock
Re: FOREIGN KEY and AccessExclusiveLock
Список pgsql-sql
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 | tpg_locks     | AccessShareLock
|tpg_class     | AccessShareLock     | tparent       | AccessExclusiveLock | fparent       | AccessShareLock     |
tpg_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



В списке pgsql-sql по дате отправления:

Предыдущее
От: sad
Дата:
Сообщение: Temp Tables
Следующее
От: Martin Marques
Дата:
Сообщение: Best way to know if there is a row