Re: Update blocking a select count(*)?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Yes I actually seem to have two of them for the single update. The update I am running will set the value of a single column in the table without a where clause. I actually have two AccessShareLock's, two ExclusiveLock's, and two RowExclusiveLock's. It sort of seems like overkill for what should be a copy the column to make the updates, make updates, and publish updates set of operations. On my select statement I have an ExclusiveLock and an AccessShareLock. I read the documentation on locking but this seems very different from what I should expect.

I am running an update statement without a where clause (so a full table update). This is not an alter table statement (though I am running that too and it is being blocked). I am looking in the SeverStatus section of pgadmin3. There are three queries which are in green (not blocked), two statements which are in red (an alter as expected and a select count(*) which are blocked by an update process).

I can not tell you how many documents I have read for locks, statements which generate locks etc. I accept that this will run slowly, what pgadmin3 is displaying to me is the described behavior.

Thanks,
~Ben



On Fri, Jun 15, 2012 at 2:43 PM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote:
Peter Geoghegan <peter@xxxxxxxxxxxxxxx> wrote:
> Benedict Holland <benedict.m.holland@xxxxxxxxx> wrote:
>> Do I seem to have this right and is there anything I can do?
>
> There are a couple of maintenance operations that could block a
> select. Do you see any AccessExclusive locks within pg_locks?
> That's the only type of lock that will block a select statement's
> AccessShare lock.

To check for that, see the queries on these Wiki pages:

http://wiki.postgresql.org/wiki/Lock_Monitoring
http://wiki.postgresql.org/wiki/Lock_dependency_information

-Kevin


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux