By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,934 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,934 IT Pros & Developers. It's quick & easy.

Process locked in INSERT: how to debug?

P: n/a
Hello,

What is the best method to debug a database that *sometimes* gets locked
in the INSERT mode? ('ps -afx | grep postgres' shows me a process that
is freezed in INSERT mode.)

Thanks

Philippe

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hi Philippe,

I can't answer your actual question, but just a hint: postgres is
deadlock-prone on inserts into tables referencing other tables via
foreign keys. This is a known limitation of postgres, it places an
exclusive lock on the referenced rows. For details search the archives
for "foreign key deadlock", there's plenty of discussions about it.
So my personal suspect nr. 1. would be the foreign keys.
If that's the problem, you can solve it in a number of ways:
- serialize your inserts, as the problem appears only for concurrent
inserts - this is probably the least viable option;
- make sure you order your inserts so that they insert the rows in the
same order of referenced parent rows (damn hard to do if you have many
foreign keys);
- be prepared to retry each transaction involving the inserts (this is
actually the best way to do it with postgres, at least in pre-8.0
versions, as it also solves the problem of "one failed SQL rolls back
the whole transaction");
- if you use 8.0 then you could use the new save point feature to retry
just the last failed insert, but I have no idea if that's not bundled
with some performance penalties (actually I didn't try at all 8.0, so I
just suppose it would work);
- use a patched postgres version which does not have the foreign key
deadlock problem, but it also do not have 100% referential integrity
guarrantied for foreign key constraints. I think you can find the patch
somewhere referenced in a past mail;

HTH,
Csaba.
On Mon, 2004-08-30 at 14:26, Philippe Lang wrote:
Hello,

What is the best method to debug a database that *sometimes* gets locked
in the INSERT mode? ('ps -afx | grep postgres' shows me a process that
is freezed in INSERT mode.)

Thanks

Philippe

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.