From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Guy Rouillier <guyr(at)masergy(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Debugging deadlocks |
Date: | 2005-03-27 08:37:44 |
Message-ID: | 20050327083744.GA46661@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Mar 27, 2005 at 12:54:28AM -0600, Guy Rouillier wrote:
> I'm getting the following in the server log:
>
> 2005-03-27 06:04:21 GMT estat DETAIL: Process 20928 waits for ShareLock
> on transaction 7751823; blocked by process 20929.
> Process 20929 waits for ShareLock on transaction 7768115;
> blocked by process 20928.
> 2005-03-27 06:04:21 GMT estat CONTEXT: SQL statement "SELECT 1 FROM
> ONLY "rumba"."service_plane" x WHERE "service_plane_id" = $1 FOR UPDATE
> OF x"
...
> The service_plane table is a reference table, i.e., a fixed set of
> values used only to validate foreign keys. So the code doesn't have any
> update statements on that table. I'm assuming PostgreSQL is generating
> that SQL to validate the foreign key. But why is it selecting for
> update?
To make sure the referenced key can't change until the transaction
completes and the referencing row becomes visible to other transactions
(or is rolled back) -- otherwise other transactions could change
or delete the referenced key and not know they'd be breaking your
referential integrity. The current implementation supports only
exclusive row-level locks (SELECT FOR UPDATE), but I think Alvaro
might be working on shared row-level locks for a future release.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
From | Date | Subject | |
---|---|---|---|
Next Message | Qingqing Zhou | 2005-03-27 09:38:05 | Re: Debugging deadlocks |
Previous Message | Tom Lane | 2005-03-27 07:49:07 | Re: Major Performance issue |