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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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 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
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:
Show quoted text
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
On Wed, Jan 29, 2014 at 3:09 PM, Neil Harkins <nharkins@gmail.com> wrote:
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.
Not all locks are on tables. Each transaction holds an Exclusive lock on
its own virtual transaction id, and that is what you are seeing there.
Looking the pg_locks table without looking at the nature of the locks is
generally not very useful.
Cheers,
Jeff