Thread: Foreign Key Deadlocking

> Can someone confirm that I've identified the right fix?

I'm pretty sure that won't help you... see:

http://archives.postgresql.org/pgsql-general/2006-12/msg00029.php

The deadlock will be there if you update/insert the child table and
update/insert the parent table in the same transaction (even if you
update some other field on the parent table than the key referenced by
the child table). If your transactions always update/insert only one of
those tables, it won't deadlock (assuming you order the inserts/updates
properly per PK).

Cheers,
Csaba.

"Steven Flatt" <steven.flatt@gmail.com> writes:
> Hi, we're using Postgres 8.1.4.
> We've been seeing deadlock errors of this form, sometimes as often as
> several times per hour:
> ...
> I also see claims that this problem is fixed in 8.2, and if the fix is what
> I think it is, it's also in 8.1.6.
> Can someone confirm that I've identified the right fix?

Hard to say without a lot more data, but as a general rule you should be
on 8.1.latest in any case.  See
http://www.postgresql.org/support/versioning
You might also find ammunition in the release notes:
http://developer.postgresql.org/pgdocs/postgres/release.html

            regards, tom lane

Hi Csaba,

I have a similar problem.

In an attempt to avoid the overhead of select count(*) from mailbox
where uid = somuid I've implemented triggers on insert and delete.

So there is a

user table which refers to to an inbox table,

so when people insert into the inbox there is an RI trigger grabbing
the shared lock, then the count triggers try to grab an exclusive
lock resulting in a deadlock.

Can we safely remove the shared locks ?

Is there a right way to implement the count triggers. I've tried
before triggers, and after triggers, both result in different kinds
of deadlocks.

Dave
On 18-Apr-07, at 11:36 AM, Csaba Nagy wrote:

>> Can someone confirm that I've identified the right fix?
>
> I'm pretty sure that won't help you... see:
>
> http://archives.postgresql.org/pgsql-general/2006-12/msg00029.php
>
> The deadlock will be there if you update/insert the child table and
> update/insert the parent table in the same transaction (even if you
> update some other field on the parent table than the key referenced by
> the child table). If your transactions always update/insert only
> one of
> those tables, it won't deadlock (assuming you order the inserts/
> updates
> properly per PK).
>
> Cheers,
> Csaba.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

On Apr 19, 2007, at 9:00 AM, Dave Cramer wrote:
>
> On 18-Apr-07, at 11:36 AM, Csaba Nagy wrote:
>
>>> Can someone confirm that I've identified the right fix?
>>
>> I'm pretty sure that won't help you... see:
>>
>> http://archives.postgresql.org/pgsql-general/2006-12/msg00029.php
>>
>> The deadlock will be there if you update/insert the child table and
>> update/insert the parent table in the same transaction (even if you
>> update some other field on the parent table than the key
>> referenced by
>> the child table). If your transactions always update/insert only
>> one of
>> those tables, it won't deadlock (assuming you order the inserts/
>> updates
>> properly per PK).
>>
>> Cheers,
>> Csaba.

> Hi Csaba,
>
> I have a similar problem.
>
> In an attempt to avoid the overhead of select count(*) from mailbox
> where uid = somuid I've implemented triggers on insert and delete.
>
> So there is a
>
> user table which refers to to an inbox table,
>
> so when people insert into the inbox there is an RI trigger
> grabbing the shared lock, then the count triggers try to grab an
> exclusive lock resulting in a deadlock.
>
> Can we safely remove the shared locks ?
>
> Is there a right way to implement the count triggers. I've tried
> before triggers, and after triggers, both result in different kinds
> of deadlocks.
>
> Dave

The ways I've done this in the past is to have the count triggers
make inserts into some interim table rather than try to update the
actual count field and have another process that continually sweeps
what's in the interim table and makes aggregated updates to the count
table.  Even if there isn't much to aggregate on any given sweep,
this gives you a sequential pattern as your inserts/deletes on the
main table don't depend on any locking in another table (well,
strictly speaking, your inserts into the interim table would be
blocked by any exclusive locks on it but you shouldn't need to ever
do that anyway).


erik jones <erik@myemma.com>
software developer
615-296-0838
emma(r)

Dave Cramer escribió:
> Hi Csaba,
>
> I have a similar problem.
>
> In an attempt to avoid the overhead of select count(*) from mailbox
> where uid = somuid I've implemented triggers on insert and delete.
>
> So there is a
>
> user table which refers to to an inbox table,
>
> so when people insert into the inbox there is an RI trigger grabbing
> the shared lock, then the count triggers try to grab an exclusive
> lock resulting in a deadlock.
>
> Can we safely remove the shared locks ?
>
> Is there a right way to implement the count triggers. I've tried
> before triggers, and after triggers, both result in different kinds
> of deadlocks.

Would it be possible for the triggers to lock the records, before
starting the actual operation, in well known orders, to avoid the
deadlocks?

A frequently mentioned approach to avoid the point of contention is to
have a "totals" record and have the triggers insert "deltas" records; to
get the sum, add them all.  Periodically, take the deltas and apply them
to the totals.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

> A frequently mentioned approach to avoid the point of contention is to
> have a "totals" record and have the triggers insert "deltas" records; to
> get the sum, add them all.  Periodically, take the deltas and apply them
> to the totals.

This is what we do here too. There is only one exception to this rule,
in one case we actually need to have the inserted records and the
updated parent in one transaction for data consistency, in that case the
delta approach won't work... we didn't find any other solution to that
except patching postgres not to lock the parent keys at all, which has
it's own problems too (occasional breakage of the foreign key
relationship when the parent is deleted and a child still slips in, but
this is very rare in our case not to cause problems which cannot be
cleaned up with relative ease - not to mention that there could be other
problems we didn't discover yet or our usage patterns are avoiding).

Cheers,
Csaba.