say, I've got a one column table table_a:

col
1

and I have two transactions to update it at the same time:

txn1: txn2:
begin; begin;
update table_a set col= col + 1; update table_a set col = col + 1;
end; end;

if two transaction begin at exact the same time,
what's the result of 'col' after both transactions committed
in Read committed level? it's 3 or 2?
My understanding is the result is 3, because the simultaneous update
would still executed one by one, and the second one would read the
"current" value of 'col' to do the update. But I'm not sure.

thank you!

laser

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

weiping he <laser@qmail.zhengmai.net.cn> writes:[color=blue]
> txn1: txn2:
> begin; begin;
> update table_a set col= col + 1; update table_a set col = col + 1;
> end; end;[/color]
[color=blue]
> if two transaction begin at exact the same time,
> what's the result of 'col' after both transactions committed
> in Read committed level? it's 3 or 2?
> My understanding is the result is 3,[/color]

If the second xact to lock the row is READ COMMITTED, you get 3.
If it's SERIALIZABLE you get an error. In no case will you silently
lose an update.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Tom Lane дµÀ:
[color=blue]
>weiping he <laser@qmail.zhengmai.net.cn> writes:
>
>[color=green]
>>txn1: txn2:
>>begin; begin;
>>update table_a set col= col + 1; update table_a set col = col + 1;
>>end; end;
>>
>>[/color]
>
>
>[color=green]
>>if two transaction begin at exact the same time,
>>what's the result of 'col' after both transactions committed
>>in Read committed level? it's 3 or 2?
>>My understanding is the result is 3,
>>
>>[/color]
>
>If the second xact to lock the row is READ COMMITTED, you get 3.
>If it's SERIALIZABLE you get an error. In no case will you silently
>lose an update.
>
>[/color]
dose is mean that I must use some kind of lock ( ... FOR UPDATE for example)
to lock that row to get the result 3 in READ COMMITTED level? My
understanding
is even in MVCC environment, the update action would still be executed
sequencly
(by means of some kind of lock).

What confused me is: in MVCC environment, what's the procedure of
postgresql to
use lock when two transaction update to the same row at the same time?

thanks you.

Laser

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


You might want to check out yesterdays thread with the subject
"Table Lock issue". Tom gave a really long and detailed explanation there
(which I'm very grateful for).
The problem you have looks similar, if not identical to the one in that
thread.

UC


On Friday 16 April 2004 03:49 am, weiping he wrote:[color=blue]
> Tom Lane дµÀ:[color=green]
> >weiping he <laser@qmail.zhengmai.net.cn> writes:[color=darkred]
> >>txn1: txn2:
> >>begin; begin;
> >>update table_a set col= col + 1; update table_a set col = col + 1;
> >>end; end;
> >>
> >>
> >>
> >>
> >>
> >>if two transaction begin at exact the same time,
> >>what's the result of 'col' after both transactions committed
> >>in Read committed level? it's 3 or 2?
> >>My understanding is the result is 3,[/color]
> >
> >If the second xact to lock the row is READ COMMITTED, you get 3.
> >If it's SERIALIZABLE you get an error. In no case will you silently
> >lose an update.[/color]
>
> dose is mean that I must use some kind of lock ( ... FOR UPDATE for
> example) to lock that row to get the result 3 in READ COMMITTED level? My
> understanding
> is even in MVCC environment, the update action would still be executed
> sequencly
> (by means of some kind of lock).
>
> What confused me is: in MVCC environment, what's the procedure of
> postgresql to
> use lock when two transaction update to the same row at the same time?
>
> thanks you.
>
> Laser
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html[/color]

- --
UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAf/9XjqGXBvRToM4RAkkRAJ49ARgLvRsOpUPYhoe/d95L3NL/ZQCeNC0I
pDrNUGKwE+wwqZicUeRbhPk=
=o69M
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings