Re: Deadlock situation?

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