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.
>
>
>