Re: how to release a transaction lock on a table?

On Tue, Feb 01, 2005 at 10:53:11AM -0800, Si Chen wrote:

> I would like to track down what in the application is causing the
> deadlock,

Are you sure you understand what "deadlock" means?  Deadlock occurs,
for example, when connection A holds a lock that connection B wants
and connection B holds a lock that connection A wants.  PostgreSQL
should recognize that situation and cause one of the connections
to fail after a timeout (one second by default).  That doesn't sound
like what you're experiencing -- based on what you've said, one
connection holds a lock and another is blocked waiting for it.

> but it's a bit hard since it's a big app with lots going on.
> I can track down the PID of the transaction which is locking the tables,
> but is there anyway to go from the PID back to the SQL statement(s) in
> the transaction?

The query "SELECT * FROM pg_stat_activity" should show connections'
current queries if you have stats_command_string set to "on".  If
stats_command_string is "off" then you can enable it by editing
postgresql.conf and restarting the postmaster, but unfortunately
that won't help you track down queries that are already running.

Is it possible that the transaction holding the lock is idle?  Some
applications use long-lived transactions that can cause locking
problems in other transactions.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/