Benedict Holland <benedict.m.holland@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
