pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFOREIGN KEY and AccessExclusiveLock
6+ messages / 2 participants
[nested] [flat]
* FOREIGN KEY and AccessExclusiveLock
@ 2004-09-28 06:14 Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-09-28 14:05 ` Re: FOREIGN KEY and AccessExclusiveLock Tom Lane <tgl@sss.pgh.pa.us>
2004-09-29 13:19 ` Re: FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 6+ messages in thread
From: Achilleus Mantzios @ 2004-09-28 06:14 UTC (permalink / raw)
To: 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 | 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: FOREIGN KEY and AccessExclusiveLock
2004-09-28 06:14 FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-09-28 14:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-28 13:34 ` Re: FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-09-29 05:36 ` Re: FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 2 replies; 6+ messages in thread
From: Tom Lane @ 2004-09-28 14:05 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> Whats the purpose of the AccessExclusiveLock on parent table?
We're adding a trigger to it.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: FOREIGN KEY and AccessExclusiveLock
2004-09-28 06:14 FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-09-28 14:05 ` Re: FOREIGN KEY and AccessExclusiveLock Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-09-28 13:34 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 6+ messages in thread
From: Achilleus Mantzios @ 2004-09-28 13:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
O Tom Lane Ýãñáøå óôéò Sep 28, 2004 :
> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> > Whats the purpose of the AccessExclusiveLock on parent table?
>
> We're adding a trigger to it.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: FOREIGN KEY and AccessExclusiveLock
2004-09-28 06:14 FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-09-28 14:05 ` Re: FOREIGN KEY and AccessExclusiveLock Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-09-29 05:36 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 6+ messages in thread
From: Achilleus Mantzios @ 2004-09-29 05:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
O Tom Lane Ýãñáøå óôéò Sep 28, 2004 :
> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> > Whats the purpose of the AccessExclusiveLock on parent table?
>
> We're adding a trigger to it.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: FOREIGN KEY and AccessExclusiveLock
2004-09-28 06:14 FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-09-29 13:19 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-09-30 10:51 ` Re: FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 6+ messages in thread
From: Achilleus Mantzios @ 2004-09-29 13:19 UTC (permalink / raw)
To: pgsql-sql; +Cc: tgl@sss.pgh.pa.us
Hmm, (something went wrong with some mailer)
Tom Lane wrote:
> We're adding a trigger to it.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: FOREIGN KEY and AccessExclusiveLock
2004-09-28 06:14 FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-09-29 13:19 ` Re: FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-09-30 10:51 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Achilleus Mantzios @ 2004-09-30 10:51 UTC (permalink / raw)
To: pgsql-sql; +Cc: tgl@sss.pgh.pa.us
O Achilleus Mantzios Ýãñáøå óôéò Sep 29, 2004 :
>
> Hmm, (something went wrong with some mailer)
And again....
hope it gets right this time...
>
> Tom Lane wrote:
> > We're adding a trigger to it.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-09-30 10:51 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-28 06:14 FOREIGN KEY and AccessExclusiveLock Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-09-28 14:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-28 13:34 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-09-29 05:36 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-09-29 13:19 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-09-30 10:51 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox