Hi. Sorry about the confusion in terminology.
You are right. The transactions are idle--when I do a "ps auxw" on my database server, I see "....idle in transaction". Is this what you meant, and would the steps you talked about with pg_stat_activity help me track down the transactions that are idle?
What's strange is that usually this does not cause problems. It is just occasionally that I am unable to gain access to a table. Unfortunately that also makes it hard to track down the source of the problem.
Thanks for all your help so far--really appreciate it.
Si
Michael Fuhr wrote:
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.
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
