Re: More Deadlock Detection on Insert - Mailing list pgsql-general

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.

HTH,
Csaba.

On Wed, 2004-03-10 at 16:33, wespvp@syntegra.com wrote:
> I've searched the archives and haven't found anything that matches my
> problem, other than it may have something to do with foreign keys.
>
> If I have two processes loading a particular table at the same time, I may
> get:
>
> ERROR:  deadlock detected
> DETAIL:  Process 12154 waits for ShareLock on transaction 74240; blocked by
> process 12142.
>         Process 12142 waits for ShareLock on transaction 74241; blocked by
> process 12154.
>
>
> The table in question has the following attributes of interest:
>
>   widget_key numeric(12,0)
>   widget_group numeric(10,0)
>   widget_maker numeric(12,0)
>
> The foreign key references are:
>
>     "$1" FOREIGN KEY (widget_group) REFERENCES widget_sessions(widget_group)
> ON DELETE CASCADE
>     "$2" FOREIGN KEY (widget_maker) REFERENCES addresses(widget_maker)
>
> There are some other attributes that are indexed but have no foreign key
> references.
>
> The program is a C program using ecpg.  It loads 10,000 records at a time.
> The load goes something like this:
>
>    while (widgets) {
>
>        [snip]
>
>         EXEC SQL SELECT
>             nextval('widget_key_sequence')
>         INTO
>             :widget_key;
>
>
>         [snip]
>
>         INSERT INTO widgets ...
>
>         [snip]
>
>     }
>
> There are no explicit locks.  The INSERT is just "INSERT INTO ... VALUES
> ..."
>
> When two processes run simultaneously, they will never be referencing the
> same widget_group record, but they could reference the same widget_maker
> record.  I need the widget_maker reference to insure referential integrity.
>
> All indexes are btree.
>
> I can find no logical reason for the deadlock.  Why is this occurring and
> how can I get around it?  Unless it is deadlocking on the widget record
> itself, it would have to be either the sequence or the widget_maker foreign
> key reference.  Neither makes any sense.  The times I've seen it, based on
> the log messages it appears to happen as soon as the second process starts
> to load, and after the 'nextval' (i.e on the INSERT).
>
> Any help would be appreciated.
>
> Wes
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html