Update blocking a select count(*)?

From: Benedict Holland <benedict(dot)m(dot)holland(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Update blocking a select count(*)?
Date: 2012-06-15 18:22:03
Message-ID: CAD+mzoxfm8Wa5hrekeakG_TYNd_dsTR3W3DPZEV90chuzh0N_A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi all,

I am using postgresql 9.0 and I am updating a large table and running a
select count(*). The update is run first and then the select. The update is
blocking the select statement. To use the term MVCC (as seems to be done so
much in this list), well it seems to be broken. MVCC should allow a select
on the same table as an update, in fact nothing at all should block a
select. Also for some reason, the update query seems to always get an
Exclusive Lock which doesn't make any sense to me. At most an update should
require a row lock. This seems to also apply to two updates on the same
table in parallel.

Do I seem to have this right and is there anything I can do?

Thanks,
~Ben

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Peter Geoghegan 2012-06-15 18:32:37 Re: Update blocking a select count(*)?
Previous Message Jeff Janes 2012-06-15 18:20:00 Re: Expected performance of querying 5k records from 4 million records?