From: | Benedict Holland <benedict(dot)m(dot)holland(at)gmail(dot)com> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Update blocking a select count(*)? |
Date: | 2012-06-15 18:46:09 |
Message-ID: | CAD+mzozTqB8YiFQsgxU5mMqVo01wxVE0yiwoMHD_uSfSApKt7Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)Grittner(at)wicourts(dot)gov
> wrote:
> Peter Geoghegan <peter(at)2ndquadrant(dot)com> wrote:
> > Benedict Holland <benedict(dot)m(dot)holland(at)gmail(dot)com> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Benedict Holland | 2012-06-15 18:46:52 | Re: Update blocking a select count(*)? |
Previous Message | Kevin Grittner | 2012-06-15 18:43:30 | Re: Update blocking a select count(*)? |