Re: concurrency in psql

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.