More Deadlock Detection on Insert

From: <wespvp(at)SYNTEGRA(dot)COM>
To: <pgsql-general(at)postgresql(dot)org>
Subject: More Deadlock Detection on Insert
Date: 2004-03-10 15:33:42
Message-ID: BC749176.B582%wespvp@syntegra.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dexter Tad-y 2004-03-10 15:34:52 Re: [NEWBIE] need help optimizing this query
Previous Message Tom Lane 2004-03-10 15:30:33 Re: updates (postgreSQL) very slow