Re: Slow connection

Andrew Sullivan wrote:
> On Fri, Jun 07, 2002 at 04:39:53PM +0200, Arthur wrote:
>
> > I can outline the basic logic of the program, maybe you can pick
> > something up. I query a table and process the rows. For each row an
> > update statement is run that updates data in the processed row. I
> > use a persistant connection for the updates, but I'm not keen to do
> > transaction batches wtih commit/rollback, etc.
>
> Are you quite sure you're not in a transaction? Because given that
> you're doing updates, and everyone else is apparently blocked, I'd
> expect you're holding a lock on data they're trying to read. Update
> causes a very aggressive lock, for obvious reasons.

In PostgreSQL that lock is not as aggressive as you might
think.

PostgreSQL uses MVCC to avoid readers beeing blocked by
writers. Try it. Start a transaction, update a row and in
another session SELECT that row. You'll not get blocked.

So obviously "everyone else" tries to get a lock for update
too. The question is, does everyone else need that lock?
Read only transaction (reporting) do not need to lock
anything. A transaction has a snapshot view of the entire
database. That's the way pg_dump is creating a consistent
snapshot of the entire database without locking up anything.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #