Re: DeadLocks - Mailing list pgsql-general

The auto-commit that I've read about in the documentation....

From what I read, it appears that PG is handling my commits/rollbacks. If I
have db.commit() & db.rollback() in my servlets, would this be for good
measure? What I mean is, does my overkill of commit/rollback from the
servlet have any affect?

I'm also considering writing a connection pool to see if that would help my
problem.

Thanks for these responses.

Sincerely,
Gordie

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Gordon Campbell" <gordie@columbia.edu>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, August 14, 2001 5:33 PM
Subject: Re: [GENERAL] DeadLocks


> "Gordon Campbell" <gordie@columbia.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
>