Re: Update blocking a select count(*)?
| От | Benedict Holland |
|---|---|
| Тема | Re: Update blocking a select count(*)? |
| Дата | |
| Msg-id | CAD+mzoykMCtGaAKN9jOyH=nDF1E=EORcs4DSAbbKwvt4jJEXsQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Update blocking a select count(*)? (Benedict Holland <benedict.m.holland@gmail.com>) |
| Ответы |
Re: Update blocking a select count(*)?
Re: Update blocking a select count(*)? |
| Список | pgsql-performance |
Sorry about the raw text but this is what I am seeing:
1736 postgres 6/39 6/39 ExclusiveLock Yes 2012-06-15 13:36:22.997-04 insert into inspections
select * from inspections_1
1736 rmv 49896 postgres 6/39 AccessShareLock Yes 2012-06-15 13:36:22.997-04 insert into inspections
select * from inspections_1
1736 rmv 33081 postgres 6/39 RowExclusiveLock Yes 2012-06-15 13:36:22.997-04 insert into inspections
select * from inspections_1
1736 rmv 33084 postgres 6/39 RowExclusiveLock Yes 2012-06-15 13:36:22.997-04 insert into inspections
select * from inspections_1
2096 postgres 8/151 ExclusiveLock Yes 2012-06-15 10:25:08.329-04 vacuum (analyze, verbose, full)
2096 rmv 33528 postgres 8/151 AccessExclusiveLock Yes 2012-06-15 10:25:08.329-04 vacuum (analyze, verbose, full)
2096 rmv 50267 postgres 8/151 AccessExclusiveLock Yes 2012-06-15 10:25:08.329-04 vacuum (analyze, verbose, full)
2096 postgres 8/151 8/151 ExclusiveLock Yes 2012-06-15 10:25:08.329-04 vacuum (analyze, verbose, full)
2844 postgres 5/27 5/27 ExclusiveLock Yes 2012-06-15 13:50:46.417-04 select count(*) from vins
2844 rmv 33074 postgres 5/27 AccessShareLock No 2012-06-15 13:50:46.417-04 select count(*) from vins
2940 postgres 2/251 2/251 ExclusiveLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
2940 rmv 41681 postgres 2/251 AccessShareLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
2940 postgres 2/251 ExclusiveLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
2940 rmv 41684 postgres 2/251 AccessShareLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
2940 rmv 50265 postgres 2/251 RowExclusiveLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
2940 rmv 33074 postgres 2/251 RowExclusiveLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
2940 rmv 33079 postgres 2/251 RowExclusiveLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
1736 postgres 6/39 6/39 ExclusiveLock Yes 2012-06-15 13:36:22.997-04 insert into inspections
select * from inspections_1
1736 rmv 49896 postgres 6/39 AccessShareLock Yes 2012-06-15 13:36:22.997-04 insert into inspections
select * from inspections_1
1736 rmv 33081 postgres 6/39 RowExclusiveLock Yes 2012-06-15 13:36:22.997-04 insert into inspections
select * from inspections_1
1736 rmv 33084 postgres 6/39 RowExclusiveLock Yes 2012-06-15 13:36:22.997-04 insert into inspections
select * from inspections_1
2096 postgres 8/151 ExclusiveLock Yes 2012-06-15 10:25:08.329-04 vacuum (analyze, verbose, full)
2096 rmv 33528 postgres 8/151 AccessExclusiveLock Yes 2012-06-15 10:25:08.329-04 vacuum (analyze, verbose, full)
2096 rmv 50267 postgres 8/151 AccessExclusiveLock Yes 2012-06-15 10:25:08.329-04 vacuum (analyze, verbose, full)
2096 postgres 8/151 8/151 ExclusiveLock Yes 2012-06-15 10:25:08.329-04 vacuum (analyze, verbose, full)
2844 postgres 5/27 5/27 ExclusiveLock Yes 2012-06-15 13:50:46.417-04 select count(*) from vins
2844 rmv 33074 postgres 5/27 AccessShareLock No 2012-06-15 13:50:46.417-04 select count(*) from vins
2940 postgres 2/251 2/251 ExclusiveLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
2940 rmv 41681 postgres 2/251 AccessShareLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
2940 postgres 2/251 ExclusiveLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
2940 rmv 41684 postgres 2/251 AccessShareLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
2940 rmv 50265 postgres 2/251 RowExclusiveLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
2940 rmv 33074 postgres 2/251 RowExclusiveLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
2940 rmv 33079 postgres 2/251 RowExclusiveLock Yes 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
On Fri, Jun 15, 2012 at 2:46 PM, Benedict Holland <benedict.m.holland@gmail.com> wrote:
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 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.
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).
Thanks,
~BenOn Fri, Jun 15, 2012 at 2:43 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:Peter Geoghegan <peter@2ndquadrant.com> wrote:To check for that, see the queries on these Wiki pages:
> Benedict Holland <benedict.m.holland@gmail.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.
http://wiki.postgresql.org/wiki/Lock_Monitoring
http://wiki.postgresql.org/wiki/Lock_dependency_information
-Kevin
В списке pgsql-performance по дате отправления: