Обсуждение: How did I get 8 Exclusive locks on the same table? And how many locks is too many?

Hi,

  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.)

The fine Postgres manual states:

EXCLUSIVE lock:
  Only reads from the table can proceed in parallel with a transaction
holding this lock mode.
  This lock mode is not automatically acquired on user tables by any
PostgreSQL command.

My questions:

1. How I could have had eight queries all with EXCLUSIVE locks on
the same table?  Wouldn't have Postgres only allowed one EXCLUSIVE
lock at a time?

2. Since there was no sign of any trouble in the application itself
(everything appeared to work fine), I'm wondering whether we need to
increase our threshold for critical for number of locks, and what factors
are involved in deciding what to set it to (double it? 10x it?)  What
value to use...

We recently upgraded our server and it is pretty beefy compared
to the workload being thrown at it.  (For example, we can do 800
Postgres transactions per second without breaking a sweat on
the disk subsystem, which records about 150,000 write IOPS,
showing 3% utilization on the disk subsystem.)

Best,
-at

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.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

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