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

Re: Strange deadlock error last night

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Scott Whitney" <swhitney(at)journyx(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Strange deadlock error last night
Date: 2009-01-13 21:04:17
Message-ID: 1307.1231880657@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
"Scott Whitney" <swhitney(at)journyx(dot)com> writes:
> Last night, I got this:

> Jan 13 03:31:28 db01 postgres[23537]: [140-2] DETAIL:  Process 23537 waits
> for AccessShareLock on relation 1260 of database 0; blocked by process
> 22228.
> Jan 13 03:31:28 db01 postgres[23537]: [140-3]   Process 22228 waits for
> AccessShareLock on relation 1262 of database 0; blocked by process 6816.
> Jan 13 03:31:28 db01 postgres[23537]: [140-4]   Process 6816 waits for
> AccessShareLock on relation 1260 of database 0; blocked by process 14624.
> Jan 13 03:31:28 db01 postgres[23537]: [140-5]   Process 14624 waits for
> AccessExclusiveLock on relation 1260 of database 0; blocked by process
> 23537.
> Jan 13 03:31:28 db01 postgres[14624]: [243-1] ERROR:  deadlock detected

Well, the problem is evidently that guy trying to get exclusive lock on
relation 1260, which is pg_authid (a quick way to check that is "select
1260::regclass").  (Note: the "database 0" just means it's a system catalog
that's shared across all databases.)  pg_authid is touched often enough
that trying to ex-lock it in an active database is just asking for
trouble.

Is it possible that that process was trying to run VACUUM FULL or
REINDEX on pg_authid?  I can't think of any other likely-sounding
explanation for something trying to take an exclusive lock on that
catalog.  The best solution would be "don't do that" ...

> It ended up locking up about 250 customer databases until I restarted the
> postmaster. This is version 8.1.4. Upgrading right now (even to a minor rev)
> is not really an option.

Not related to the immediate problem, but: you really need to schedule
5 minutes' downtime so you can update to 8.1.latest.  Reasons can be
found here:
http://www.postgresql.org/docs/8.1/static/release.html

			regards, tom lane

In response to

Responses

pgsql-admin by date

Next:From: tyrrill_edDate: 2009-01-13 21:38:12
Subject: Problem with pg_dump
Previous:From: Scott WhitneyDate: 2009-01-13 17:37:00
Subject: Strange deadlock error last night

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