Re: How did I get 8 Exclusive locks on the same table? And how many locks is too many?

On Wed, 2011-06-29 at 16:39 -0400, Bill Moran wrote:
> In response to Aleksey Tsalolikhin <atsaloli.tech@gmail.com>:
> >
> >   We use the fine Bucardo check_postgres Nagios plugin,
> > and it reported a "CRITICAL" level spike in database locks
> > (171 locks).
> >
> > I looked closely at my database logs and found an even bigger spike
> > just a few minutes earlier (208 locks).
> >
> > I saw 8 EXCLUSIVE locks on table X.   All of these queries completed
> > within 5 seconds.  (I know that because they do not appear in my
> > slow query log.)
>
> What manner did you use to determine this?  It's not possible to have
> multiple table-level EXCLUSIVE locks on a single table, since a single
> table level EXCLUSIVE lock will cause other lock attempts to block.
> However, it's possible to have multiple row-level EXCLUSIVE locks, 1
> per row.  Did the mysterious magic that you used to determine that
> there were 8 EXCLUSIVE locks tell you whether they were table level
> or row level?  If they're table level, then something is wrong either
> with PostgreSQL or your magic.  If they're row level, then that's
> hardly unusual.
>

You can have multiple EXCLUSIVE LOCKs on the same table, but only one
will be granted. See granted column in pg_locks catalog table.


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com