Ann,

> Yes, that's the way they work. They don't see committed data by other
> transactions.
>
>> And it's surely not "read committed" in PGSQL.
>
> But it is..
>
> http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html

Sorry, but I cannot find a confirmation there. Exactly the opposite:

<cite>

Read Committed is the default isolation level in PostgreSQL. When a
transaction runs on this isolation level, a *SELECT query sees only data
committed before the query began; it never sees either uncommitted data
or changes committed during query execution by concurrent transactions.*
(However, the SELECT does see the effects of previous updates executed
within its own transaction, even though they are not yet committed.) In
effect, a SELECT query sees a snapshot of the database as of the instant
that that query begins to run. *Notice that two successive SELECT
commands can see different data, even though they are within a single
transaction, if other transactions commit changes during execution of
the first SELECT*.

UPDATE, DELETE, and SELECT FOR UPDATE commands behave the same as SELECT
in terms of searching for target rows: they will only find target rows
that were committed as of the command start time. However, such a target
row may have already been updated (or deleted or marked for update) by
another concurrent transaction by the time it is found. In this case,
the would-be updater will wait for the first updating transaction to
commit or roll back (if it is still in progress). If the first updater
rolls back, then its effects are negated and the second updater can
proceed with updating the originally found row. If the first updater
commits, the second updater will ignore the row if the first updater
deleted it, otherwise it will attempt to apply its operation to the
updated version of the row. *The search condition of the command (the
WHERE clause) is re-evaluated to see if the updated version of the row
still matches the search condition.* If so, the second updater proceeds
with its operation, starting from the updated version of the row.

</cite>

It's exactly our read-committed mode with two additions I described earlier:

a) consistent reads per query/cursors
b) update/delete re-evaluates predicates and either skips or overwrites
concurrent changes

There's no read consistency at the transaction level, only at the
statement level. So it's kinda per query mini-snapshots rather than our
global snapshot.

As for Oracle's read-committed, selects seem working as our
read-committed there, while modifications work accordingly to your scenario:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#2641

So these two examples demonstrate the "write-committed" behavior without
transaction-wise consistent reads, i.e. in the regular read-committed mode.

> No, that's not the way InnoDB works from personal experience

AFAIK, their default isolation mode is REPEATABLE READ, so you're
correct here.

> No you didn't get it right. This mode retains read stability for
> the duration of the transaction, with the exception that a select
> for update will return and lock a record version that was committed
> after the reading transaction started, and the reader will wait if
> the most recent version is not yet committed. Updates and deletes
> block if there is an uncommitted version, but succeed after that
> version commits.

Is there anything else besides InnoDB that offers such a mode for read
stability transactions?

> Yes, I think you did. Jim will do a better job of explaining his
> implementation, but it certainly did not involve redoing whole
> statements.

I'd be glad to hear the details.


Dmitry