Re: DeadLocks

"Gordon Campbell" <gordie(at)columbia(dot)edu> writes:
> This is my first posting to this site, but it's my last resort. We're runni=
> ng 7.0.3. I have 20 servlets that make use of our postgres database. All of=
> the programs are able to perform their assigned SQL tasks, but not on a co=
> nsistent basis. Often and unpredictably, different programs performing diff=
> erent functions (add, delete, update) will essentially hang.

Hmm. There are some known bugs in 7.0.* that can allow deadlock
situations to go undetected, but they are not easy to trigger (the
example cases that I know of require at least four processes trying
to get overlapping sets of locks). I would strongly recommend an
update to 7.1.2 or later, but that's only on general principles;
I think it's unlikely that your problem is really due to those bugs.

What seems more likely to me is that you have a client-side problem,
specifically a servlet that fails to close out its transaction and
thus continues to hold locks that are needed for other transactions
to proceed. This would definitely explain a VACUUM hang: VACUUM wants
exclusive lock on the table and cannot proceed until all existing
transactions that have touched the table are committed or rolled back.
Simple inserts/updates/deletes don't normally block each other, so hangs
of other servlets aren't so easy to explain that way. But if you have
multiple servlets that might try to update the same row of a table, then
failure to close a transaction would leave other would-be updaters of
the same row waiting. If you make use of foreign key constraints, then
the SELECT FOR UPDATE locking that's used by our present foreign-key
implementation could cause this sort of blocking too, even though you
didn't think you were doing an update.

regards, tom lane