Thread: Clearing locks

Edwin New wrote:
> I have encountered a situation where a java process is dying but leaving
> locks active.

If the connection to PostgreSQL is severed (e.g. the client actually
disconnects), the current transaction will be rolled back and any held
locks will be released.

So it seems that the problem is that when the client dies, it is not
actually disconnecting from PostgreSQL, and is in the midst of a
transaction that has acquired some locks. Perhaps this is due to buggy
connection pooling software that does not rollback a connection's
transaction before putting it back into the connection pool? Without
more information it's tough to be sure.

FYI, you can examine the status of the lock manager via the pg_locks
system view:

http://www.postgresql.org/docs/8.0/static/monitoring-locks.html

> How can I, as DBA, clear a lock / roll back an incomplete transaction
> without access to the connection that created the lock?

Well, you can always kill the backend process -- that will abort its
transaction and release any locks it holds.

-Neil

On Tue, Mar 22, 2005 at 04:19:06PM +1100, Neil Conway wrote:
> Edwin New wrote:
> >I have encountered a situation where a java process is dying but leaving
> >locks active.
>
> So it seems that the problem is that when the client dies, it is not
> actually disconnecting from PostgreSQL, and is in the midst of a
> transaction that has acquired some locks. Perhaps this is due to buggy
> connection pooling software that does not rollback a connection's

It needn't even be a matter of a pool.  I've seen plenty of cases
where the transaction is in mid-execution when the client dies.  The
connection stays open because the transaction is still going on
(think of a very long running UPDATE, for instance).  The transaction
will indeed roll back when the back end attempts to deliver the
results, and finds the client is gone.  But in the mean time, the
locks are maintained.

In any case, the answer probably is still to kill -2 the offending
back end.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
        --Philip Greenspun