Thread: deadlocks in multiple-triggers environment

hi
i have a stituation a situation where i have multiple tables, and multiple triggers on all of them.
at least 1 or 2 triggers on at lease 4 different tables does updates to main cache table.

now.
i have tasks which involve simultaneously (from different machines even) modifying all of the "source" tables.
and i get some deadlocks.
what is the best way to fight deadlocks?
how to find exactly what happened deadlock - which command, which trigger, which function?
how to avoid them (deadlocks).
i can't lock all tables for update, because they happen constantly.

any clues?
pointers? urls?

depesz

On Wed, Jun 08, 2005 at 05:45:45PM +0200, hubert depesz lubaczewski wrote:
> hi
> i have a stituation a situation where i have multiple tables, and multiple
> triggers on all of them.
> at least 1 or 2 triggers on at lease 4 different tables does updates to main
> cache table.

Do say, are there foreign keys on those tables?

If there are, that may explain the deadlocks.  This is a known problem,
fixed in the development version, for which there is no complete
solution on current releases (except for dropping the foreign keys
completely.)  One workaround that may reduce the ocurrence of deadlocks
is to make the constraints INITIALLY DEFERRED, so that the checks happen
as late as possible in the transaction.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
Thou shalt check the array bounds of all strings (indeed, all arrays), for
surely where thou typest "foo" someone someday shall type
"supercalifragilisticexpialidocious" (5th Commandment for C programmers)

[snip]
> Do say, are there foreign keys on those tables?
>
> If there are, that may explain the deadlocks.  This is a known problem,
> fixed in the development version, for which there is no complete
  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Wow, that's a good news :-)
Time to drop that nasty patch we're using...
I'm not on the developers list, I guess it was discussed there, I'm
curios what kind of solution it is ? A special foreign key thing or row
level shared locks were implemented ?

[snip]

Cheers,
Csaba.

On Thu, Jun 09, 2005 at 04:26:44PM +0200, Csaba Nagy wrote:
> [snip]
> > Do say, are there foreign keys on those tables?
> >
> > If there are, that may explain the deadlocks.  This is a known problem,
> > fixed in the development version, for which there is no complete
>   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> Wow, that's a good news :-)
> Time to drop that nasty patch we're using...
> I'm not on the developers list, I guess it was discussed there, I'm
> curios what kind of solution it is ? A special foreign key thing or row
> level shared locks were implemented ?

Shared row locks.  It'd be nice if you could give them a try to get some
feedback ... (we are a couple of months from beta though)

The development group is always looking for ways to annoy users by
forcing them to upgrade to newer versions ;-)

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
Bob [Floyd] used to say that he was planning to get a Ph.D. by the "green
stamp method," namely by saving envelopes addressed to him as 'Dr. Floyd'.
After collecting 500 such letters, he mused, a university somewhere in
Arizona would probably grant him a degree.              (Don Knuth)