Skip site navigation (1) Skip section navigation (2)

Re: concurrency in psql

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: concurrency in psql
Date: 2008-01-01 15:27:44
Message-ID: 20080101152744.GA11262@frubble.xen.chris-lamb.co.uk (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Jan 01, 2008 at 01:02:42PM +0000, kenneth d'souza wrote:
> I am trying to understand concurrency and mvcc with a small example in
> psql.

I don't think your example below really shows MVCC working.  MVCC is a
behind the scenes implementation detail that if you're just typing SQL
commands you're not normally going to notice, apart from the fact that
some statements will be able to run concurrently (but not the example
below) and (with PG's current implementation) you have to run VACUUM
sometimes.

> Isolation_level is read commited. There are 4 psql session by the same
> Role. I am executing the commands in the below sequence.
>  
> Session 1:insert into kentab values ( 1,'A');commit; 
> begin;update kentab set name='Ad' where id=1;
> session 2: begin;update kentab set name='A2d' where id=1;
> session 3: begin;update kentab set name='A3d' where id=1;
>  
> Session 1:commit;
> session 4:begin; update kentab set name='A4d' where id=1;

> I want to now commit in Session 3. Firstly I don't see the command
> prompt. Morever, despite executing commit; it is not commiting and
> ending before session2 or session4.I have tried Select for Update too
> but it is behaving the same.
>
> Any clue what must be happening here?

The database is taking out a lock on the appropriate rows and waiting
for the lock to be granted before the update proceeds.  This is
generally what you want to happen and even if the database could use
MVCC to allow both writes to happen it would then have to abort lots
of transactions at commit time because they'd cause the data in PG to
become inconsistent.  You can use the NOWAIT option on your SELECT FOR
UPDATE statement if you really want to abort early.

Try thinking about a larger example, involving a couple of data
modification statements across different tables and it may help to
understand PG's default behavior.


  Sam

p.s. this sort of question is probably better suited to -general than
-hackers.

In response to

pgsql-hackers by date

Next:From: Trevor TalbotDate: 2008-01-01 16:02:51
Subject: Re: concurrency in psql
Previous:From: Bruce MomjianDate: 2008-01-01 15:23:29
Subject: 8.3RC1 release date

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group