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(dot)tech(at)gmail(dot)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