FOREIGN KEY and AccessExclusiveLock

Поиск
Список
Период
Сортировка
Искать
От
Achilleus Mantzios
Тема
FOREIGN KEY and AccessExclusiveLock
Дата
Msg-id
Pine.LNX.4.44.0409280842330.29178-100000@matrix.gatewaynet.com
Ответ на
Список
Дерево обсуждения
multi column foreign key for implicitly unique columns Markus Bertheau <twanger@bluetwanger.de>
Re: multi column foreign key for implicitly unique columns Oliver Elphick <olly@lfix.co.uk>
Re: multi column foreign key for implicitly unique columns Markus Bertheau <twanger@bluetwanger.de>
Re: multi column foreign key for implicitly unique columns Josh Berkus <josh@agliodbs.com>
Re: multi column foreign key for implicitly unique columns Markus Bertheau <twanger@bluetwanger.de>
Re: multi column foreign key for implicitly unique columns Jan Wieck <JanWieck@Yahoo.com>
Re: multi column foreign key for implicitly unique columns Markus Bertheau <twanger@bluetwanger.de>
Re: multi column foreign key for implicitly unique columns Jan Wieck <JanWieck@Yahoo.com>
Re: multi column foreign key for implicitly unique columns Markus Bertheau <twanger@bluetwanger.de>
Re: multi column foreign key for implicitly unique columns Rod Taylor <pg@rbt.ca>
Re: multi column foreign key for implicitly unique columns Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: multi column foreign key for implicitly unique columns Tom Lane <tgl@sss.pgh.pa.us>
Re: multi column foreign key for implicitly unique columns Richard Huxton <dev@archonet.com>
Re: multi column foreign key for implicitly unique columns Tom Lane <tgl@sss.pgh.pa.us>
Re: multi column foreign key for implicitly unique columns Jan Wieck <JanWieck@Yahoo.com>
Re: multi column foreign key for implicitly unique columns Rod Taylor <pg@rbt.ca>
Re: multi column foreign key for implicitly unique columns Tom Lane <tgl@sss.pgh.pa.us>
Re: multi column foreign key for implicitly unique columns Richard Huxton <dev@archonet.com>
Re: multi column foreign key for implicitly unique columns Jan Wieck <JanWieck@Yahoo.com>
Re: multi column foreign key for implicitly unique columns Josh Berkus <josh@agliodbs.com>
Re: multi column foreign key for implicitly unique columns Jan Wieck <JanWieck@Yahoo.com>
Re: multi column foreign key for implicitly unique columns Josh Berkus <josh@agliodbs.com>
Re: multi column foreign key for implicitly unique columns Oliver Elphick <olly@lfix.co.uk>
Re: multi column foreign key for implicitly unique columns Jan Wieck <JanWieck@Yahoo.com>
Re: multi column foreign key for implicitly unique columns Josh Berkus <josh@agliodbs.com>
Re: multi column foreign key for implicitly unique columns Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: multi column foreign key for implicitly unique columns Bruno Wolff III <bruno@wolff.to>
Re: multi column foreign key for implicitly unique columns Josh Berkus <josh@agliodbs.com>
Re: multi column foreign key for implicitly unique columns Richard Huxton <dev@archonet.com>
Re: multi column foreign key for implicitly unique columns Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: multi column foreign key for implicitly unique columns Richard Huxton <dev@archonet.com>
Re: multi column foreign key for implicitly unique columns Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: multi column foreign key for implicitly unique columns Tom Lane <tgl@sss.pgh.pa.us>
Re: multi column foreign key for implicitly unique columns Markus Bertheau <twanger@bluetwanger.de>
Re: multi column foreign key for implicitly unique columns Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: multi column foreign key for implicitly unique columns Markus Bertheau <twanger@bluetwanger.de>
Re: multi column foreign key for implicitly unique columns Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: multi column foreign key for implicitly unique columns Tom Lane <tgl@sss.pgh.pa.us>
FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
Re: FOREIGN KEY and AccessExclusiveLock Tom Lane <tgl@sss.pgh.pa.us>
Re: FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
Re: FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
Re: FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
Re: FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>

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
Дата:
FAQ