Обсуждение: apparent deadlock
Hello, I'm working with Pg 7.4 on CentOS, and I'm seeing an apparent deadlock which is not resolving itself. According to the postgres documentation, deadlocks should be detected and resolved by killing one or more transactions involved. http://www.postgresql.org/docs/current/static/explicit-locking.html What I'm seeing is an upgrade of an application running a series of alter table commands to migrate the db schema, but blocking during the process and never continuing. I finally had to restart the db to unblock the process. Does anyone have any suggestions for troubleshooting a potential deadlock like this? I'm not sure how to query existing locks on tables, for example, which might tell me where the issue is. Thank you, mps -- Michael P. Soulier <michael_soulier@mitel.com>, 613-592-2122 x2522 "Any intelligent fool can make things bigger and more complex... It takes a touch of genius - and a lot of courage to move in the opposite direction." --Albert Einstein
On Tue, Nov 18, 2008 at 11:43 AM, Michael P. Soulier <michael_soulier@mitel.com> wrote: > Hello, > > I'm working with Pg 7.4 on CentOS, and I'm seeing an apparent deadlock which > is not resolving itself. > > According to the postgres documentation, deadlocks should be detected and > resolved by killing one or more transactions involved. > > http://www.postgresql.org/docs/current/static/explicit-locking.html > > What I'm seeing is an upgrade of an application running a series of alter > table commands to migrate the db schema, but blocking during the process and > never continuing. I finally had to restart the db to unblock the process. Just because you've got a blocking transaction doesn't mean it's a deadlock. A deadlock means you have two transactions, each of which is waiting for the other to continue before it can go on. If one query is holding up another query, but not vice versa, it's not a deadlock, just a lock. > Does anyone have any suggestions for troubleshooting a potential deadlock like > this? I'm not sure how to query existing locks on tables, for example, which > might tell me where the issue is. First place to start is the view pg_locks
On 18/11/08 Scott Marlowe did say: > Just because you've got a blocking transaction doesn't mean it's a > deadlock. A deadlock means you have two transactions, each of which > is waiting for the other to continue before it can go on. If one > query is holding up another query, but not vice versa, it's not a > deadlock, just a lock. Right you are. > First place to start is the view pg_locks I found this: http://fishbowl.pastiche.org/2004/02/26/finding_open_locks_on_postgresql/ it revealed where the locks were consumed. Thanks, Mike -- Michael P. Soulier <michael_soulier@mitel.com>, 613-592-2122 x2522 "Any intelligent fool can make things bigger and more complex... It takes a touch of genius - and a lot of courage to move in the opposite direction." --Albert Einstein
On 18/11/08 Michael P. Soulier did say: > I found this: > > http://fishbowl.pastiche.org/2004/02/26/finding_open_locks_on_postgresql/ > > it revealed where the locks were consumed. Does postgres implicitely lock tables? I noticed that I had an alter table command but I didn't lock, and yet there was a lock being requested on the table by that process... Thanks, Mike -- Michael P. Soulier <michael_soulier@mitel.com>, 613-592-2122 x2522 "Any intelligent fool can make things bigger and more complex... It takes a touch of genius - and a lot of courage to move in the opposite direction." --Albert Einstein
"Michael P. Soulier" <michael_soulier@mitel.com> writes: > Does postgres implicitely lock tables? I noticed that I had an alter > table command but I didn't lock, and yet there was a lock being > requested on the table by that process... http://www.postgresql.org/docs/8.3/static/explicit-locking.html#LOCKING-TABLES regards, tom lane