Skip site navigation (1) Skip section navigation (2)

Re: Deadlock situation?

From: Erik Jones <erik(at)myemma(dot)com>
To: Dan Armbrust <daniel(dot)armbrust(dot)list(at)gmail(dot)com>
Cc: "pgsql general" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Deadlock situation?
Date: 2008-04-29 22:10:31
Message-ID: 5966C489-79A0-4CA9-B500-031E06BD15A8@myemma.com (view raw or flat)
Thread:
Lists: pgsql-general
On Apr 29, 2008, at 4:54 PM, Dan Armbrust wrote:

> I have an app that we were load testing - it maintains a pool of
> connections to PostgreSQL - 8.3.1
>
>
> Suddenly, after running fine for weeks, the app hung - unable to get a
> free connection from the pool.
>
>
> select * from pg_stat_activity;" shows me that most of my connections
> in a COMMIT phase:
>
> 03:05:37.73064-05  | 2008-04-24 03:05:38.419796-05 | 2008-04-24
> 02:11:53.908518-05 | 127.0.0.1   |       53807
> 16385 | ispaaa  |     953 |    16384 | pslogin  | COMMIT       | f
>   | 2008-04-24
>
>
> While some are in a SELECT:
>
> 16385 | ispaaa  |    1181 |    16384 | pslogin  | select
> dynamichos0_.ethernetmacaddr as ethernet1_0_, dynamichos0_.ipaddr as
> ipaddr0_, dynamichos0_.cpemac as cpemac0_, dynamichos0_.regtime as
> regtime0_, dynamichos0_.leasetime as leasetime0_,
> dynamichos0_.last_updated as last5_0_ from iphost dynamichos0_, cpe
> cpe1_ where  dynamichos0_.cpemac=cpe1_.cpemac and 1=1 and
> dynamichos0_.ethernetmacaddr=$1 and dynamichos0_.cpemac=$2 and
> cpe1_.regBaseId=$3 and dynamichos0_.ipaddr<>$4    | f       |
> 2008-04-24 03:05:37.734041-05 | 2008-04-24 03:05:38.405854-05 |
> 2008-04-24 02:41:54.413337-05 | 127.0.0.1   |       55363
>
>
> Perhaps VACUUM had something to do with it?:
>
> 16385 | ispaaa  |    8956 |    16384 | pslogin  | delete from iphost
> where leasetime<$1      | f       | 2008-04-24 18:43:29.920069-05 |
> 2008-04-24 18:43:30.116435-05 | 2008-04-24 18:41:59.071032-05 |
> 127.0.0.1   |
>
>  49069  16385 | ispaaa  |    1618 |       10 | postgres | autovacuum:
> VACUUM public.iphost      | f       | 2008-04-24 03:05:13.212436-05 |
> 2008-04-24 03:05:13.212436-05 | 2008-04-24 03:05:12.526611-05 |
>     |
>
>
> Where should I begin to look for the source of this problem?
>
> Thanks for any info,

Well, you can look in pg_locks to see if there are outstanding locks  
waiting on already granted conflicting locks.  This isn't a deadlock  
situation, though, Postgres will detect those and kill one of the  
offending processes so that the others can finish (it'll leave a log  
message about it, too).  My guess is that you've got some long running  
write/ddl query that's go a heavy lock on iphost or you have a LOT of  
queries that need heavy locks hitting the table at once. How large is  
iphost?  How many of those deletes have you got going on it?  Do you  
also have concurrent updates running against it?  Do you have any ddl  
queries running against it (alter tables, index builds/drops, etc...)?

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




In response to

Responses

pgsql-general by date

Next:From: Dan ArmbrustDate: 2008-04-29 22:30:55
Subject: Re: Deadlock situation?
Previous:From: Dan ArmbrustDate: 2008-04-29 21:54:48
Subject: Deadlock situation?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group