If I remember correctly, UPDATE establishes a lock on the affected rows,
which will block another UPDATE on the same rows for the duration of the
transaction. If that's true, shouldn't I be able to achieve my desired
behavior by removing the initial as follows:

create function nextid( varchar(32)) returns int8 as '
update idseq set id = id + 1 where name = $1::text;
select id from idseq where name = $1::text;
' language 'sql';
Yes, better, but be sure, to only use this function from inside a transaction.
If you use it in autocommit mode (no begin work) you might in theory read a row,
that another session modified between the two lines.
Or, would I still have to add FOR UPDATE to that final SELECT?
Now, this certainly looks very funny. You actually get reasonable results only
if you do include the "for update" with RC1 sources .

To the rest on the list:
Try the above example by adding a lock between the two lines:

create function nextid( varchar(32)) returns int8 as '
update idseq set id = id + 1 where name = $1::text;
select * from lock1;
select id from idseq where name = $1::text for update;
' language 'sql';

session1:
begin work;
lock table lock1 in access exclusive mode;
session 2:
not in txn: select nextid('one'); // this blocks
select nextid('one');
commit work;

And stare at the results you get with and without for update :-(
Something is definitely fishy with the visibility of SELECT here.

Andreas

Search Discussions

  • Mikheev, Vadim at Mar 30, 2001 at 8:02 pm

    It is intuitive. The bug was iirc, that you saw 2 versions
    of the same row in the second select statement (= 2 rows
    returned by second select).
    I think we should be extremely wary of assuming that we have a clear
    characterization of "what the bug is", let alone "how to fix it".
    The real issue here is that SELECT has different MVCC visibility rules
    from UPDATE and SELECT FOR UPDATE. I suspect that that *must* be so
    This is not correct - SELECT has same rules. Are you able to reproduce
    this bad behaviour without running queries in functions? I assume
    the answer is NO. I just overlooked function case two years ago.
    But SELECT/UPDATE visibility rules are same!
    Ever wonder why in SERIALIZABLE mode UPDATE/SELECT_FOR_UPDATE cause
    rollback in the event of concurrent modification? Because of concurrent
    modifications make visibility of SELECT and UPDATE different and this
    means *unconsistent* view of database for applications.
    In READ COMMITTED mode a query must see changes made by previous
    queries - the only one rule we have to follow to provide consistent
    result for applications.
    in any mode that allows more concurrency than full serializable mode.
    Thus, the question we are really facing is how we might alter the
    visibility rules in a way that will make the results more intuitive
    and/or useful while still allowing concurrency.

    This will take thought, research and discussion. A quick fix is the
    last thing that should be on our minds.
    I agreed to leave it as Known Bug for 7.1.
    A first question: where did the MVCC rules come from
    originally, anyway?
    From the fact that I've used Oracle before Postgres'95,
    liked it and had time to read its documentation -:)
    Is there any academic research to look at?
    There is academic Theorem of Serializability but it's
    different from SERIALIZABLE mode definitions in standard.
    Probably, this difference was caused by lobbying from
    Oracle...

    Vadim
  • Hiroshi Inoue at Mar 31, 2001 at 1:18 am

    -----Original Message-----
    From: Mikheev, Vadim
    It is intuitive. The bug was iirc, that you saw 2 versions
    of the same row in the second select statement (= 2 rows
    returned by second select).
    I think we should be extremely wary of assuming that we have a clear
    characterization of "what the bug is", let alone "how to fix it".
    The real issue here is that SELECT has different MVCC visibility rules
    from UPDATE and SELECT FOR UPDATE. I suspect that that *must* be so
    This is not correct - SELECT has same rules. Are you able to reproduce
    this bad behaviour without running queries in functions? I assume
    the answer is NO. I just overlooked function case two years ago.
    But SELECT/UPDATE visibility rules are same!
    Yes, there seems to be a confusion about visibility.
    Each query in SERIALIZABLE isolation level uses a common snapshot
    for a TX. Each query in READ COMMITTED isolation level uses its own
    snapshot. It seems the only difference between SERIALZABLE and READ
    COMMITTED. But there's a sort of SERIALIZABLE world inside functions
    even under READ COMMITTED mode.

    regards,
    Hiroshi Inoue
  • Mikheev, Vadim at Mar 30, 2001 at 8:08 pm

    This will take thought, research and discussion. A quick fix is the
    last thing that should be on our minds.
    From my latest tests( see following post), I tend to agree,
    that this is extremely sensitive :-(
    I do however think that Vadim's patch description was the
    correct thing to do.
    To avoid double tuple versions return - maybe.
    To get same results from SELECT and SELECT FOR UPDATE in functions -
    no time for 7.1.
    The problem case seems to be when the function is not
    executed inside a txn.
    Any query is executed inside TX. All queries of a function
    are executed in the same TX.

    Vadim

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 30, '01 at 3:42p
activeMar 31, '01 at 1:18a
posts4
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase