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