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@Yahoo.com #