Обсуждение: concurrent SELECT blocking ALTER?

Hi all, I'm curious if anyone can explain or suggest some debugging to
explain some odd locking behavior I am able to reproduce on pg 9.2.1

I start a test program with 20 threads (autocommit=1), all
executing the same SELECT query with a LEFT OUTER JOIN,
1~2 per second. Then I execute an ALTER to drop a column
(one not referenced by the query, neither explicitly or by a *)
from the table referenced in the OUTER JOIN.

The ALTER blocks for many minutes:

2014-01-28 02:39:48.781
GMT,"postgres","pbs_production",49521,"[local]",52e713cb.c171,7,"ALTER
TABLE waiting",2014-01-28 02:19:55
GMT,18/59,241951078,LOG,00000,"process 49521 acquired
AccessExclusiveLock on relation 16637 of database 16409 after
932916.917 ms",,,,,,"alter table refunds drop column
external_refund_id;",,,"psql"

When I stop the test program doing the SELECTs,
the ALTER gets unblocked and completes very quickly
(the table contains 20k rows):

2014-01-28 02:39:48.803
GMT,"postgres","pbs_production",49521,"[local]",52e713cb.c171,8,"ALTER
TABLE",2014-01-28 02:19:55 GMT,18/0,0,LOG,00000,"duration: 932939.482
ms  statement: alter table refunds drop column
external_refund_id;",,,,,,,,,"psql"

When the test program is running, I see:

# SELECT mode, COUNT(*) FROM pg_locks
WHERE pid != pg_backend_pid() GROUP BY 1;
      mode       | count
-----------------+-------
 ExclusiveLock   |    20
 AccessShareLock |   440
(2 rows)

Why are those exclusive locks present?
Can't the database rely on mvcc for those reads
without locking? The autocommit should be
increasing the xid used for the reads, so the
ALTER should be able to slip in-between?

thanks in advance for any input,
-neil

On 1/29/14, 4:59 PM, Neil Harkins wrote:
> Why are those exclusive locks present?
> Can't the database rely on mvcc for those reads
> without locking? The autocommit should be
> increasing the xid used for the reads, so the
> ALTER should be able to slip in-between?

One would think so, but it's more complicated.  There is a long thread
on pgsql-hackers spreading over many months that discusses the
intricacies of reducing the strength of the locks taken by DDL commands.
 This is being addressed, but at the moment most DDL commands take
exclusive locks.

I totally understand DDL taking exclusive locks, the problem here seems to be that the *SELECTs* are taking out exclusive locks, locking out the ALTER, which feels like a bug.

On Wednesday, January 29, 2014, Peter Eisentraut <peter_e@gmx.net> wrote:

On 1/29/14, 4:59 PM, Neil Harkins wrote:
> Why are those exclusive locks present?
> Can't the database rely on mvcc for those reads
> without locking? The autocommit should be
> increasing the xid used for the reads, so the
> ALTER should be able to slip in-between?

One would think so, but it's more complicated.  There is a long thread
on pgsql-hackers spreading over many months that discusses the
intricacies of reducing the strength of the locks taken by DDL commands.
 This is being addressed, but at the moment most DDL commands take
exclusive locks.

Neil Harkins wrote on 29.01.2014 23:37:
> I totally understand DDL taking exclusive locks, the problem here seems to be that the *SELECTs*
>are taking out exclusive locks, locking out the ALTER, which feels like a bug.

The SELECT is not holding an exclusive lock, it's holing a *shared* lock, but the ALTER is _requesting_ an exclusive
lockand that can only be granted until all shared (or otherwise incompatible) locks are released. 

Note the number of exclusive locks in my first message, it is equal to the number of threads (20). Also, the ALTER was not running then, apologies if that was not clear.

On Wednesday, January 29, 2014, Thomas Kellerer <spam_eater@gmx.net> wrote:

Neil Harkins wrote on 29.01.2014 23:37:

I totally understand DDL taking exclusive locks, the problem here seems to be that the *SELECTs*
are taking out exclusive locks, locking out the ALTER, which feels like a bug.

The SELECT is not holding an exclusive lock, it's holing a *shared* lock, but the ALTER is _requesting_ an exclusive lock and that can only be granted until all shared (or otherwise incompatible) locks are released.





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general