Re: ExclusiveLock without a relation in pg_locks

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Carlos Oliva <carlos(at)pbsinet(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: ExclusiveLock without a relation in pg_locks
Date: 2006-02-23 15:04:00
Message-ID: 20060223150400.GH28530@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thu, Feb 23, 2006 at 08:54:36AM -0500, Carlos Oliva wrote:
> Would connections to a database require crating an extra ExclusiveLock? We
> have some connections to the database that happen to be "idle in
> transaction" and their pids have a granted "Exclusive Lock" in pg_locks. I
> cannot discern the tables where the ExclusiveLock is being held because the
> relation field is blank.

AIUI each backend has an exclusive lock on its own transaction. If
you're idle in transaction you've acquired a lock on your transaction
so other people can wait on you if necessary. That's why there's
nothing in the relation field, because it's not a table lock.

> Moreover, there are other connections to the database coming from the same
> ip address as that of the connection with the ExclusiveLock. Some of the
> pids of these other connections seem to have different kinds of locks
> (AccessShareL0ck) so I am not quite sure why the pids with the
> ExclusiveLock's are necessary.

AccessShareLock is the normal lock you acquire when selecting data.
It's doesn't really do much other than say "I'm using this table, don't
delete it". See the documentation for all the details.

> How could I find out the tables that are being locked when I see an
> "ExclusiveLock" in pg_locks.

It's rare to see exclusive locks on tables except for things like
VACUUM FULL and CLUSTER and other such admin commands...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Fuhr 2006-02-23 15:05:29 Re: ExclusiveLock without a relation in pg_locks
Previous Message Chad 2006-02-23 14:59:28 Re: How do I use the backend APIs