On 1/1/08, kenneth d'souza <[EMAIL PROTECTED]> 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.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
