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/ ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
