Re: Debugging deadlocks

Alvaro Herrera wrote:
> On Sun, Mar 27, 2005 at 06:02:25PM -0600, Guy Rouillier wrote:
>
>> With the current implementation, it appears I need to either (1)
>> always commit after every inserted row, or (2) single thread my
>> entire insert logic.  Neither of these two alternatives is very
>> desirable.
>
> I think a usual workaround is to declare the contraints INITIALLY
> DEFERRED.  This will delay the check until commit time, so the time
> window to deadlock is smaller.  There still is a possibility though,
> so you need to take it into account.  It occurs to me that if you
> control all insertion threads, you could try to serialize access to
> COMMIT in order to make the chance of deadlock even smaller.

I changed the definition of the two tables with the common foreign key
to use DEFERRABLE INITIALLY DEFERRED.  I'm no longer getting a deadlock,
but I'm getting another error that may just be masking the same problem.
I'd like to understand your caveat about deadlocks still being possible.
To help me understand I'll draw a simplistic timeline.  A and B are
transactions on the two different tables:

A  !--------------------!
B           !------------------!

At the time that A commits, what is the effect on B?  B has not yet
attempted to lock any rows in the ref table (the one with the primary
key referenced by the foreign keys.)  So as I understand it, B should be
unaffected by A's commit.  Correct?  Does the deadlock possibility arise
if A and B should try to commit at the same instant?

--
Guy Rouillier