Thread: More Deadlock Detection on Insert
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
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
On 3/10/04 10:03 AM, "Csaba Nagy" <nagy@ecircle-ag.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
[snip] > 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? > That's correct. > 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? > That's correct too. However, if you break your import (I assume it's a data import) in smaller chunks of transactions, say 100 inerts per transaction, and perhaps pre-ordering the data so each chunk refers the same parent foreign key, that could make a better concurrency. > I'd prefer to avoid one-off patches, as in a new installation that is likely > to be overlooked. > That' correct too. I have applied a patch myself for this FK issue (which I won't publish BTW cause it's tailored to our application's needs), and it is an extra effort to apply it to new versions when upgrading. I'm just doing it for 7.4.2, nothing hard, but not trivial either. I just hope that at one time I can just drop this patch cause the issue will be fixed properly in the postgres sources... :-) Cheers, Csaba.