Обсуждение: Waiting for select
Can someone explain this? I seem to have a query which is being blocked by a lock. I was under the impression that selects are never blocked. Am I missing something or is this bad behaviour? I am using slony and am synchronising a slave for the first time. In the hope of seeing some progress on the slave I attempt to perform a select count(*) on one of the tables. The select just stops. ps shows this: postgres 5987 0.0 0.4 19180 4188 ? S 15:16 0:00 postgres: postgres testdb 192.168.1.111(33598) SELECT waiting A query of blocking locks shows this: object | trans | pid | mode | blocker ---------------------------+-------+------+---------------------+--------- testdb.campaign_cost_pk | | 5754 | AccessShareLock | testdb.csn_log_pk | | 5754 | RowExclusiveLock | testdb.pg_trigger | | 5754 | AccessShareLock | testdb.pg_trigger | | 5754 | RowExclusiveLock | testdb.sl_subscribe | | 5754 | AccessShareLock | testdb.csn_log_idx2 | | 5754 | RowExclusiveLock | testdb.campaign | | 5754 | AccessShareLock | testdb.campaign | | 5754 | RowExclusiveLock | testdb.campaign | | 5754 | AccessExclusiveLock | testdb.pg_rewrite | | 5754 | AccessShareLock | testdb.pg_rewrite | | 5754 | RowExclusiveLock | testdb.computer_sn_log | | 5754 | AccessShareLock | testdb.computer_sn_log | | 5754 | RowExclusiveLock | testdb.computer_sn_log | | 5754 | AccessExclusiveLock | testdb.address_type_pk | | 5754 | AccessShareLock | testdb.campaign_pk | | 5754 | AccessShareLock | testdb.sl_log_1 | | 5754 | AccessShareLock | testdb.sl_log_1 | | 5754 | RowExclusiveLock | testdb.csn_cookie_idx1 | | 5754 | AccessShareLock | testdb.pg_index | | 5754 | AccessShareLock | testdb.pg_index | | 5754 | RowShareLock | testdb.csn_log_idx3 | | 5754 | RowExclusiveLock | testdb.csn_cookie_pk | | 5754 | AccessShareLock | testdb.sl_log_2 | | 5754 | AccessShareLock | testdb.sl_log_2 | | 5754 | RowExclusiveLock | testdb.sl_set | | 5754 | AccessShareLock | testdb.sl_set | | 5754 | RowShareLock | testdb.campaign_cost | | 5754 | AccessShareLock | testdb.campaign_cost | | 5754 | RowExclusiveLock | testdb.campaign_cost | | 5754 | AccessExclusiveLock | testdb.sl_table | | 5754 | AccessShareLock | testdb.sl_table | | 5754 | RowShareLock | testdb.sl_table | | 5754 | RowExclusiveLock | | 9182 | 5754 | ExclusiveLock | testdb.computer_sn_cookie | | 5754 | AccessShareLock | testdb.computer_sn_cookie | | 5754 | RowExclusiveLock | testdb.computer_sn_cookie | | 5754 | AccessExclusiveLock | testdb.pg_attribute | | 5754 | AccessShareLock | testdb.sl_config_lock | | 5754 | AccessExclusiveLock | testdb.sl_trigger | | 5754 | AccessShareLock | testdb.csn_pk | | 5754 | AccessShareLock | testdb.pg_class | | 5754 | AccessShareLock | testdb.pg_class | | 5754 | RowShareLock | testdb.pg_class | | 5754 | RowExclusiveLock | testdb.address_type | | 5754 | AccessShareLock | testdb.address_type | | 5754 | RowExclusiveLock | testdb.address_type | | 5754 | AccessExclusiveLock | testdb.pg_namespace | | 5754 | AccessShareLock | testdb.pg_namespace | | 5754 | RowShareLock | testdb.csn_log_idx1 | | 5754 | RowExclusiveLock | testdb.computer_sn | | 5754 | AccessShareLock | testdb.computer_sn | | 5754 | RowExclusiveLock | testdb.computer_sn | | 5754 | AccessExclusiveLock | testdb.address_type | | 5987 | AccessShareLock | 5754 | 9422 | 5987 | ExclusiveLock | 5754 (55 rows) All responses will be welcomed. __ Marc
Вложения
Marc Munro wrote: > Can someone explain this? I seem to have a query which is being blocked > by a lock. I was under the impression that selects are never blocked. > Am I missing something or is this bad behaviour? Do you happen to be running a vacuum full? > > I am using slony and am synchronising a slave for the first time. In > the hope of seeing some progress on the slave I attempt to perform a > select count(*) on one of the tables. > > The select just stops. ps shows this: > > postgres 5987 0.0 0.4 19180 4188 ? S 15:16 0:00 postgres: postgres testdb 192.168.1.111(33598) SELECT waiting > > A query of blocking locks shows this: > > object | trans | pid | mode | blocker > ---------------------------+-------+------+---------------------+--------- > testdb.campaign_cost_pk | | 5754 | AccessShareLock | > testdb.csn_log_pk | | 5754 | RowExclusiveLock | > testdb.pg_trigger | | 5754 | AccessShareLock | > testdb.pg_trigger | | 5754 | RowExclusiveLock | > testdb.sl_subscribe | | 5754 | AccessShareLock | > testdb.csn_log_idx2 | | 5754 | RowExclusiveLock | > testdb.campaign | | 5754 | AccessShareLock | > testdb.campaign | | 5754 | RowExclusiveLock | > testdb.campaign | | 5754 | AccessExclusiveLock | > testdb.pg_rewrite | | 5754 | AccessShareLock | > testdb.pg_rewrite | | 5754 | RowExclusiveLock | > testdb.computer_sn_log | | 5754 | AccessShareLock | > testdb.computer_sn_log | | 5754 | RowExclusiveLock | > testdb.computer_sn_log | | 5754 | AccessExclusiveLock | > testdb.address_type_pk | | 5754 | AccessShareLock | > testdb.campaign_pk | | 5754 | AccessShareLock | > testdb.sl_log_1 | | 5754 | AccessShareLock | > testdb.sl_log_1 | | 5754 | RowExclusiveLock | > testdb.csn_cookie_idx1 | | 5754 | AccessShareLock | > testdb.pg_index | | 5754 | AccessShareLock | > testdb.pg_index | | 5754 | RowShareLock | > testdb.csn_log_idx3 | | 5754 | RowExclusiveLock | > testdb.csn_cookie_pk | | 5754 | AccessShareLock | > testdb.sl_log_2 | | 5754 | AccessShareLock | > testdb.sl_log_2 | | 5754 | RowExclusiveLock | > testdb.sl_set | | 5754 | AccessShareLock | > testdb.sl_set | | 5754 | RowShareLock | > testdb.campaign_cost | | 5754 | AccessShareLock | > testdb.campaign_cost | | 5754 | RowExclusiveLock | > testdb.campaign_cost | | 5754 | AccessExclusiveLock | > testdb.sl_table | | 5754 | AccessShareLock | > testdb.sl_table | | 5754 | RowShareLock | > testdb.sl_table | | 5754 | RowExclusiveLock | > | 9182 | 5754 | ExclusiveLock | > testdb.computer_sn_cookie | | 5754 | AccessShareLock | > testdb.computer_sn_cookie | | 5754 | RowExclusiveLock | > testdb.computer_sn_cookie | | 5754 | AccessExclusiveLock | > testdb.pg_attribute | | 5754 | AccessShareLock | > testdb.sl_config_lock | | 5754 | AccessExclusiveLock | > testdb.sl_trigger | | 5754 | AccessShareLock | > testdb.csn_pk | | 5754 | AccessShareLock | > testdb.pg_class | | 5754 | AccessShareLock | > testdb.pg_class | | 5754 | RowShareLock | > testdb.pg_class | | 5754 | RowExclusiveLock | > testdb.address_type | | 5754 | AccessShareLock | > testdb.address_type | | 5754 | RowExclusiveLock | > testdb.address_type | | 5754 | AccessExclusiveLock | > testdb.pg_namespace | | 5754 | AccessShareLock | > testdb.pg_namespace | | 5754 | RowShareLock | > testdb.csn_log_idx1 | | 5754 | RowExclusiveLock | > testdb.computer_sn | | 5754 | AccessShareLock | > testdb.computer_sn | | 5754 | RowExclusiveLock | > testdb.computer_sn | | 5754 | AccessExclusiveLock | > testdb.address_type | | 5987 | AccessShareLock | 5754 > | 9422 | 5987 | ExclusiveLock | 5754 > (55 rows) > > All responses will be welcomed. > > __ > Marc -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org
Marc Munro <marc@bloodnok.com> writes: > Can someone explain this? I seem to have a query which is being blocked > by a lock. I was under the impression that selects are never blocked. AccessExclusiveLock blocks anything. > A query of blocking locks shows this: > object | trans | pid | mode | blocker > ---------------------------+-------+------+---------------------+--------- > testdb.address_type | | 5754 | AccessExclusiveLock | > testdb.address_type | | 5987 | AccessShareLock | 5754 So what's process 5754 doing? regards, tom lane
It's doing something in slony. Part of the initial sync operation I guess. I guess it must be doing an alter table or reindex or something. I don't understand why though. I'll repeat my question on the slony mailing list. Thanks for the response. __ Marc On Fri, 2005-06-10 at 18:48 -0400, Tom Lane wrote: > Marc Munro <marc@bloodnok.com> writes: > > Can someone explain this? I seem to have a query which is being blocked > > by a lock. I was under the impression that selects are never blocked. > > AccessExclusiveLock blocks anything. > > > A query of blocking locks shows this: > > > object | trans | pid | mode | blocker > > ---------------------------+-------+------+---------------------+--------- > > testdb.address_type | | 5754 | AccessExclusiveLock | > > testdb.address_type | | 5987 | AccessShareLock | 5754 > > So what's process 5754 doing? > > regards, tom lane