Brendan Duddridge <brendan@clickspace.com> writes:
> We do have foreign keys on other tables that reference the product table.
> Also, there will be updates going on at the same time as this update. When
> anyone clicks on a product details link, we issue an update statement to
> increment the click_count on the product. e.g. update product set click_count
> = click_count + 1;
You should realize this will produce a lot of garbage records and mean you'll
have to be running vacuum very frequently. You might consider instead of
updating the main table inserting into a separate clickstream table. That
trades off not getting instantaneous live totals with isolating the
maintenance headache in a single place. That table will grow large but you can
prune it at your leisure without impacting query performance on your main
tables.
> There are 1.2 million rows in this table and my update will affect 200,000
> of them.
>
> We do have indexes on all foreign keys that reference the product table.
Well I suppose you had an update running concurrently against one of CATEGORY,
MANUFACTURER, or MEDIA. Do any of those tables have a reference back to the
product table? Is it possible to have a record with a reference back to the
same record that refers to it?
I think you're seeing the problem because these foreign keys are all initially
deferred. That means you can update both tables and then can't commit either
one because it needs to obtain a shared lock on the other record which is
already locked for the update.
I'm not certain that making them not deferred would actually eliminate the
deadlock. It might just make it less likely.
The deferred foreign key checks may also be related to the performance
complaints. In my experience they're quite fast but I wonder what happens when
you do a large batch update and then need to perform a whole slew of deferred
foreign key checks.
More likely you were blocking on some lock. Until that other query holding
that lock tries to commit Postgres won't actually detect a deadlock, it'll
just sit waiting until the lock becomes available.
Also, you have a lot of indexes here. That alone will make updates pretty
slow.
--
greg
