From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Benedict Holland <benedict.m.holland@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Update blocking a select count(*)?
Date: Fri, 15 Jun 2012 13:41:02 -0500
Message-ID: <4FDB3B6E02000025000484DF@gw.wicourts.gov> (raw)
In-Reply-To: <CAD+mzoxfm8Wa5hrekeakG_TYNd_dsTR3W3DPZEV90chuzh0N_A@mail.gmail.com>
References: <CAD+mzoxfm8Wa5hrekeakG_TYNd_dsTR3W3DPZEV90chuzh0N_A@mail.gmail.com>
Benedict Holland <benedict.m.holland@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, benedict.m.holland@gmail.com
Subject: Re: Update blocking a select count(*)?
In-Reply-To: <4FDB3B6E02000025000484DF@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox