Re: Debugging deadlocks

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/