Re: concurrency in psql

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.