Thread: 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

1. I'll upgrade asap.
2. Yeah, I pretty sure it's a deadlock or as Tom says a connection that is
not getting cleaned up
3. This is the hard part, I can't replicate. I could run update x times in a
row using the same key and it will work, but on eventually I'll get shut
out. At the moment, I'm the only one hitting this table/db.

Thanks for you feedback.


----- Original Message -----
From: "Joseph Shraibman" <jks@selectacast.net>
To: "Gordon Campbell" <gordie@columbia.edu>
Sent: Tuesday, August 14, 2001 5:35 PM
Subject: Re: [GENERAL] DeadLocks


> I have had some deadlocks, but haven't been able to reproduce them
reliably.
>
> 1) Use the lastest version. 7.0.3 is old and the maintainers won't want
> to debug it
> 2) Are you *sure* it is deadlocking?  is the system just really slow?
> 3) Can you make a test case that always locks?
>
> Gordon Campbell wrote:
> > This is my first posting to this site, but it's my last resort. We're
> > running 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 consistent basis. Often and unpredictably, different
> > programs performing different functions (add, delete, update) will
> > essentially hang. I will view the process (ps -ef) and postgres will be
> > in an (commit, delete or update) state but does not finish. All others
> > wishing to perform a task either via the browser or at command line
> > (psql) will hang. The only way I can free up the process is having our
> > sysadmin "kill" the process.
> >
> >
> >
> > The ten tables contain less than 400 rows. We are in test mode and will
> > go into production after this "show stopper" is resolved.
> >
> >
> >
> > I have run "vacuum" which also hangs on one table that contain 382 rows.
> > I am the sole tester, so I am not clashing with other persons. The
> > servlets each make their own connection using the postgres' jdbc driver.
> > The connections are closed at the end of the program. These programs are
> > very simple insert, update, select statements.
> >
> >
> >
> > Has anybody else had this problem? What debugging methods/tools/logs
> > will help.
> >
> >
> >
> > This is a very small development shop, so I'm throwing this out to a
> > larger community for the first time.
> >
> >
> >
> > Thanks,
> >
> > Gordon Campbell
> >
> > gordie@columbia.edu <mailto:gordie@columbia.edu>
> >
> > Educational Technologist
> >
> > 212-854-1869
> >
>
>
> --
> Joseph Shraibman
> jks@selectacast.net
> Increase signal to noise ratio.  http://www.targabot.com
>

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
>