Thread: concurrency in psql

 <br /> Hi,<br /> I am trying to understand concurrency and mvcc with a small example in psql.<br />Isolation_level is
readcommited. There are 4 psql session by the same Role. I am executing the commands in the below sequence.<br />  <br
/>Session 1:<br />insert into kentab values ( 1,'A');<br />commit; <br /> begin;<br />update kentab set name='Ad' where
id=1;<br/><br />session 2: <br />begin;<br />update kentab set name='A2d' where id=1;<br /><br />session 3: <br
/>begin;<br/>update kentab set name='A3d' where id=1;<br />  <br /> Session 1:<br />commit;<br /><br />session 4:<br
/>begin;<br />update kentab set name='A4d' where id=1;<br /><br />I want to now commit in Session 3. <br />Firstly I
don'tsee the command prompt. <br />Morever, despite executing commit; it is not commiting and ending before session2 or
session4.<br/>I have tried Select for Update too but it is behaving the same.<br />  <br /> Any clue what must be
happeninghere?<br />  <br /> Thanks,<br /> Ken<br /><br /><br /><hr />Fly HYD-BLR for Rs.499 Log on to MakeMyTrip! <a
href="http://ss1.richmedia.in/recurl.asp?pid=266"target="_new">Check it out!</a> 

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.

On 1/1/08, kenneth d'souza <kd_souza@hotmail.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.