From: | "Guy Rouillier" <guyr(at)masergy(dot)com> |
---|---|
To: | "PostgreSQL General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Debugging deadlocks |
Date: | 2005-03-28 19:43:03 |
Message-ID: | CC1CF380F4D70844B01D45982E671B2348E75D@mtxexch01.add0.masergy.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Yudie Gunawan | 2005-03-28 19:50:06 | Re: Table partition for very large table |
Previous Message | Mike Rylander | 2005-03-28 19:40:20 | Re: general purpose full text indexing |