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)
Список
Дерево обсуждения
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 по дате отправления