Benedict Holland <benedict(dot)m(dot)holland(at)gmail(dot)com> wrote:
> I am using postgresql 9.0 and I am updating a large table
Updating as in executing an UPDATE statement, or as in using ALTER
TABLE to update the *structure* of the table?
> and running a select count(*). The update is run first and then
> the select. The update is blocking the select statement.
What is your evidence that there is blocking? (A long run time for
the select does not constitute such evidence. Nor does a longer run
time for the same select when it follows or is concurrent to the
update than it was before.)
> To use the term MVCC (as seems to be done so much in this list),
> well it seems to be broken.
That would be very surprising, and seems unlikely.
> MVCC should allow a select on the same table as an update, in fact
> nothing at all should block a select.
Well, no DML should block a select. DDL can.
> Also for some reason, the update query seems to always get an
> Exclusive Lock which doesn't make any sense to me.
There is no PostgreSQL command which acquires an EXCLUSIVE lock on a
table. An UPDATE will acquire a ROW EXCLUSIVE lock, which is very
different. An ALTER TABLE or TRUNCATE TABLE can acquire an ACCESS
EXCLUSIVE lock, which is the *only* level which can block a typical
SELECT statement.
> At most an update should require a row lock. This seems to also
> apply to two updates on the same table in parallel.
You *really* need to read this chapter in the docs:
http://www.postgresql.org/docs/current/interactive/mvcc.html
The part about the different lock levels and what the conflicts are
might be of particular interest:
http://www.postgresql.org/docs/current/interactive/explicit-locking.html#LOCKING-TABLES
> Do I seem to have this right
No.
> and is there anything I can do?
Probably, but you haven't given us enough information to be able to
suggest what.
-Kevin