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