Re: More Deadlock Detection on Insert

On 3/10/04 10:03 AM, "Csaba Nagy" <nagy(at)ecircle-ag(dot)com> wrote:

> It is the foreign key. Checking foreign keys in postgres is implemented
> by locking the corresponding row in the parent table. So if you have 2
> transactions inserting rows which reference the same keys in the parent
> table in reverse order, you get a deadlock.
> This lock is admittedly too strong and not appropriate for a foreign key
> check, but postgres lacks the proper lock type to do it.
>
> I think there was a patch for disable this locking and accept a certain
> risk of data corruption - look in the archives. Might suite your needs
> if you can make sure your application can accept that risk (or does not
> generate the risky cases in the first place).
> Or you can order your inserts, but that won't help if you have multiple
> and complex foreign key relations, and is bound to be broken when you
> change schema.

Ugh. That's ugly. All I need to do is verify at insert time that the child
record exists (database enforced as opposed to code enforced).

If I understand you right, if I were to insert the records ordered by the
child foreign key (since the parent is unique between runs), this would
eliminate the deadlock. I'm assuming the lock is retained until the
transaction is complete?

Since all 10,000 records are a single transaction (if one fails, all must
fail), and it is almost certain that two loads will have common child
records, it sounds like even with ordered records I have almost no
concurrency. Once a collision occurred, process 2 would wait on process 1
to complete. I might as well just grab an exclusive lock on the table when
loading it?

I'd prefer to avoid one-off patches, as in a new installation that is likely
to be overlooked.

Wes