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

Re: concurrency in psql

From: "Trevor Talbot" <quension(at)gmail(dot)com>
To: "kenneth d'souza" <kd_souza(at)hotmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: concurrency in psql
Date: 2008-01-01 16:02:51
Message-ID: 90bce5730801010802m4afd5054l6f5574f3701eed66@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 1/1/08, kenneth d'souza <kd_souza(at)hotmail(dot)com> wrote:

>  I am trying to understand concurrency and mvcc with a small example in
> psql.

Note that the big advantage to MVCC is that writers do not block
readers. Since your example consists of all writers, MVCC isn't doing
much for you.

> 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;

Transaction 1 has competed the UPDATE, but not committed yet.

> session 2:
> begin;
> update kentab set name='A2d' where id=1;

Transaction 2 does not know how to do the update yet. Transaction 1
has already locked the row for changes, but because it has not
committed yet, transaction 2 does not know what the current values of
the row are. (In this example it doesn't really matter, but imagine if
you were using "where name = 'A'": either transaction 1 will comit a
change to the name, so transaction 2 must skip this row, or
transaction 1 will roll back and transaction 2 must update.)

Transaction 2 is waiting for transaction 1 to finish, so it knows
whether to use the old or new version of the row.

> session 3:
> begin;
> update kentab set name='A3d' where id=1;

Same problem as transaction 2. It is waiting for transaction 1 to finish.

>  Session 1:
> commit;

Transaction 1 has committed its changes, so all waiting transactions
can use the new value of the row. Either transaction 2 or transaction
3 will continue now, and the other one will keep waiting. (Which one
goes first is indeterminate.)

> session 4:
> begin;
> update kentab set name='A4d' where id=1;

Same problem as before. It is waiting for transaction 2 or 3 to
finish, and might have to wait for both.

> I want to now commit in Session 3.
> Firstly I don't see the command prompt.

That means transaction 3 is still waiting. Transaction 2 probably
continued with its UPDATE (in psql, it would say "UPDATE 1" and give
you a prompt), so transaction 3 is waiting for it now.

If you repeat this test, transaction 3 may get to go before transaction 2.

> Morever, despite executing commit; it is not commiting and ending before
> session2 or session4.

The COMMIT cannot be executed until the UPDATE is finished. The UPDATE
is still waiting.

> I have tried Select for Update too but it is behaving the same.

SELECT ... FOR UPDATE performs the same kind of lock as an UPDATE
does, just without changing anything.

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-01-01 19:02:01
Subject: Re: Index Page Split logging
Previous:From: Sam MasonDate: 2008-01-01 15:27:44
Subject: Re: concurrency in psql

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