Thread: 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 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:
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
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