From: Greg Stark <gsstark@mit.edu>
To: Brendan Duddridge <brendan@clickspace.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: App very unresponsive while performing simple update
Date: 28 May 2006 09:11:21 -0400
Message-ID: <874pzanvk6.fsf@stark.xeocode.com> (raw)
In-Reply-To: <21B15664-F4EE-462C-88B4-EEEB2B4A979F@clickspace.com>
References: <961F6F02-4B00-494C-B071-6DF76A305C14@clickspace.com>
<21B15664-F4EE-462C-88B4-EEEB2B4A979F@clickspace.com>
Brendan Duddridge <brendan@clickspace.com> writes:
> Further to my issue, the update never did finish. I received the following
> message in psql:
>
> ssprod=# update product set is_hungry = 'true' where date_modified >
> current_date - 10;
> ERROR: deadlock detected
> DETAIL: Process 18778 waits for ShareLock on transaction 711698780; blocked
> by process 15784.
> Process 15784 waits for ShareLock on transaction 711697098; blocked by process
> 18778.
What queries are those two processes executing? And what foreign keys do you
have on the product table or elsewhere referring to the product table? And
what indexes do you have on those columns?
I think this indicates you have foreign keys causing the deadlock. One process
is waiting until an update elsewhere finishes before modifying a record that
other update refers to via a foreign key. But that other process is waiting
similarly for the first one.
Do you have any foreign keys in other tables referring to the product table?
Do you have indexes on those other tables? The update needs to check those
other tables to make sure there are no references to the records you're
updating. If there's no index it has to do a sequential scan.
To get a deadlock I think you would need another update running somewhere
though.
--
greg
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsstark@mit.edu, brendan@clickspace.com
Subject: Re: App very unresponsive while performing simple update
In-Reply-To: <874pzanvk6.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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