Обсуждение: Broken RR?
Does Postgres garantee repeatable-read (RR) during transactions? And does it implement ARIES/KVL? If so, why is the following possible? T1: begin; T1: select * from table; (notice the row with id = X) T2: begin; T2: delete from table where id = X; T1: select * from table; (notice the row with id = X suddenly is gone) _________________________________________________________________ Tag MSN Hotmail med dig p� mobilen http://www.msn.dk/mobile
On Thu, 5 Jun 2003, Rasmus Resen Amossen wrote: > Does Postgres garantee repeatable-read (RR) during transactions? And does it > implement ARIES/KVL? > > If so, why is the following possible? > > T1: begin; > T1: select * from table; > (notice the row with id = X) > T2: begin; > T2: delete from table where id = X; > T1: select * from table; > (notice the row with id = X suddenly is gone) I can't reproduce the above. Are you sure T2 isn't committing? If it were, since the default isolation level is read committed, T1 would be allowed to see the state after T2 has committed. It isn't allowed to in serializable isolation (or in repeatable read, but afaik we only support read committed and serializable currently).
> T1: begin; > T1: select * from table; > (notice the row with id = X) > T2: begin; > T2: delete from table where id = X; > T1: select * from table; > (notice the row with id = X suddenly is gone) You'll need to SELECT ... FOR UPDATE to lock the row, or use the SERIALIZABLE transaction more I think... Chris
I wrote: > Rasmus Resen Amossen wrote: > > >>Does Postgres garantee repeatable-read (RR) during transactions? And >>does it implement ARIES/KVL? >> >>If so, why is the following possible? >> >>T1: begin; >>T1: select * from table; >> (notice the row with id = X) >>T2: begin; >>T2: delete from table where id = X; >>T1: select * from table; >> (notice the row with id = X suddenly is gone) > > > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=transaction-iso.html#XACT-SERIALIZABLE Whoops. Sorry. I though this was confusion regarding phantom rowsand READ COMMITTED vs. SERIALIZABLE. Nevertheless, I cannot repeat the above... Mike Mascari mascarm@mascari.com
Rasmus Resen Amossen wrote: > Does Postgres garantee repeatable-read (RR) during transactions? And > does it implement ARIES/KVL? > > If so, why is the following possible? > > T1: begin; > T1: select * from table; > (notice the row with id = X) > T2: begin; > T2: delete from table where id = X; > T1: select * from table; > (notice the row with id = X suddenly is gone) http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=transaction-iso.html#XACT-SERIALIZABLE This should probably have been posted to the novice, sql, or general mailing as well, BTW. But that is also mentioned in the on-line pages: ;-) http://www.postgresql.org/lists.html "This list is for the discussion of current development issues, problems and bugs and the discussion of proposed new features. If people in the other lists don't know the answer to a question and it is likely that only a developer will know the answer, you may re-post that question here. You must try elsewhere first!" HTH, Mike Mascari mascarm@mascari.com
I'm moving this to GENERAL. Whomsoever replies there please delete the pgsql-hackers cc entry. On Thu, 5 Jun 2003, Rasmus Resen Amossen wrote: > Does Postgres garantee repeatable-read (RR) during transactions? And does it > implement ARIES/KVL? > > If so, why is the following possible? > > T1: begin; > T1: select * from table; > (notice the row with id = X) > T2: begin; > T2: delete from table where id = X; > T1: select * from table; > (notice the row with id = X suddenly is gone) What version of postgresql are you running? Did you NOT commit the T2 transaction before the last select for T1? If you commit the deletion, and do NOT have transaction mode set to serializable, then yes, this is what you'll see. You can either use select for update or serializable transactions.
>I can't reproduce the above. Are you sure T2 isn't committing? Arch, damn, yes, bugger... >If it were, since the default isolation level is read committed, Ok, that explains it. But my second question is still unanswered: Is the key value locking, that is implemented in postgres, ARIES/KVL? _________________________________________________________________ F� Hotmail p� mobilen http://www.msn.dk/mobile